Timesheet Grid View Query
-
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.
-
Hi
Hope this works for you...
set nocount on
declare @TimeSheet table
(
TimesheetID INT,
EmployeeIDINT,
TimeSheetdate Datetime
)
INSERT INTO @TimeSheet values (1, 1, '08/06/2006')
INSERT INTO @TimeSheet values (2, 1, '08/06/2006')
declare @TimeSheetItem table
(
TimesheetItemID INT,
TimesheetIDINT,
Description VARCHAR(50)
)
INSERT INTO @TimeSheetItem values (1, 1, 'Resource for project 1')
INSERT INTO @TimeSheetItem values (2, 1, 'Documenting Search')
INSERT INTO @TimeSheetItem values (3, 1, 'Hello Everyone')
INSERT INTO @TimeSheetItem values (4, 2, 'This is a new week')
INSERT INTO @TimeSheetItem values (5, 2, 'This is new week''s second item')
declare @TimeSheetTime table
(
TimeSheetTimeID INT,
TimeSheetItemIDINT,
Hoursdecimal(5,2),
OffsetToEntrydate tinyint
)
INSERT INTO @TimeSheetTime values (1, 1, 1.5, 0)
INSERT INTO @TimeSheetTime values (2, 1, 2, 1)
INSERT INTO @TimeSheetTime values (3, 1, 2.5, 3)
INSERT INTO @TimeSheetTime values (4, 2, 1, 0)
INSERT INTO @TimeSheetTime values (5, 2, 2.5, 2)
INSERT INTO @TimeSheetTime values (6, 2, 1.5, 3)
INSERT INTO @TimeSheetTime values (7, 3, 3.5, 0)
INSERT INTO @TimeSheetTime values (8, 4, 2.5, 2)
INSERT INTO @TimeSheetTime values (9, 4, 1.5, 2)
INSERT INTO @TimeSheetTime values (10, 5, 1.5, 0)
INSERT INTO @TimeSheetTime values (11, 5, 2, 2)
SELECT TimeSheetItemID, [0] AS Mon, [1] AS Tue, [2] AS Wed, [3] AS Thu, [4] AS Fri, [5] as Sat, [6] as Sun
FROM
(SELECT OffSetToEntryDate, Hours, TimesheetItemID
FROM @TimeSheet ts
LEFT JOIN @timeSheetTime tst
on TST.TimeSheetItemID = tst.TimeSheetItemID
WHERE TimesheetID = 1
) p
PIVOT
(
sum (Hours)
FOR OffSetToEntryDate IN
( [0], [1], [2], [3], [4], [5], [6] )
) AS pvt
ORDER BY TimeSheetItemID
Note: You may need to add few other table in main query...
-
Hi,
Thanks for the reply, it would work except that I'm using SQL 2000, so unfortunately, I don't have the PIVOT keyword functionality yet.
I've tried another approach, maybe you'd like to comment on whether this method would be better against large volumes in the TimeSheetTime table.
Many thanks,
Joshua
SELECT TSI.TimesheetItemID,
sum(case when OffSetToEntryDate = 0 then Hours else null end) '0',
sum(case when OffSetToEntryDate = 1 then Hours else null end) '1',
sum(case when OffSetToEntryDate = 2 then Hours else null end) '2',
sum(case when OffSetToEntryDate = 3 then Hours else null end) '3',
sum(case when OffSetToEntryDate = 4 then Hours else null end) '4',
sum(case when OffSetToEntryDate = 5 then Hours else null end) '5',
sum(case when OffSetToEntryDate = 6 then Hours else null end) '6'
FROM TimeSheet TS
LEFT JOIN TimeSheetItem TSI on TSI.TimeSheetID = TS.TimeSheetID
LEFT JOIN TimeSheetTime TST on TST.TimeSheetItemID = TSI.TimeSheetItemID
WHERE TSI.TimeSheetID = '1'
group by TSI.TimeSheetItemID
-
Yes it would.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Thanks
-
Joshua,
I am having a debate with others over the datamodel for timesheets. Your image files no longer work. Would you mind posting a text version of the tables and fields in your approach.
Are you happy with it, would you change it if you could?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply