Totaling more than one line item in table

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

General SQL Server Forums

New to SQL Server Programming

Totaling more than one line item in table

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2008-01-16 : 14:21:49

I need to figure out hours based on StartTime and EndTime (year does not matter) from the first table and totaled for the entire week based on the effective date in the second table.

I need it to return the follwing:
WorkHourGroup TotalWorkHours
TJOHNSO 24 Hours

Table 1 -'workhour'
WorkHourGroup DayID StatrtTime EndTime
TJOHNSO 0 NULL NULL
TJOHNSO 1 1899-12-30 09:00:00.000 1899-12-30 17:00:00.000
TJOHNSO 2 NULL NULL
TJOHNSO 3 1899-12-30 09:00:00.000 1899-12-30 17:00:00.000
TJOHNSO 4 NULL NULL
TJOHNSO 5 1899-12-30 09:00:00.000 1899-12-30 17:00:00.000
TJOHNSO 6 NULL NULL

Table 2 -'workhourgroup'
WorkHourGroup WorkHourDesc Effective Date
SMB SMB Work Week 2007-09-11 00:00:00.000
SMITHBSTANDARD 2008-01-12 00:00:00.000
TJOHNSO Johnson1 2008-01-11 00:00:00.000

Any ideas on how to accomplish this?

Thanks,
DZ

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-01-16 : 17:06:42

SELECT wh.WorkHourGroup
,[TotalWorkHours] =SUM(DATEDIIFF(hour,whg.StartTime,whg.EndTime)
FROM
WorkHour wh
INNER JOIN
WorkHourGroup whg
ON
wh.WorkHourGroup = whg.WorkHourGroup
WHERE whg.EffectiveDate = '01/11/2008'
GROUP BY
wh.WorkHourGroup

dzabor
Posting Yak Master

138 Posts

Posted - 2008-01-16 : 19:58:52

That worked perfect! Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources