Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Issues with Joins in Query Results

Status
Not open for further replies.

renee35

MIS
Jan 30, 2007
199
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
 
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.
 
Thanks! I found the error!!

Thanks a bunch!!

-T
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top