SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

Tallboy
Starting Member

4 Posts

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

Sweden
30249 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.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Tallboy
Starting Member

4 Posts

Posted - 06/14/2014 :  06:56:22  Show Profile  Reply with Quote
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

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

BBarn
Starting Member

USA
13 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.

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.

Edited by - BBarn on 06/16/2014 08:14:40
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.11 seconds. Powered By: Snitz Forums 2000