P: 46

Hi 
I'm trying to join these two queries but apparently I don't know how to do a left join for them.
Select r.Eid, AcYear, dbo.idtoservice(s.srvcode)as service,rotation,max(pgy) pgy, startdate,enddate,
SUM(DATEDIFF(day, StartDate, EndDate) + 1) AS daysworked
from Schedule s, ViewcurrentResidents r
where s.eid=r.eid and s.stfgroup in (3,4)
and r.eid = 8119
group by r.eid, acyear, dbo.idtoservice(s.srvcode),rotation,cast(right(deg rcode,1) as tinyint), startdate, enddate
Select r.eid,acyear,rotation,sum(datediff(day, datefrom, dateto)+1) daysoff
from vacation v, ViewcurrentResidents r
where v.eid=r.eid
and r.eid = 8119
group by r.eid,acyear,rotation
I really just need the last field (daysoff) in the second query to be joined to the first query  even when a record does not exist.
Please help.
Thanks,
Tenesha
 
Share this Question
Expert 100+
P: 1,134

hard to be sure its right without knowing more about your data and being able to test it, but something like. 
SELECT r.Eid,

AcYear,

dbo.idtoservice(s.srvcode) as service,

rotation,

max(pgy) as pgy,

startdate, enddate,

SUM(DATEDIFF(day, StartDate, EndDate) + 1) AS daysworked,

sum(datediff(day, datefrom, dateto)+1) daysoff

FROM ViewcurrentResidents r

LEFT JOIN Schedule s ON r.eid=s.eid

LEFT JOIN vacation v ON r.eid=v.eid

WHERE s.stfgroup in (3,4)

AND r.eid = 8119

GROUP BY r.eid, acyear, rotation, startdate, enddate,

dbo.idtoservice(s.srvcode),

cast(right(deg rcode,1) as tinyint)

 
P: 46

here is what i used:
SELECT A.*, daysoff
FROM
(Select r.Eid, AcYear, dbo.idtoservice(s.srvcode)as service,rotation,max(pgy) pgy, startdate,enddate,
SUM(DATEDIFF(day, StartDate, EndDate) + 1) AS daysworked
from Schedule s, ViewcurrentResidents r
where s.eid=r.eid and s.stfgroup in (3,4)
and r.eid = 8119
group by r.eid, acyear, dbo.idtoservice(s.srvcode),rotation,cast(right(deg rcode,1) as tinyint), startdate, enddate
) A
LEFT JOIN
(Select r.eid,acyear,rotation,sum(datediff(day, datefrom, dateto)+1) daysoff
from vacation v, ViewcurrentResidents r
where v.eid=r.eid
and r.eid = 8119
group by r.eid,acyear,rotation
) B
ON
A.EID=B.EID AND A.ACYEAR=B.ACYEAR AND A.ROTATION=B.ROTATION
    Question stats  viewed: 1111
 replies: 2
 date asked: Oct 15 '08
