468,238 Members | 2,037 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,238 developers. It's quick & easy.

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.
Please help.

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

Post your reply

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

Similar topics

reply views Thread by Marek Lewczuk | last post: by
1 post views Thread by Paul Bramscher | last post: by
3 posts views Thread by Ian Boyd | last post: by
4 posts views Thread by polycom | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.