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 2000 Forums
 Transact-SQL (2000)
 Timesheet Grid View Query

Author  Topic 

caffeine200
Starting Member

3 Posts

Posted - 2006-06-15 : 11:25:35
I'm doing a timesheet application. The table structure is attached in an image and there is also a screen shot of the application.

I'm trying to get data in a suitable format for the application. There is a reasonable expectation that the third level table (TimeSheetItems) will grow to many hundreds of thousands of records. The following query may become a bit expensive on the database overhead. Is there a better way to do this?

tia
Joshua


query:

Select TSI.JobID, TSI.CustomerID, TSI.ProductID, TSTMon.Hours, TSTTue.Hours, TSTWed.Hours, TSTThu.Hours
, TSTFri.Hours, TSTSat.Hours, TSTSun.Hours
from caTimeSheet TS
join caTimeSheetItem TSI on TS.TimeSheetID = TSI.TimeSheetID -- Will always have a TSI .:. an inner join
left join caTimeSheetTime TSTMon on TfSTMon.TimeSheetItemID = TSI.TimeSheetItemID and TSTMon.OffSetToEntryDate = 0
left join caTimeSheetTime TSTTue on TSTTue.TimeSheetItemID = TSI.TimeSheetItemID and TSTTue.OffSetToEntryDate = 1
left join caTimeSheetTime TSTWed on TSTWed.TimeSheetItemID = TSI.TimeSheetItemID and TSTWed.OffSetToEntryDate = 2
left join caTimeSheetTime TSTThu on TSTThu.TimeSheetItemID = TSI.TimeSheetItemID and TSTThu.OffSetToEntryDate = 3
left join caTimeSheetTime TSTFri on TSTFri.TimeSheetItemID = TSI.TimeSheetItemID and TSTFri.OffSetToEntryDate = 4
left join caTimeSheetTime TSTSat on TSTSat.TimeSheetItemID = TSI.TimeSheetItemID and TSTSat.OffSetToEntryDate = 5
left join caTimeSheetTime TSTSun on TSTSun.TimeSheetItemID = TSI.TimeSheetItemID and TSTSun.OffSetToEntryDate = 6
where TS.TimeSheetID = '1'




TimeSheet has an Identity field, the user logged in and the datestamp of the beginning of the week the timesheet is captured in.

There will be many TimeSheetItems to each TimeSheet. Fields are Identity, ID of the Timesheet and a description.

TimeSheetTime contains up to 7 records for each TimeSheetItem. Fields include OffsetToEntry date. This is an integer that specifies the number of days elapsed betweent the Entry date in the TimeSheet table and the TimeSheetTime record.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-15 : 11:52:34
try this
select TSI.JobID, TSI.CustomerID, TSI.ProductID, 
sum(case when TSI.OffSetToEntryDate = 0 then TSI.Hours end) as MonHours,
sum(case when TSI.OffSetToEntryDate = 1 then TSI.Hours end) as TueHours,
. . .
from caTimeSheet TS join caTimeSheetItem TSI
on TS.TimeSheetID = TSI.TimeSheetID
where TS.TimeSheetID = '1'



KH

Go to Top of Page

kaethy
Starting Member

1 Post

Posted - 2011-06-15 : 06:30:01
Our time and attendance system is totally optimized for multiple sites, multiple time zones and multiple languages. There are many features that allow you to configure the application to fit your solution needs



unspammed

kaethy
Go to Top of Page
   

- Advertisement -