I’m going to tell you a little secret. Come, sit closer.
You know your running chart for every other dashboard, showing
the SUM / Average / Top / Least / etc.
of Sales / New customers / Quotes / etc.
for the last… 1 hour / 1 day / 1 week / 4 weeks / 12 months / etc…. ?
In SQL world I’ve been solving the problem with dateadd() getdate().
sum(case when month(orderDate) = dateadd( mm, -1, getdate()) then orderAmount end) as [total order amount last month]
Of course if you want your chart to contain the last 12 months, you’d have to repeat the select 12 times. No brainer, redundant query. Here comes this little trick I’ve been using. Instead of repeating it 12 times, you can refactor it as:
orderDate >= dateadd(yy, -1, getdate())
ORDER BY --this is where the magic happens
The ORDER BY has it all.
On a completely unrelated note: USA won 2-1 against Ghana!
Conclusion: Now you know how to write even shorter scripts for your running charts.