Voice of the DBA

Tag: SQLNewBlogger

Comparing Daily Estimates to Actuals–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

In a previous post I wrote about using a few tables to capture information about my solar system. With a way to capture the data for each day, I now want to report on this. This post will look at the first part of my reporting, which is the daily reporting.

Scenario

On a daily basis, I want to know if the system is producing more or less than the estimate for that month. If you remember from the previous post, there is a single row in a table for each month and then a row in a different table for each day.

My estimates look like this:

Each day looks like:

What I want is a comparison of the actual output against the estimate for each day that I have data. I don’t want to see a number of zeros unless the system produced no power. What I really want is the estimate expanded to cover each of the days of the month for which I have actual data. I want to see this:

A Simple Join

This is very simple query to write. It’s really a join between the two tables, based on the month. If I join on month, then the data from the estimate is returned for each row of actual data where the month’s match.

I can then assemble the date in the results using DATEFROMPARTS(). When I do that, I have this code:
```
SELECT
   DATEFROMPARTS (spa.trackingyear, spa.trackingmonth, spa.trackingday) AS ProductionDate

, spa.actual_daily AS Actual

, spe.estimate_daily AS Estimate

FROM
   dbo.SolarPowerActual AS spa
   INNER JOIN dbo.SolarPowerEstimate AS spe
     ON spe.trackingmonth = spa.trackingmonth
     ORDER BY ProductionDate
```
This gives me the results I need, and it works well. Since I have numeric values for the months in both tables, this is a very quick join, especially when those columns are indexed. In this case, most of the time the index won’t matter as we really are pulling most of the data from one table and the tables are so narrow that the index might not ever help.

I’ll compile this code into a view, which I can use for more detailed analysis.

SQL New Blogger

I was building this system to track some data, and decided to split up each section into a separate post. If you look at the first post and this one, you will see they are both short and could be combined, but I wanted to separate them into different topics, as well as schedule them separately.

A good technique you can use on your blog to separate out topics and ensure a more consistent pipeline of content as you publish information about you knowledge.
May 4, 2022
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.

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.
April 20, 2022
Removing a LocalDB Instance

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

This might be obvious and easy, but I spent a couple minutes learning how to remove a LocalDB instance.

I tried to use remote, but that didn’t work:

There error reminded me that in my day, most commands use –? or /? to get help. A lot of CLIs these days use –help (two dashes). That doesn’t work here. But /? does.

This shows me the delete option is the one to use. I tried that, but I needed to stop the instance.

Running stop and then delete allowed this to succeed.

SQL New Blogger

After writing a previous post, which took me 10 minutes or so, I went to clean up my environment. I realized this was easy, but also worth a post about how I learned this.

Just 5 minutes. You could write posts like this to further your knowledge and help your career.

April 6, 2022
Starting LocalDB–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Lots of people have never worked with LocalDB, which is an in-process version of SQL Express. No service account, just a SQL Server instance running with your app. It’s a nice lightweight way to get SQL Server running quickly without a hassle.

This is a SQL Server Express version, but the bare bones for development. This post looks at how you can get this running.

This version of SQL is installed with SQL Express, and with Visual Studio. If you look in this path: C:\Program Files\Microsoft SQL Server\150\Tools\Binn, there is a SQLLocalDB.exe. You can see that here.

This is my SQL Server 2016 version of LocalDB. I can start a new instance by calling this with the CREATE option. I can give this a name as well, as I might want to stat multiple instance for different apps. Here I’ll create an instance called app1.
```
SQLLocalDB create app1
```
I then call the same command, but use START instead of CREATE. You can see this reports as started from the CLI. I also add the INFO call to get status.

Now I can connect. I use (LocalDB)\app1 to connect:

I see I’m connected to a version of LocalDB then:

Now it’s just an instance of SQL Server I can use.

SQL New Blogger

I needed to check something for a customer and realized I hadn’t started LocalDB in a long time, so I needed to check the docs. I spent 10 minutes putting this post together.

An easy type of post for any of you out there. Learn something, try something, write something.
March 28, 2022