Tag: SQLNewBlogger

Finding Memorial Day–#SQLNewBlogger

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

It’s Memorial Day in the US. A holiday, though I’m off on a trip to Germany today.

I wanted to have a fun Memorial Day Question of the Day today, and I decided to write some code to calculate Memorial Day. This post looks at how the code works.

The Algorithm

Memorial day is always the last Monday in May. For me, I decided to find all the Mondays in May and then take the last one. I started with a tally table to get a list of days. In any given year, we can find the first day of the year with this code:

DATEADD (yy, DATEDIFF (yy, 0, GETDATE ()), 0)

Now I use a DATEADD with my tally table to find the first 200 days of the year. The end of May will always fall in this number of days. Here is the code for a list of the first 200 days of the current year:

WITH myTally (n)
AS ( SELECT n = ROW_NUMBER () OVER (ORDER BY
                                       (SELECT NULL))
      FROM
        ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a (n)
        CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b (n)
        CROSS JOIN ( VALUES (1), (2)) c (n) )
    , cteCurrYearDates (myDate)
AS ( SELECT DATEADD (DAY, n, DATEADD (yy, DATEDIFF (yy, 0, GETDATE ()), 0))
      FROM myTally)
    , cteMay (Mondays)

Once I have this, I now need to get the Mondays in May. I can use the DATEPART and MONTH functions to find this. Actually, I ought to use DATEPART to be consistent here, but my habit is MONTH() for the month.

I also need to set the DATEFIRST for this code, otherwise the day of the week will be inconsistent. Here’s the code for this:

SET DATEFIRST 7;
WITH myTally (n)
AS ( SELECT n = ROW_NUMBER () OVER (ORDER BY
                                       (SELECT NULL))
      FROM
        ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a (n)
        CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b (n)
        CROSS JOIN ( VALUES (1), (2)) c (n) )
    , cteCurrYearDates (myDate)
AS ( SELECT DATEADD (DAY, n, DATEADD (yy, DATEDIFF (yy, 0, GETDATE ()), 0))
      FROM myTally)
    , cteMay (Mondays)
AS ( SELECT cteCurrYearDates.myDate
      FROM cteCurrYearDates
      WHERE
        DATEPART (WEEKDAY, cteCurrYearDates.myDate) = 2
        AND MONTH(cteCurrYearDates.myDate) = 5
)

This gives me a list of Mondays in May for the current year. I want the last one, which isn’t easy to do in a result set. However, I can get the first one with a TOP 1 limit. The easy way to get the last one is reverse the order of the rows and then take the first one. I do this with an ORDER BY.

Here’s the complete code:

SET DATEFIRST 7;
WITH myTally (n)
AS ( SELECT n = ROW_NUMBER () OVER (ORDER BY
                                       (SELECT NULL))
      FROM
        ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a (n)
        CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b (n)
        CROSS JOIN ( VALUES (1), (2)) c (n) )
    , cteCurrYearDates (myDate)
AS ( SELECT DATEADD (DAY, n, DATEADD (yy, DATEDIFF (yy, 0, GETDATE ()), 0))
      FROM myTally)
    , cteMay (Mondays)
AS ( SELECT cteCurrYearDates.myDate
      FROM cteCurrYearDates
      WHERE
        DATEPART (WEEKDAY, cteCurrYearDates.myDate) = 2
        AND MONTH(cteCurrYearDates.myDate) = 5
)
SELECT --TOP 1
        Mondays
FROM cteMay
ORDER BY cteMay.Mondays DESC;

Now I can get Memorial Day for the current year.

SQL New Blogger

This is a great example of breaking down an algorithm and explaining it to the reader. If you write T-SQL code for a living, you might write a series of posts on how you solve various problems in T-SQL and explain the process. Link to places where you learn, and show some results that give a feeling for how you built the code.

This took about 10 minutes to write once I’d built all the code. I didn’t go into details with individual result sets, but you could easily do that to show how the code works.

May 30, 2022

Posting Data and Code–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Here are some hints to get started.

I wrote a SQL New Blogger post recently on running totals and used some images to show data. A reader sent me a note to say it would be nice to see the code that helps put the post together. I thought that was a good suggestion, so that’s this post.

How can you add code and data to your post?

Publishing Code

There are a number of ways to add code to a blog post. Some of the platforms have specific plugins to handle different types of code. There are also some plugins in Open Live Writer, which I use, for code.

I’ve stopped using those.

It’s simpler to me to just use the PRE tag, for selecting preformatting for a set of code. I use WordPress here, and when I added the code used in this post, I highlighted the code and then selected the Preformatted style. This is shown below.

The PRE tag works well for me and it’s simple. If you want to spend the time configuring code for colors specific to your language, feel free, but I am less concerned about that.

Generating CREATE Statements

For most demo set ups, I create a new table and I copy the code over. For example, I was doing some DDM testing and I made this table.

To add that to this post, I’d CTRL+A to select all the code and then copy it. I can paste it below and then format with the PRE tag.
```
CREATE TABLE [dbo].[DDMEmailTest](
     [MyID] [int] IDENTITY(1,1) NOT NULL  CONSTRAINT [DDMEmailTestPK] PRIMARY KEY ,
     [MyName] [varchar](100) NULL,
     [Email] [varchar](100) MASKED WITH (FUNCTION = 'email()') NULL,
     [Salary] [int] NULL,
GO
```
In LiveWriter there isn’t a PRE tag, so I just quickly edit the source and add a “re” to the paragraph tags. You can see where I’ve added PRE below, highlighted by the arrow:

The other option is to script the table to the Clipboard. I can do this in SSMS easily.

I then paste the code into Live Writer.

Note, I usually edit out the USE and SET statements.

Adding INSERT Statements

After the DDL for tables is around, the next important thing for readers is data. If you have the INSERT statements from your testing, then use those. As above, paste those in here.

If not, there are a few options. I have SQL Prompt, so I can select from the table, highlight the results, and then “script as insert”.

The other option is to “build” an insert statement like this:
```
SELECT TOP 10
        '(''' + CAST(spt.ProductionDate AS VARCHAR(10)) + ''', ' +
       + CAST(spt.Actual AS VARCHAR(10)) + ', '
       + CAST(spt.Estimate AS VARCHAR(10)) + ')'
FROM dbo.SolarPowerTracker AS spt;
```
I can wrap the INSERT tablename VALUES in front of this and then paste this into the post.

SQL New Blogger

Not really a technical post, but this does show a little technical skill. Writing about how you solve problems or build on other posts is a good way to show you have some varied skills that an employer might like.

This post took me about 15 minutes to write.
May 11, 2022
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