I want to group the data by consecutive months (mydate). how can i do this?
Data looks like this:
mydate enddate itemnum amount
01/22/24 03/22/24 1234 100.00
02/11/24 02/28/24 2223 200.00
03/05/24 04/02/24 4948 150.00
05/12/24 05/22/24 5353 100.00
05/22/24 06/21/24 8883 125.00
06/01/24 07/03/24 1212 525.00
08/04/24 09/22/24 4243 100.00
Results should return :
01/22/24 03/22/24 1234 450.00
05/12/24 05/22/24 5353 750.00
08/04/24 09/22/24 4243 100.00
November 15, 2024 at 12:07 am
Pretend I'm dumb as a rock and explain how your result is correct and how you got there.
Are you talking about something like the "Islands Question" discussed by Itzik Ben-Gan here?
November 15, 2024 at 12:28 am
In my results, I'm grouping by mydate on consecutive months. So the first 3 rows are consecutive months but the next row is 05 which skips a month. So i'm grouping the first 3 rows. Rows 4, 5,6 are consequtive so they are grouped and the last row is by itself so it is grouped by itself. I'm summing the amount and in my grouping I'm only displaying the first row of my group. Does this make sense?
November 15, 2024 at 5:23 am
So essentially you're grouping islands together. (The island consists of consecutive months of data). Fancy that! I found an old Dwain Camps article! (I miss his posts... he could figure stuff out AND write about it) I know Itzik Ben-Gan wrote articles about it too. There's a bit of it in Chapter 4 of his book T-SQL Querying. I'm pretty sure there are a bunch of articles he wrote about it kicking about on the internet too. Dig around.
Merhaba,
Sorguyu deneyebilir misin
IF OBJECT_ID('my_table', 'U') IS NOT NULL
DROP TABLE my_table;
CREATE TABLE my_table (
mydate DATE,
enddate DATE,
itemnum INT,
amount DECIMAL(10, 2)
);
INSERT INTO my_table (mydate, enddate, itemnum, amount) VALUES
('2024-01-22', '2024-03-22', 1234, 100.00),
('2024-02-11', '2024-02-28', 2223, 200.00),
('2024-03-05', '2024-04-02', 4948, 150.00),
('2024-05-12', '2024-05-22', 5353, 100.00),
('2024-05-22', '2024-06-21', 8883, 125.00),
('2024-06-01', '2024-07-03', 1212, 525.00),
('2024-08-04', '2024-09-22', 4243, 100.00);
WITH GroupedMonths AS (
SELECT
mydate,
enddate,
itemnum,
amount,
DATEDIFF(MONTH, LAG(mydate) OVER (ORDER BY mydate), mydate) AS month_gap,
ROW_NUMBER() OVER (ORDER BY mydate) AS row_num
FROM my_table
),
GroupIdentifiers AS (
SELECT
mydate,
enddate,
itemnum,
amount,
SUM(CASE WHEN month_gap > 1 OR month_gap IS NULL THEN 1 ELSE 0 END)
OVER (ORDER BY row_num) AS group_id
FROM GroupedMonths
),
AggregatedResult AS (
SELECT
MIN(mydate) AS mydate,
MAX(enddate) AS enddate,
MIN(itemnum) AS itemnum,
SUM(amount) AS total_amount
FROM GroupIdentifiers
GROUP BY group_id
)
SELECT
mydate,
enddate,
itemnum,
total_amount
FROM AggregatedResult
ORDER BY mydate;
Allah bize yeter, O ne güzel vekildir.
vedatoozer@gmail.com
November 15, 2024 at 11:24 am
Vedat, Sen bir dahisin, teşekkür ederim!
Thank you! it worked.
November 15, 2024 at 11:26 am
So essentially you're grouping islands together. (The island consists of consecutive months of data). Fancy that! I found an old Dwain Camps article! (I miss his posts... he could figure stuff out AND write about it) I know Itzik Ben-Gan wrote articles about it too. There's a bit of it in Chapter 4 of his book T-SQL Querying. I'm pretty sure there are a bunch of articles he wrote about it kicking about on the internet too. Dig around.
Thank you! this is a great read and explanation.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply