Running sum for last 12 months, SQL style

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().

SELECT
sum(case when month(orderDate) = dateadd( mm, -1, getdate()) then orderAmount end) as [total order amount last month]
FROM
GoatOrders
GROUP BY
month(orderDate)

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:

SELECT
SUM(orderAmount), month(orderDate)
FROM
GoatOrders
WHERE
orderDate >= dateadd(yy, -1, getdate())
GROUP BY
month(orderDate),
year(orderDate)
ORDER BY –this is where the magic happens
year(orderDate) desc,
month(orderDate) desc

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 code for your running charts.

Sabermetrics and the best part of every four years

I mentioned three months ago that I’ll be joining the tenacious group of Yankees fans – at least for a season, we shall see how this works out.

Now I’ve upped my game to the next level, and joined edX’s Sabermetrics course. (Sabermetrics = the mathematical analysis of baseball.)

More on that later. In the meantime, ’tis the season for every living soul to focus on the undoubtedly most awesome sport the world has ever invented. Here is the Group Stage for 2014’s Fifa World Cup, because I know you will want to watch every game.

Conclusion: oh my God, oh my God, oh my God, 22 men chasing a ball!