Left Join -

[teneesh]

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,rotatio n,max(pgy) pgy, startdate,endda te,
SUM(DATEDIFF(da y, StartDate, EndDate) + 1) AS daysworked
from Schedule s, ViewcurrentResi dents 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),rot ation,cast(righ t(degrcode,1) as tinyint), startdate, enddate



Select r.eid,acyear,ro tation,sum(date diff(day, datefrom, dateto)+1) daysoff
from vacation v, ViewcurrentResi dents r
where v.eid=r.eid
and r.eid = 8119
group by r.eid,acyear,ro tation

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

[Delerna]

hard to be sure its right without knowing more about your data and being able to test it, but something like.
[code=sql]
SELECT r.Eid,
AcYear,
dbo.idtoservice (s.srvcode) as service,
rotation,
max(pgy) as pgy,
startdate, enddate,
SUM(DATEDIFF(da y, StartDate, EndDate) + 1) AS daysworked,
sum(datediff(da y, datefrom, dateto)+1) daysoff
FROM ViewcurrentResi dents 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)
[/code]

[teneesh]

here is what i used:

SELECT A.*, daysoff
FROM
(Select r.Eid, AcYear, dbo.idtoservice (s.srvcode)as service,rotatio n,max(pgy) pgy, startdate,endda te,
SUM(DATEDIFF(da y, StartDate, EndDate) + 1) AS daysworked
from Schedule s, ViewcurrentResi dents 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),rot ation,cast(righ t(degrcode,1) as tinyint), startdate, enddate
) A
LEFT JOIN
(Select r.eid,acyear,ro tation,sum(date diff(day, datefrom, dateto)+1) daysoff
from vacation v, ViewcurrentResi dents r
where v.eid=r.eid
and r.eid = 8119
group by r.eid,acyear,ro tation
) B
ON
A.EID=B.EID AND A.ACYEAR=B.ACYE AR AND A.ROTATION=B.RO TATION