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.
SELECT ID, 1-(SUM(Sales-Cancellations) / SUM(Sales)) AS Cancellation-share
FROM Database
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 :)
1条答案
按热度按时间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:
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:
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.