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.
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?tiaJoshuaquery:Select TSI.JobID, TSI.CustomerID, TSI.ProductID, TSTMon.Hours, TSTTue.Hours, TSTWed.Hours, TSTThu.Hours, TSTFri.Hours, TSTSat.Hours, TSTSun.Hoursfrom caTimeSheet TSjoin caTimeSheetItem TSI on TS.TimeSheetID = TSI.TimeSheetID -- Will always have a TSI .:. an inner joinleft join caTimeSheetTime TSTMon on TfSTMon.TimeSheetItemID = TSI.TimeSheetItemID and TSTMon.OffSetToEntryDate = 0left join caTimeSheetTime TSTTue on TSTTue.TimeSheetItemID = TSI.TimeSheetItemID and TSTTue.OffSetToEntryDate = 1left join caTimeSheetTime TSTWed on TSTWed.TimeSheetItemID = TSI.TimeSheetItemID and TSTWed.OffSetToEntryDate = 2left join caTimeSheetTime TSTThu on TSTThu.TimeSheetItemID = TSI.TimeSheetItemID and TSTThu.OffSetToEntryDate = 3left join caTimeSheetTime TSTFri on TSTFri.TimeSheetItemID = TSI.TimeSheetItemID and TSTFri.OffSetToEntryDate = 4left join caTimeSheetTime TSTSat on TSTSat.TimeSheetItemID = TSI.TimeSheetItemID and TSTSat.OffSetToEntryDate = 5left join caTimeSheetTime TSTSun on TSTSun.TimeSheetItemID = TSI.TimeSheetItemID and TSTSun.OffSetToEntryDate = 6where 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 thisselect 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.TimeSheetIDwhere TS.TimeSheetID = '1' KH |
|
|
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 needsunspammedkaethy |
|
|
|
|
|
|
|