October 26, 2024 at 2:37 pm
Hi,
I have a simple table with 5 columns as follows with about 80,000 rows, I wish to sum the Hours for each Client at 2 week intervals.
In other words starting at a particular EndDate, sum the Hours for each client for 2 week period.
ID, ClientID, EndDate, Hours
1 , 1234, 01/7/24, 10
2, 1234, 07/7/24, 5
3, 2222, 01/7/24, 4
4, 2222, 07/7/24, 6
5, 1234, 14/7/24, 12
6, 1234, 21/7/24, 2
Result
ClientID, EndDate, Hours
1234, 7/7/24, 15
2222, 7/7/24, 10
1234, 21/7/24, 14
I am not sure the best way to start, to either use a loop, which I was told was not the best way in sql or extract the 2nd date and sum where EndDate and the EndDate 1 week before matches for each client.
I am open to suggestions and I am sure there maybe an even simpler/smarter way of doing it without iterating?
October 26, 2024 at 3:15 pm
one way is to use a Calendar table (which could be overkill) so you know which weeks go together.
DATEPART(week,[date Column])
will give you the week of the year, and since it returns an integer, you can use integer division to create even bins
SELECT DATEPART(week,GETDATE())/2
It'll round down the closest integer. Then group by that.
October 26, 2024 at 5:04 pm
No directly usable sample data, so I can't test it, but maybe this:
DECLARE @first_week_ending_date date;
SET @first_week_ending_date = '20240707';
SELECT
ClientID,
DATEADD(DAY, @first_week_ending_date, CASE WHEN EndDate > @first_week_ending_date
THEN 14 ELSE 0 END) AS EndDate,
SUM(Hours) AS Hours
FROM dbo.your_table
WHERE EndDate >= DATEADD(DAY, -13, @first_week_ending_date) AND EndDate <= DATEADD(DAY, 14, @first_week_ending_date)
GROUP BY ClientID, DATEADD(DAY, @first_week_ending_date, CASE WHEN EndDate > @first_week_ending_date
THEN 14 ELSE 0 END)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 26, 2024 at 9:57 pm
So I tried this suggestion and it worked perfectly, except for the first row for each Client which is one less than the row above skewing all the data.
I tired different starting dates to see if I can sort it out but it didn't work!
See below, any ideas?
ClientID, EndDate, NewWk, Hours
22001672 2022-07-10 14 17.75
22001672 2022-07-17 15 21.25
22001672 2022-07-24 15 8.25
22001672 2022-07-31 16 22.25
22001672 2022-08-07 16 7.00 5
October 26, 2024 at 10:45 pm
Hi Scott,
My mistake I didnt explain it has to run for all months and years after the July too!
I tired you query and it works (had to rearrange the dateadd params), but works but only for the month of July does not continue for the rest of the months!
That why iteration seems to be the answer.
Any suggestions !
October 27, 2024 at 2:32 pm
I have developed Scotts query using a loop but still not getting the resutls I need. I have supplied better data for testing below...
CREATE TABLE Client( ID int, ClienID int, EndDate Date, Hours int);
INSERT INTO Client
VALUES
(1, 1234, 07/7/24, 10),
(2, 1234, 14/7/24, 5),
(3, 2222, 07/7/24, 4),
(4, 2222, 14/7/24, 6),
(5, 1234, 12/7/24, 12),
(6, 1234, 28/7/24, 2),
(7, 2222, 21/7/24, 4),
(8, 2222, 28/7/24, 4),
(9, 1234, 04/8/24, 9),
(10, 1234, 11/8/24, 2)
(11, 2222, 04/8/24, 6),
(12, 2222, 11/8/24, 7)
RESULTS
ClientID, EndDAte, HOurs
1234, 14/7/24, 15
2222, 14/7/24, 10
1234, 28/7/24, 14
2222, 28/7/24, 8
1234, 11/8/24, 11
2222, 11/8/24, 13
SET @first_week_ending_date = '2022-07-17';
While @first_week_ending_date <'2024-10-15'
BEGIN
INSERT INTO SampleTable (ClientID, EndDate, Hours)
SELECT ClientID,
DATEADD(DAY, CASE WHEN EndDate > @first_week_ending_date THEN 14 ELSE 0 END, @first_week_ending_date) AS EndDate, SUM(Hours) AS Hours
FROM MyTable
WHERE EndDate >= DATEADD(DAY, -13, @first_week_ending_date) --AND Weekending <= DATEADD(DAY, 14, @first_week_ending_date)
GROUP BY ClientID, EndDate,
DATEADD(DAY, CASE WHEN [EndDate] > @first_week_ending_date THEN 14 ELSE 0 END, @first_week_ending_date)
SET @first_week_ending_date = DATEADD(DAY, 14, @first_week_ending_date)
END
SELECT * FROM SampleTable
ORDER BY ClientID, EndDate
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply