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.