Dynamically Aggregating Sales Figures by Date in SQL

Data and Databases
1

I’m looking for an SQL method that dynamically sums current and previous year sales until yesterday’s date. Below is my revised approach:

SELECT item_group,
       SUM(CASE WHEN order_date >= '2021-01-01' AND order_date < CONVERT(date, GETDATE()) THEN revenue_value ELSE 0 END) AS current_sales,
       SUM(CASE WHEN order_date >= '2020-01-01' AND order_date < DATEADD(day, -1, CONVERT(date, GETDATE())) THEN revenue_value ELSE 0 END) AS prior_sales
FROM order_summary
GROUP BY item_group;
2

hey, have u tried window functions for a cumulative sum? i tinkers with partition by year might simplify dynamic date ranges. what do u think, any other neat tricks or encountered issues?

3

In my experience, using a common table expression (CTE) to calculate and alias the necessary date boundaries simplifies the query and minimizes repeated calculations. This method allows for a more structured query that is easier to maintain and adjust, especially as criteria change over time. I have found that establishing these dynamic date variables early in the query enhances both readability and performance. Ensuring that the date column is properly indexed also contributes to faster execution times when operating on large datasets.

4

hey, try sticking with a subquery that calcualtes yesterday date. i found using cross apply sometimes roughs out the issues with mixed criteria in one query. also check indexes on dates if performance gets funky.

5

hey, i tried using a derived table to pre-calc the date ranges and it made the query neater. reduces repetition in your case statements and helps troubleshooting overlaps in dates. maybe give it a go!