(medium) complex joining - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

(medium) complex joining

Author

Topic

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-06-30 : 09:40:21

tblCALENDAR
contains all these fields

id datefrom datetill catid rosterid

tblUSERS

id rosterid name ...

tblUSERS_PLANNING

id userid datefrom datetill
(multiple records with same userid are posible, always take the one with the date = highest)

i want to make a view, with all users, combined with the actual hours they represent in the calendar, in my content , the number of hours they worked....

i already got the following:

a view wich gets all the users and there corresponding (maximum) datefrom's

so i only need to use these fields to (join?) make a count in the calendar table



select 
U.id,U.naam,U.voornaam,U.teamid,U.opleidingtypeidplanning,U.begindatumplanning 
,P.hoursworked

	FROM vwUSERS U
		LEFT OUTER JOIN
			(SELECT sum(DATEDIFF (hour ,dd_start ,dd_end )) as hoursworked from tblcalendar C
			where 
		        C.opleidingtypeid = U.opleidingtypeidplanning
                        C.rosterid = U.rosterid
			and C.datefrom >= U.datefrom 
			) AS P ON ..... ???? .......

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-30 : 10:14:14

[code]SELECT u.ID,
u.Naam,
u.Voornaam,
u.TeamID,
u.OpleidingTypeIDPlanning,
u.BeginDatumPlanning,
p.HoursWorked
FROM vwUSERS AS u
OUTER APPLY (
SELECT SUM(DATEDIFF(MINUTE, c.dd_start, c.dd_end) / 60.0) AS HoursWorked
FROM tblCalendar AS c
WHERE c.OpleidingTypeID = u.OpleidingTypeIDPlanning
AND c.RosterID = u.RosterID
AND c.DateFrom >= u.datefrom
) AS p(HoursWorked)[/code]

N 56°04'39.26"
E 12°55'05.63"

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-07-01 : 03:16:36

dead on !

thx , now i want to extend this by integrating this with the table with the date ranges of absentiism ( is that a word ?)
so i want the sum of the hours, like already show above, minus the ranges of the rows of the table tblabsent

this table has following layout

id userid datefrom datetill

as u can see, this has no teamid in it to link it, but u may assume that it is there, i can make a simple view
of that table with then the userid and the corresponding teamid of that user to link it to calendar...
PS: rosterid is in fact the same as teamid, but tblcalendar also contains a teamid field...

i hope you can help me with this, you[guys][ve] helped me already alot which im very grateful for ....

additional question:
when trying to resolve the first query i used
"select field1, ..... , ( SELECT sum ..... ) as hoursworked FROM...."

Is this very bad performance wise ?
i guess the server then has to make 1 extra request for each row... or isnt this too bad ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources