468,238 Members | 2,037 Online

# Left Join -

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.

Thanks,
Tenesha
Oct 15 '08 #1
2 1180
Delerna
1,134 Expert 1GB
hard to be sure its right without knowing more about your data and being able to test it, but something like.
Expand|Select|Wrap|Line Numbers
1. SELECT r.Eid,
2.       AcYear,
3.       dbo.idtoservice(s.srvcode) as service,
4.       rotation,
5.       max(pgy) as pgy,
6.       startdate,   enddate,
7.       SUM(DATEDIFF(day, StartDate, EndDate) + 1) AS daysworked,
8.       sum(datediff(day, datefrom, dateto)+1) daysoff
9. FROM ViewcurrentResidents r
10. LEFT JOIN Schedule s ON r.eid=s.eid
11. LEFT JOIN vacation v ON r.eid=v.eid
12. WHERE s.stfgroup in (3,4)
13.   AND r.eid = 8119
14. GROUP BY r.eid, acyear, rotation, startdate, enddate,
15.     dbo.idtoservice(s.srvcode),
16.    cast(right(deg rcode,1) as tinyint)
17.
Oct 15 '08 #2
teneesh
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
Oct 17 '08 #3