Issues with Joins in Query Results

[renee35]

What I need to accomplis is:
1- get all candidates who has at least 90-180 days between startdate and @quarter:


declare @quarterstart datetime
SET @quarterstart = '4/1/2009'

Create table #candidates (candidateid int, indentificationdetails nvarchar(50), firstname nvarchar(50), lastname nvarchar(50), startdate datetime,
email nvarchar(60), email2 nvarchar(60))

Insert into #candidates (candidateid, indentificationdetails, firstname, lastname, startdate, email, email2)
(
Select c.candidateid, identificationdetails, firstname, lastname, min(oc.startingdate)as startdate, email, email2
from ordercandidates oc
inner join candidates c on c.candidateid = oc.candidateid
inner join
(
Select ch.candidateid, ch.orderid
From CandidateHistory ch join
HistoryCategories h on h.ID = ch.CategoryID
where h.StageID = 6
Group By candidateid, ch.orderid
)as ch on ch.candidateid = c.candidateid and ch.orderid = oc.orderid
where datediff(dd, oc.startingdate, @quarterstart) between 90 and 180
Group by c.candidateid, identificationdetails, firstname, lastname, email, email2
)

2- From that list I need to get the max(checkdate) and only pull those candidates whose checkdate is less than the startdate from #candidates:

from #candidates r left outer join
(
select h.candidateid, max(checkdate) as checkdate, c.startdate
from #candidates c
join integrateprod.dbo.rmxpayrollhistory h on h.candidateid = c.candidateid and checkdate < c.startdate
Group by h.candidateid, c.startdate
)p on p.candidateid = r.candidateid

** the reason I am doing the left outer join is because of the where clause in the report:
3- Once that list is completed based on the join, I need to then only return anyone who has never gotten a check or the difference between the checkdate and startdate is over 365 days:

where(datediff(dd, p.checkdate, r.startdate) > 365 or checkdate IS NULL )

I not only want those emps that have a checkdate less than the startdate, but I also want any candidates that don't have a check before their startdate (in other words they new and have never had an assignment in the past).

The issue here is I am getting back too many rows when I run the query (combined version):

declare @quarterstart datetime
SET @quarterstart = '4/1/2009'

Create table #candidates (candidateid int, indentificationdetails nvarchar(50), firstname nvarchar(50), lastname nvarchar(50), startdate datetime,
email nvarchar(60), email2 nvarchar(60))

Insert into #candidates (candidateid, indentificationdetails, firstname, lastname, startdate, email, email2)
(
Select c.candidateid, identificationdetails, firstname, lastname, min(oc.startingdate)as startdate, email, email2
from ordercandidates oc
inner join candidates c on c.candidateid = oc.candidateid
inner join
(
Select ch.candidateid, ch.orderid
From CandidateHistory ch join
HistoryCategories h on h.ID = ch.CategoryID
where h.StageID = 6
Group By candidateid, ch.orderid
)as ch on ch.candidateid = c.candidateid and ch.orderid = oc.orderid
where datediff(dd, oc.startingdate, @quarterstart) between 90 and 180
Group by c.candidateid, identificationdetails, firstname, lastname, email, email2
)

--elect min(oc.startingdate) from ordercandidates oc where candidateid = '830'
Select
r.candidateid,
'SSN' = r.indentificationdetails,
'Full Name' = r.lastname+', '+r.firstname,
'Start Date' = convert(varchar, r.startdate,101),
'Last Check Date' = convert(varchar, p.checkdate, 101),
'Email Address' = isnull(r.email,r.email2)


from #candidates r --order by 1
left outer join
(
select h.candidateid, max(checkdate) as checkdate, c.startdate
from #candidates c
join integrateprod.dbo.rmxpayrollhistory h on h.candidateid = c.candidateid and checkdate < c.startdate
Group by h.candidateid, c.startdate --order by 1
)p on p.candidateid = r.candidateid --order by 1
where(datediff(dd, p.checkdate, r.startdate) > 365 or checkdate IS NULL )

Order by 1

I am getting too many rows returned? What am I doing wrong in my joins?

Thanks a bunch for any help with this query..

Thanks a bunch!!

-T

 

[markros]

Which SQL Server version you're using? To me it looks OK, but it's really hard to understand the logic.

If you're using SQL Server 2005 and up, I would try to split your select into the sub-selects using CTE. This way you can debug it and it would be easier to maintain and understand.

 

[renee35]

Thanks! I found the error!!

Thanks a bunch!!

-T

 

[markros]

Welcome