group data into 4 week payperiods

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 2008 Forums

Transact-SQL (2008)

group data into 4 week payperiods

Author

Topic

Tallboy
Starting Member

4 Posts

Posted - 2014-06-13 : 19:54:36

Hi,
I have a table as follows

ID, int PK
PersNo, int
WeekEnding, date
WorkedHours, float
ContractHours float

There may an entry for every stff member for each week for the year or not, most will have 52 entries per year and each WeekEnding date is the sunday.
So I want to group into 4 week pay period and sum the WorkedHours and ContractHours by PersNo.

Data would look like
That would be great...
Sample data would be
ID, PersNo,WeekEnding, WorkedHours, ContractHours
14, 1234, 1/10/14, 10.50, 20.00
13, 1234, 23/9/14, 11.00, 20.00
12, 1234, 16/9/14, 10.75, 22.00
11, 1234, 9/9/14, 10.50, 22.00
10, 4343, 1/10/14, 11.25, 12.00
9, 4343, 23/9/14, 11.25, 12.00
8, 4343, 16/9/14, 11.25, 12.00
7, 4343, 9/9/14, 11.25, 12.00

So each employee may have 52 weeks data per year, but they may start late in the year or leave before the year is up. But all data has WeekEnding dates ie Sundays.

Final output for each employee would be 1 row for each 4 week period as follows;
PerNo, 4WkEnding, TotalWorkedHours, TotalContractHours
1234, 1/10/14, 42.75, 82.00
4343, 1/10/14, 45.00, 48.00

I think I need to use rownumber or CTE but I am not sur ehow to proceed.

All help appreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-14 : 04:49:55

Please define what constitutes the first week of the year.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Tallboy
Starting Member

4 Posts

Posted - 2014-06-14 : 06:56:22

HI,

Startdate would be 23/3/14.

If I could assign a value to everydate following date in groups fo 4 dates ...

I was thinking of 1+datefiff(wk,StartDate,WeekEnding)/4

Regards

BBarn
Starting Member

14 Posts

Posted - 2014-06-16 : 08:13:42

My assumption is that this will be a long-term / permanent application addition. With that assumption, create a calendar table of dates (think dimDates - or search for dimDates, there are plenty of examples out there).

In the calendar table, you define FULLDATE,DOW,MONTH,QUARTER,DAYOFYEAR... you could add My4WeekPeriod and set the data accordingly. Once that is done, join the table to your main query table by FULLDATE and use your new column for selecting the appropriate My4WeekPeriod.

WHERE ...
AND dimDate.My4WeekPeriod = X

The advantage to this is that you can make it a table based parameter that you can add administrative access to so that end-users can manipulate their periods in the future or make it easier for you to do later on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources