March 1, 2019 at 12:22 pm
Good afternoon I need to calculate rolling 12 month count I.e. 1/1/2018 - 12/31/2018 is period 1 , 2/1/2018 - 1/30/2019 is period 2. This would be completed from 1/1/2004 to 2/28/2019 should be 342 rolling 12 month periods
The data set does not provide a monthly reported value for each month, so we must assume the value remains the same until we see a new Create_date value or we can think of it as a new status_history_id will be created to report the status of the id, so status history is unique.
Hoping this sample data provides some clarity.
| status_history_id | status | id | Create_Date | Update_Date |
| 14260 | 1 | 3 | 4/25/2005 | 4/25/2005 |
| 81766 | 8 | 3 | 6/25/2009 | 6/25/2009 |
| 87423 | 1 | 3 | 10/5/2009 | 10/5/2009 |
| 225558 | 8 | 3 | 8/9/2013 | 8/9/2013 |
So I need to create a status value = 1 for every id for months 5/2005 - 5/2009, status value =8 for 7/2005 - 9/2005, status value 1 for months 11/2009 - 7/2013 and lastly status value 8 from 7/2013 - 2/2019.
I planned on creating a temp table with these new and existing values so that a rolling 12 month count of id grouped by status and Month year can be completed. Hoping there is someone more creative than me out there. Thanks
March 1, 2019 at 1:26 pm
Assuming that the data is already summarized by month, you just need to COUNT(<some field> ) OVER(PARTITION BY <partition fields> ORDER BY <date field> ROWS BETWEEN 11 PRECEDING AND CURRENT ROW). If you want a more specific answer, post consumable data.
Drew
Edited to add a space so the smiley face didn't show up.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 6, 2019 at 9:05 am
Here we go:IF OBJECT_ID(N'tempdb..#StatusHistory', N'U') IS NOT NULL
BEGIN;
DROP TABLE #StatusHistory;
END;
CREATE TABLE #StatusHistory (
Status_History_Id int NOT NULL PRIMARY KEY CLUSTERED,
[Status] tinyint NOT NULL,
id tinyint NOT NULL,
Create_Date date,
Update_Date date,
Create_Month_Date AS DATEFROMPARTS(YEAR(Create_Date), MONTH(Create_Date), 1),
RowNum int IDENTITY(1,1) NOT NULL
);
INSERT INTO #StatusHistory (Status_History_Id, [Status], id, Create_Date, Update_Date)
VALUES (14260, 1, 3, '04/25/2005', '04/25/2005'),
(81766, 8, 3, '06/25/2009', '06/25/2009'),
(87423, 1, 3, '10/05/2009', '10/05/2009'),
(225558, 8, 3, '08/09/2013', '08/09/2013');
DECLARE @MinMonthDate AS date,
@LastMth AS date = DATEADD(month, -1, GETDATE());
DECLARE @MaxMonthDate AS date = DATEFROMPARTS(YEAR(@LastMth), MONTH(@LastMth), 1);
SELECT
@MinMonthDate = DATEFROMPARTS(YEAR(MIN(SH.Create_Date)), MONTH(MIN(SH.Create_Date)), 1)
FROM #StatusHistory AS SH;
PRINT @MinMonthDate;
PRINT @MaxMonthDate;
WITH N AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
),
ALL_DATES AS (
SELECT X.THE_DATE
FROM (
SELECT DATEADD(month, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @MinMonthDate) AS THE_DATE
FROM N AS N1, N AS N2, N AS N3
) AS X
WHERE X.THE_DATE <= @MaxMonthDate
),
DATE_RANGES AS (
SELECT
SH1.RowNum,
SH1.Create_Month_Date,
ISNULL(SH2.Create_Month_Date, @MaxMonthDate) AS NextCRDate
FROM #StatusHistory AS SH1
LEFT OUTER JOIN #StatusHistory AS SH2
ON SH1.RowNum + 1 = SH2.RowNum
),
ALL_DATA AS (
SELECT
DR.RowNum,
SH.Status_History_Id,
SH.[Status],
SH.id,
AD.THE_DATE AS MonthDate
FROM DATE_RANGES AS DR
INNER JOIN #StatusHistory AS SH
ON DR.RowNum = SH.RowNum
LEFT OUTER JOIN ALL_DATES AS AD
ON AD.THE_DATE BETWEEN DR.Create_Month_Date AND DR.NextCRDate
)
SELECT
AD.Status_History_Id,
AD.[Status],
AD.id,
COUNT(DISTINCT AD.MonthDate) AS THE_COUNT,
MIN(AD.MonthDate) AS DATE_RANGE_BEGIN,
MAX(AD.MonthDate) AS DATE_RANGE_END
FROM ALL_DATA AS AD
GROUP BY
AD.Status_History_Id,
AD.[Status],
AD.id
ORDER BY
AD.Status_History_Id,
AD.[Status],
AD.id;
DROP TABLE #StatusHistory;
I'm quite certain that someone can improve this with the use of either LEAD or LAG to replace the SELF-JOIN.
I just didn't have anything but SQL 2008 to work with at the moment, so I did what I could within my limits.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 6, 2019 at 3:29 pm
Thanks for offering a solution Steve, so the challenge for me is converting the code into a workable solution
You added a field RowNum as part of the temp table you created when importing the data I provided and then used that value as part of your selection and that value is not part of the existing table stumbling as I try to work through that piece.
thanks for the help your response was timely
March 7, 2019 at 12:34 pm
rickyschroder - Wednesday, March 6, 2019 3:29 PMThanks for offering a solution Steve, so the challenge for me is converting the code into a workable solution
You added a field RowNum as part of the temp table you created when importing the data I provided and then used that value as part of your selection and that value is not part of the existing table stumbling as I try to work through that piece.
thanks for the help your response was timely
It might help to test my query by taking it one CTE at a time, and letting the final select just choose * from that CTE name, and you can start to see what each one is actually doing. It often helps me test things and verify that I have each piece correct before assembling a whole thing. Let me know if you have any questions on how I made use of the RowNum.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply