Sum a Column by Client and Group by date 4 weeks apart!

Hi MM,

Thansks for your suggestions.

Thats what I was thinking myself a calendar table with just 2 columns something like...

MonthID, WeekEndate

1 , 7/1/14

1 , 14/1/14

1 , 21/1/14

1 , 28/1/14

2 , 5/2/14

2 , 12/2/14

and so on...

then join it to my table and group by MonthID and CustomerID.

But I was thinking I cant be the first person to do this... so maybe someone else has a better way of doing it...

kind regards

This is a possible solution but I'm not sure that it will work as it is for your data because I can't test it. This won't need a calendar table or additional columns, but it might not be exactly what you need and you'll have to work with it.

CREATE TABLE Sometable(

CustomersID int,

WeekEndingDate date,

WorkedHours decimal( 18,8));

WITH CTE AS(

SELECT customersID,

WeekEndingDate,

(DENSE_RANK() OVER( PARTITION BY customersID ORDER BY WeekEndingDate) - 1 )/ 4 drankdrank,

WorkedHours

FROM SomeTable

)

SELECT customersID,

MIN( WeekEndingDate),

MAX( WeekEndingDate),

SUM( WorkedHours)

FROM CTE

GROUP BY drank

DDL would have been simpler than the explanation, as you can see in the example. Sample data and expected results would have helped to validate that the code is correct instead of a wild guess.

This will work with SQL Server 2008 & 2012 which I suppose that you're using as there's no 2010.

Double doesn't exists in SQL Server, so I used a decimal type.

EDIT: Code corrected.

;WITH cte (CustomerID,WeekEndingDate,WorkedHours,WeekNo) AS (

SELECT CustomerID,WeekEndingDate,WorkedHours,CEILING(ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY WeekEndingDate) / 4.0)

FROM

)

SELECT CustomerID,MAX(WeekEndingDate),SUM(WorkedHours)

FROM cte

GROUP BY CustomerID,WeekNo

ORDER BY CustomerID ASC,2 ASC

Hi Guys, thanks for your reply, it looks like something that would work the bill.

Everything was going grand until I read

DENSE_RANK() OVER( PARTITION BY WeekEndingDate ORDER BY customersID) - 1 )/ 4 drank

I am familiar with most of t-sql but an explanation of above would help...

What I want out is three columns as follows;

customerID , Max(4WeekEndDate), Sum(WorkedHours)

123456 , 30/1/14 , 200

123456 , 28/2/14 , 145.5

321321 , 30/1/14 , 311

321321 , 28/2/14 , 125.5

So while my original table had 52 weekly entries for each client with hours worked for each week.

So if I had 10 clients there woudl be 520 rows of data.

My final table should have 13 rows for each client thus onyl 130 rows and each row would have the max date for that particular 4 week period...

All suggestions appreciated.

kind regards

I take your point about the DDL but I think it doesnt give the full story...

This is why I wanted sample data. That way I could try the code. :hehe:

The problem is that I used the columns wrong in the OVER clause. It should read as follows:

DENSE_RANK() OVER( PARTITION BY customersID ORDER BY WeekEndingDate) - 1 )/ 4 drank

I'll correct my original post.

Have a look at these articles and see if they will help.

http://jasonbrimhall.info/2010/02/13/payperiod-ii/

Though the schemas are different, it seems to be the same general issue.

I thought that is what my solution did :blink:

It does not matter if you replace the CEILING/ROW_NUMBER with DENSE_RANK it will produce the same result in the end.

Hi Guys, Thanks for all the excellent idea's.

On further thought and examination of my needs , I think I need a stored procedure in which I pass in a StartDate ( a pay period ending date) and a StaffID number.( a pay period consist of 4 weeks!)

I have three simple tables as follows

table1 (StaffID, WeekendingDate, WorkedHours)

table2 (StaffID, WeekendingDate, ALHours)

table3 (StaffID, WeekendingDate, SLHours)

The StaffID will be in table1 but not every staff member will have an entery in table2 or table3.

So when the procedure receives that StaffID and DAte it queries each table and extracts a total WorkedHours, ALHours and SLHours (if data in table 2 and table 3 exits else nulls) between StartDate and StartDate- 23 Days!.

Then JOIN's each table by StaffID and returns a dataset I can display in a datagridview in vb.net !

I know this is different from what I initially suggested but this is because some Staff may have 4 weeks data in table1 and some may only have 1 weeks data, also they may have no data in the other 2 tables.

I hope that all makes sense, if not let me know.

kind regards

SELECTt1.StaffID,

SUM(t2.ALHours) AS [ALHours],

SUM(t3.SLHours) AS [SLHours]

FROMtable1 t1

LEFT JOIN table2 t2

ON t2.StaffID = t1.StaffID

AND t2.WeekendingDate = t1.WeekendingDate

LEFT JOIN table3 t2

ON t3.StaffID = t1.StaffID

AND t3.WeekendingDate = t1.WeekendingDate

WHEREt1.StaffID = @StaffID

ANDt1.WeekendingDate BETWEEN DATEADD(day,-23,@StartDate) AND @StartDate

Hi David,

Thank you so much for your answer, it looks like it will fit the bill alright

I just have one amendment I cant be sure that the dates in the tables will line up exactly .

In a perfect world they will but in reality users a rushing and type a slightly different date, so I would prefer a kind if catch all between 2 dates. So my suggestion would be something like this...

SELECTt1.StaffID,

SUM(t2.ALHours) AS [ALHours],

SUM(t3.SLHours) AS [SLHours]

FROMtable1 t1

LEFT JOIN table2 t2

ON t2.StaffID = t1.StaffID

AND t2.WeekendingDate BETWEEN DATEADD(day,-23,@StartDate) AND @StartDate

LEFT JOIN table3 t2

ON t3.StaffID = t1.StaffID

AND t3.WeekendingDate BETWEEN DATEADD(day,-23,@StartDate) AND @StartDate

WHEREt1.StaffID = @StaffID

ANDt1.WeekendingDate BETWEEN DATEADD(day,-23,@StartDate) AND @StartDate

Would you agree?

Also

That might give you a semi cross join which will lead to incorrect results. Date calculations can prevent that but you might get some inconsistencies. Check the following:

CREATE TABLE #table1 (StaffID int, WeekendingDate date, WorkedHours int)

CREATE TABLE #table2 (StaffID int, WeekendingDate date, ALHours int)

CREATE TABLE #table3 (StaffID int, WeekendingDate date, SLHours int)

INSERT INTO #table1 VALUES

(1,'20140504', 40),

(1,'20140511', 40),

(1,'20140518', 40)

INSERT INTO #table2 VALUES

(1,'20140503', 30),

(1,'20140510', 30),

(1,'20140517', 30)

INSERT INTO #table3 VALUES

(1,'20140504', 20),

(1,'20140511', 20),

(1,'20140518', 20)

DECLARE @StartDate date = GETDATE(),

@StaffID int = 1

SELECTt1.StaffID,

SUM(t2.ALHours) AS [ALHours],

SUM(t3.SLHours) AS [SLHours]

FROM#table1 t1

LEFT JOIN #table2 t2

ON t2.StaffID = t1.StaffID

AND t2.WeekendingDate BETWEEN DATEADD(day,-23,@StartDate) AND @StartDate

LEFT JOIN #table3 t3

ON t3.StaffID = t1.StaffID

AND t3.WeekendingDate BETWEEN DATEADD(day,-23,@StartDate) AND @StartDate

WHEREt1.StaffID = @StaffID

ANDt1.WeekendingDate BETWEEN DATEADD(day,-23,@StartDate) AND @StartDate

GROUP BY t1.StaffID

GO

DROP TABLE #table1

DROP TABLE #table2

DROP TABLE #table3

The best you could do is to add a CHECK constraint to your WeekendingDate column. That will ensure that all dates will be correct and you can join by date. Something like this:

CREATE TABLE Test(

TestDate date CHECK (DATEPART(dw, TestDate) = 7))

--And a test

GO

INSERT INTO Test VALUES( '20140101')

GO

INSERT INTO Test VALUES( '20140102')

GO

INSERT INTO Test VALUES( '20140103')

GO

INSERT INTO Test VALUES( '20140104')

GO

INSERT INTO Test VALUES( '20140105')

GO

INSERT INTO Test VALUES( '20140106')

GO

INSERT INTO Test VALUES( '20140107')

GO

SELECT * FROM Test

GO