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.
Update: Someone noted this might not be clear how this works, so I’ll do another post on more details of the query itself. FWIW, another good way to get moving with SQLNewBlogger and add new posts to add detail.
I’ve added the CREATE and INSERT statements here:
CREATE TABLE [dbo].[SolarPowerTracker]
( ProductionDate DATE CONSTRAINT SolarPowerTrackerPK PRIMARY KEY
, Actual NUMERIC(10, 2)
, Estimate NUMERIC(10, 2));
GO
INSERT INTO dbo.SolarPowerTracker
(ProductionDate, Actual, Estimate)
VALUES
( N'2022-02-23', 11.7530, 41.65 ),
( N'2022-02-24', 46.7710, 41.65 ),
( N'2022-02-25', 71.2480, 41.65 ),
( N'2022-02-26', 72.0820, 41.65 ),
( N'2022-02-27', 69.8990, 41.65 ),
( N'2022-02-28', 69.0050, 41.65 ),
( N'2022-03-01', 68.9900, 43.96 ),
( N'2022-03-02', 65.1330, 43.96 ),
( N'2022-03-03', 61.1790, 43.96 ),
( N'2022-03-04', 33.2930, 43.96 ),
( N'2022-03-05', 10.1330, 43.96 ),
( N'2022-03-06', 0.6170, 43.96 ),
( N'2022-03-07', 4.2670, 43.96 ),
( N'2022-03-08', 47.7440, 43.96 ),
( N'2022-03-09', 11.5580, 43.96 ),
( N'2022-03-10', 0.6470, 43.96 ),
( N'2022-03-11', 15.4400, 43.96 ),
( N'2022-03-12', 70.3260, 43.96 ),
( N'2022-03-13', 61.3710, 43.96 ),
( N'2022-03-14', 74.5110, 43.96 )
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.
Voice of the DBA 