SQL Server Constructing rolling quarter-year data from date variable

uurity8g  于 2023-06-21  发布在  其他
关注(0)|答案(1)|浏览(103)

I have a dataset with individual records of Sales and Cancellations, identified by individual (ID) and date (Date). My goal is to construct a new table in which I aggregate the individual records to have the cancellation-share by individual, year, and quarter always for the past 12 months (i.e. for Quarter 1 in 2023 it is April 2022 to March 2023, for Quarter 2 in 2023 it is July 2022 to June 2023). The final data should look like this:
| ID | Year | Quarter | Cancellation-share |
| ------------ | ------------ | ------------ | ------------ |
| 1 | 2022 | 4 | X |
| 1 | 2023 | 1 | X |
| 1 | 2023 | 2 | X |
| 2 | 2022 | 4 | X |
| ... | ... | ... | ... |
| ... | ... | ... | ... |

I have a SELECT-statement and work with a date parameter (@Today) that is set to 30th of June 2023, e.g.

  1. SELECT ID, 1-(SUM(Sales-Cancellations) / SUM(Sales)) AS Cancellation-share
  2. FROM Database
  3. WHERE Date BETWEEN DATEADD(Day, +1, DATEADD(Month, -12, @Today)) AND @Today

I considered to manually always go back by three months on the Between and AND Side, and alter the table to insert Year and Quarter manually, to finally UNION all constructed tables. However, this seems highly inefficient and error prone.

Since I wanna go back several years in time I want to write it dynamically and efficient. In the best case, I could state a min and max date, in between which the calculation is properly conducted to yield the desired results.

Unfortunately, I did not find threads that got me closer to a feasible solution for this specific setup.

I would be very thankful for any tips on how to tackle this, thank you very much in advance :)

nkoocmlb

nkoocmlb1#

When asking questions like this it's very helpful to provide easily reproducible DDL/DML. This makes it easy for folks to help you. I like to use table variables for this as there's no clean up required. Consider:

  1. DECLARE @DataTable TABLE (ID BIGINT IDENTITY, IndividualID BIGINT, Type NVARCHAR(1), Date DATE);
  2. INSERT INTO @DataTable (IndividualID, Type, Date)
  3. SELECT TOP 500 ROUND(((10 - 1) * Rnd1 + 1), 0), CASE ROUND(((2 - 1) * Rnd2 + 1), 0) WHEN 1 THEN 'S' WHEN 2 THEN 'C' END,
  4. DATEADD(DAY,-ROUND(((730 - 1) * Rnd3 + 1), 0),GETUTCDATE())
  5. FROM (Values (RAND(CONVERT(VARBINARY,NEWID(),1)), RAND(CONVERT(VARBINARY,NEWID(),1)), RAND(CONVERT(VARBINARY,NEWID(),1)))) a (Rnd1,Rnd2,Rnd3)
  6. CROSS APPLY sys.sysobjects z
  7. CROSS APPLY sys.sysobjects y;

This produces some random data which I think matches your description. It generates a Individual ID from 1 through 10, randomly decides if it should be a cancellation or a sale and a random date in the past two years.

You will likely want to use a calendar table or function. This will make it trivial to be able to group up your data. Consider:

  1. SELECT c.Date, c.Year, c.Quarter, a.IndividualID, COUNT(CASE WHEN d.Type = 'S' THEN 1 END) AS Sales, COUNT(CASE WHEN d.Type = 'C' THEN 1 END) AS Cancels
  2. FROM Calendar(2,0) c
  3. CROSS APPLY (SELECT DISTINCT IndividualID FROM @DataTable) a
  4. LEFT OUTER JOIN @DataTable d
  5. ON d.Date BETWEEN DATEADD(MICROSECOND,3,DATEADD(YEAR,-1,c.QuarterEndTime)) AND c.QuarterEndTime
  6. AND a.IndividualID = d.IndividualID
  7. WHERE c.Date = c.QuarterStartDate
  8. GROUP BY c.Date, c.Year, c.Quarter, a.IndividualID
  9. ORDER BY a.IndividualID, c.Date
DateYearQuarterIndividualIDSalesCancels
2021-01-0120211100
2021-04-0120212120
2021-07-0120213122
2021-10-0120214133
2022-01-0120221163
2022-04-0120222163
2022-07-0120223183
2022-10-0120224173
2023-01-0120231155
2023-04-0120232137
2021-01-0120211200
2021-04-0120212214
2021-07-01202132510
2021-10-01202142716
2022-01-012022121019
2022-04-012022221120
2022-07-01202232919
2022-10-012022421015
2023-01-01202312815
2023-04-01202322910
2021-01-0120211300
2021-04-0120212313
2021-07-0120213356
2021-10-012021431010
2022-01-012022131114
2022-04-012022231414
2022-07-012022331511
2022-10-012022431914
2023-01-012023132611
2023-04-012023232410
2021-01-0120211400
2021-04-0120212412
2021-07-0120213454
2021-10-0120214476
2022-01-01202214910
2022-04-012022241311
2022-07-012022341413
2022-10-012022441711
2023-01-012023142012
2023-04-012023241911
2021-01-0120211500
2021-04-0120212500
2021-07-0120213567
2021-10-01202145119
2022-01-012022151411
2022-04-012022251415
2022-07-012022351112
2022-10-01202245911
2023-01-012023151311
2023-04-01202325149
2021-01-0120211600
2021-04-0120212620
2021-07-0120213624
2021-10-0120214637
2022-01-0120221659
2022-04-01202226417
2022-07-01202236616
2022-10-01202246618
2023-01-01202316517
2023-04-01202326813
2021-01-0120211700
2021-04-0120212701
2021-07-0120213744
2021-10-0120214767
2022-01-012022171314
2022-04-012022271717
2022-07-012022371620
2022-10-012022471722
2023-01-012023171220
2023-04-012023271218
2021-01-0120211800
2021-04-0120212811
2021-07-0120213866
2021-10-01202148912
2022-01-012022181316
2022-04-012022281415
2022-07-012022381413
2022-10-01202248158
2023-01-01202318155
2023-04-01202328179
2021-01-0120211900
2021-04-0120212910
2021-07-0120213942
2021-10-0120214975
2022-01-01202219159
2022-04-012022291810
2022-07-012022391811
2022-10-012022491810
2023-01-012023191511
2023-04-012023291613
2021-01-01202111000
2021-04-01202121010
2021-07-01202131032
2021-10-01202141044
2022-01-01202211065
2022-04-01202221075
2022-07-01202231053
2022-10-01202241074
2023-01-01202311083
2023-04-01202321084

If you don't have a calendar table/function here's an example of one. It accepts two INTs representing the number of years to go back and forward.

  1. CREATE FUNCTION [dbo].[Calendar] (@StartYear INT, @EndYear INT)
  2. RETURNS @Calender TABLE (
  3. Date DATE, DateEndTime DATETIME2, Year INT, Month INT, Day INT, Quarter INT, WeekNumber INT, MonthName NVARCHAR(20), DayName NVARCHAR(20), WeekStartDate DATE, WeekEndDate DATE, MonthStartDate DATE, MonthEndDate DATE,
  4. QuarterStartDate DATE, QuarterEndDate DATE, YearStartDate DATE, YearEndDate DATE, WeekStartTime DATETIME2, WeekEndTime DATETIME2, MonthStartTime DATETIME2, MonthEndTime DATETIME2, QuarterStartTime DATETIME2, QuarterEndTime DATETIME2,
  5. YearStartTime DATETIME2, YearEndTime DATETIME2, IsWeekDay BIT)
  6. AS
  7. BEGIN
  8. WITH CalendarHistory AS (
  9. SELECT CAST(GETUTCDATE() AS DATE) AS Date
  10. UNION ALL
  11. SELECT DATEADD(DAY,-1,Date)
  12. FROM CalendarHistory
  13. WHERE DATEADD(DAY,-1,Date) > DATEADD(DAY,-1,DATEFROMPARTS(DATEPART(YEAR,DATEADD(YEAR,-@StartYear,GETUTCDATE())),1,1))
  14. ), CalendarFuture AS (
  15. SELECT CAST(DATEADD(DAY,1,GETUTCDATE()) AS DATE) AS Date
  16. UNION ALL
  17. SELECT DATEADD(DAY,1,Date)
  18. FROM CalendarFuture
  19. WHERE DATEADD(DAY,1,Date) < DATEFROMPARTS(DATEPART(YEAR,DATEADD(YEAR,@EndYear,GETUTCDATE())),1,1)
  20. ), Calendar AS (
  21. SELECT Date
  22. FROM CalendarHistory
  23. UNION ALL
  24. SELECT Date
  25. FROM CalendarFuture
  26. )
  27. INSERT INTO @Calender
  28. SELECT Date,
  29. DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,1,Date) AS DATETIME2)) AS DateEndTime,
  30. DATEPART(YEAR,Date) AS Year, DATEPART(MONTH,Date) AS Month, DATEPART(DAY,Date) AS Day, DATEPART(QUARTER,Date) AS Quarter, DATEPART(WEEK,Date) AS WeekNumber,
  31. DATENAME(MONTH,Date) AS MonthName, DATENAME(WEEKDAY,Date) AS DayName,
  32. DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date) AS WeekStartDate, DATEADD(DAY,7-DATEPART(WEEKDAY,Date),Date) AS WeekEndDate,
  33. DATEADD(DAY,1-DATEPART(DAY,Date),Date) AS MonthStartDate, DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date))) AS MonthEndDate,
  34. DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)) AS QuarterStartDate, DATEADD(DAY,-1,DATEADD(MONTH,3,DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)))) AS QuarterEndDate,
  35. DATEFROMPARTS(DATEPART(YEAR,Date),1,1) AS YearStartDate, DATEADD(DAY,-1,DATEFROMPARTS(DATEPART(YEAR,Date)+1,1,1)) AS YearEndDate,
  36. CAST(DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date) AS DATETIME2) AS WeekStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,1,DATEADD(DAY,7-DATEPART(WEEKDAY,Date),Date)) AS DATETIME2)) AS WeekEndTime,
  37. CAST(DATEADD(DAY,1-DATEPART(DAY,Date),Date) AS DATETIME2) AS MonthStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,0,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date))) AS DATETIME2)) AS MonthEndTime,
  38. CAST(DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)) AS DATETIME2) AS QuarterStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,0,DATEADD(MONTH,3,DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)))) AS DATETIME2)) AS QuarterEndTime,
  39. CAST(DATEFROMPARTS(DATEPART(YEAR,Date),1,1) AS DATETIME2) AS YearStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,0,DATEFROMPARTS(DATEPART(YEAR,Date)+1,1,1)) AS DATETIME2)) AS YearEndTime,
  40. CASE WHEN DATEPART(WEEKDAY,Date) IN (1,7) THEN 0 ELSE 1 END AS IsWeekDay
  41. FROM Calendar
  42. ORDER BY Date
  43. OPTION (MAXRECURSION 0)
  44. RETURN
  45. END;
展开查看全部

相关问题