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
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