By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

Left Join -

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
Oct 15 '08 #1
Share this Question
Share on Google+
2 Replies


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

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
Oct 17 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.