Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 group data into 4 week payperiods
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

4 Posts

Posted - 06/13/2014 :  19:54:36  Show Profile  Reply with Quote
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

Patron Saint of Lost Yaks

30421 Posts

Posted - 06/14/2014 :  04:49:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Please define what constitutes the first week of the year.

Go to Top of Page

Starting Member

4 Posts

Posted - 06/14/2014 :  06:56:22  Show Profile  Reply with Quote

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


Edited by - Tallboy on 06/14/2014 07:12:34
Go to Top of Page

Starting Member

14 Posts

Posted - 06/16/2014 :  08:13:42  Show Profile  Reply with Quote
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.

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.

Edited by - BBarn on 06/16/2014 08:14:40
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000