Summing Timesheet Data by Week

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)

Summing Timesheet Data by Week

Author

Topic

Artanor
Starting Member

2 Posts

Posted - 2008-06-05 : 07:32:55

OK I have a couple of problems I am hoping somebody can help me with.

Firstly I am trying to summarise some data in a timesheet entry database.

Resources table has fields;

ResourceID - Employees ID Code
Name - Name of employee

Timesheet table has fields;

ResourceID - Employees ID code
RegularHoursBooked - the hours entered for this particular entry
TimeDate - the date this entry applies to i.e. when the work was actually done
CreationDate - the date the time entry record was created.
ApprovalStatus - if this is null then the time has not been verified so is excluded.

Heres my query

SELECT Resources.Name, SUM(Timesheet.RegularHoursBooked) AS TotalHours, DATEPART(ww, Timesheet.TimeDate) AS Week
FROM Timesheet INNER JOIN Resources ON Timesheet.ResourceId = Resources.ResourceId
WHERE (NOT (Timesheet.ApprovalStatus IS NULL)) AND (Resources.Name = N'Andrew Wood') AND (Timesheet.TimeDate >= DATEADD(dd, 1, DATEADD(week, 1, DATEADD(yy, DATEPART(yy, GETDATE()) - 1900, 0)) - 4 - DATEPART(dw, DATEADD(week, 1, DATEADD(yy, DATEPART(yy, GETDATE()) - 1900, 0)) - 4)))
GROUP BY Resources.Name, DATEPART(ww, Timesheet.TimeDate)

Now the problem is for the first week of this year Monday actually falls in 2007 and so appears as week 53 which is gonna throw my report out completely. Is there a way around this ?

Secondly I am wanting to add another column to flag weeks where the time entry was later than the Monday following the time the data was actually entered for.

I can get the entries made before the the following Monday i.e. On Time by adding an extra condition to the where clause
AND (Timesheet.CreationDate <= DATEADD(dd, 7, DATEADD(wk, DATEDIFF(wk, 7, Timesheet.TimeDate), 7)))
But I need to include the late time entries too.

Any guidance will be greatly appreciated.
Andrew Wood

Artanor
Starting Member

2 Posts

Posted - 2008-06-09 : 07:04:56

Does anybody have a clue where a to begin with the issues I am having, particularly the week returned issue is there some way I can force the week number in that oparticular instance to be a 1 instead of 53?

Also on the second question is there a way to include a second query to generate that value maybe ?

As you can probably guess I am relatively new to SQL comming at it without much in the way of a database background. However just some pointers in the right direction is all I need.

Many thanks
Andrew

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-09 : 10:39:43

It sounds like your weeks are following the ISO definition. You can use the functions below to convert a datetime to an ISO week.
ISO Year Week Day of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60515
ISO Week of Year Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510

You can use the function on the link below to generate a date table that include columns for ISO weeks.
Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources