A Monthly Running Total–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Recently I was looking at some data and wanted to analyze it by month. I have a goal that is set for each day and then an actual value. I wanted to know how I was tracking against the goal, as a running total. If my goal is 10 a day, then I ought to actually get to 10 the first day, 20 for the second day (10 + 10), etc.

Here is some data that I am using, showing the date, the actual, and the estimate:

The estimate is constant, so a running total is just the sum of all previous rows. The actual is similar, though in both cases, I want to reset this for each month. If I did a straight sum of all previous rows, I’d see something like this:

I don’t want this. Instead, I want something that’s like this:

This is fairly easy to do with window functions in T-SQL. I use a SUM() for each column with an OVER() clause. In this case, I partition by the year and month, which means that when those items change, we reset a new set of values. Here is the query that produces the correct data above:

SELECT
   spt.ProductionDate
, SUM (spt.Actual) OVER (PARTITION BY
                            YEAR (spt.ProductionDate)
                          , MONTH (spt.ProductionDate)
                          ORDER BY spt.ProductionDate
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS AcutalRunningTotal
, SUM (spt.Estimate) OVER (PARTITION BY
                              YEAR (spt.ProductionDate)
                            , MONTH (spt.ProductionDate)
                            ORDER BY spt.ProductionDate
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS EstimateRunningTotal
FROM dbo.SolarPowerTracker AS spt;

This creates a window for each month (based on year and month) and groups all the data with the same values together. Then I get the sum as a running total. I also want a rows clause to be sure this works as intended.

SQL New Blogger

As I was working on this query, I realized it wasn’t complex, but it was something unusual. Often I’ve done totals for a time period that a user supplies, not a set one like a month with a reset each month. I thought this was a good way to showcase how to solve this relatively simple problem.

I spent about 15 minutes taking my code and then writing this post to show how I solved a a problem. This is something you could add on your blog to showcase your knowledge on solving a specific problem, not a general one.