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

[Delerna]

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)
 

[teneesh]

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