Microsoft SQL Server TSQL Challenge #1

Most of my experience the past 8 years has been with Microsoft .NET and C# programming language, for both windows applications and also web applications. I also have over 10 years experience working with Microsoft SQL Server databases and Transact SQL, including ETL experience with SQL Server Integration Services (SSIS), and reporting experience with SQL Server Reporting Services (SSRS). Some time ago I was given a evaluation as part of a job interview to evaluate my SQL skills, the evaluation was not all that difficult, but the best solution to the evaluation displayed some important and not very well known methods of using SQL with SQL Server which I learned over the years.

I was given this TSQL script to create the table, SQLTEST, for the evaluation, I provide the script as text that can be copied and pasted and used SQL Server Management Studio (SSMS). 

-- create table for sql evaluation test
CREATE TABLE [dbo].[SQLTest] (
[ID] uniqueidentifier NOT NULL,
[Period] int,
[Amount] money,
CONSTRAINT [PK_SQLTest] PRIMARY KEY ([ID]))
GO

-- insert new ID as unique identifier with each new record
ALTER TABLE [dbo].[SQLTest] ADD CONSTRAINT [DF_SQLTest_ID] DEFAULT (newid()) FOR [ID]
GO

-- insert test data into table
INSERT INTO [dbo].[SQLTest] ([Period], [Amount])
SELECT 1,500.00 UNION ALL
SELECT 6,300.00 UNION ALL
SELECT 9,20.00 UNION ALL
SELECT 13,200.00 UNION ALL
SELECT 17,800.00 UNION ALL
SELECT 22,750.00 UNION ALL
SELECT 23,50.00 UNION ALL
SELECT 24,550.00 UNION ALL
SELECT 31,100.00
GO

-- select contents of SQLTest table
SELECT [Period], [Amount]
FROM [dbo].[SQLTest]
ORDER BY Period ASC;

This script would create the SQLTest table with sales info per each period, with some periods with no sales info that are left out. The data in the table is the following after creation, minus the uniqueid ID column for readability.

The solution to the problem is to return all the records, including for periods with no sales, and to also calculate the running total of sales up until that particular period. The solution record set should look like the following:

Now, thinking about a solution for this problem, as a procedural programmer the first thing I would think is that I need a loop, in TSQL looping is done with a CURSOR. But one thing to remember about SQL programming, there is usually a way to solve the problem without the need of a procedural programming feature like looping using a CURSOR, a CURSOR should only be a last resort if nothing else works, but with SQL it is amazing what you can do.

The first step to the solution is to create a temporary table in the database to hold all 31 periods, instead of just using the source table which does not have periods with sales of 0.00. Temporary tables can be very useful in TSQL work, you simply delete them when they are no longer needed. Normally, temp table names start with a # . The following TSQL will create the temp table that we will use for the solution, and also populate the temp table with 31 periods. 

-- create temp table to hold the periods
CREATE TABLE #Periods
(
 	Period int NOT NULL
)

-- populate the Periods table with numbers up until the max period in SQLTest table, dynamically
DECLARE @Period int;
DECLARE @Limit int;
SELECT @Period = 1;

-- create temp table with 1 row for each period, up to 32 periods
SELECT @Limit = MAX(Period) FROM SQLTest;
SET NOCOUNT ON
WHILE @Period <= @Limit
BEGIN
  INSERT INTO #Periods VALUES (@Period);
  SELECT @Period = @Period + 1;
END;
SET NOCOUNT OFF

Now we work on the actual query that will generate the solution to this problem, using CASE statements and also SUBQUERIES, we can do the solution without the need for a CURSOR to do looping.

In our query, we want to generate a row with amount of 0.00 if that period does not occur in the source data SQLTest table, if that period is present in SQLTest table, we want to list the amount from the SQLTest table in the amount column in the results. We do this using CASE statements, which are similar to IF conditions in procedural programming. Then to calculate the running total column, we use a subquery that uses the Period from the main query to sum up the total.

This is the real genius of the solution is the subquery. A great feature of subqueries is that you can use a WHERE condition that checks the current value of the Period in the main query, for example, the following WHERE condition in the subquery: 

WHERE sqltest.Period <= periods.Period

In this statement, the table periods is the Periods temp table in the main query, when that value is say 2, then the subquery looks for matching rows in the SQLTest table also for the value of 2, when the results of the main query for Period is 3, then the same thing, the subquery also checks for matching records for period 3. In this case we are doing a Sum so we want to get all amounts for all records equal to or less than the current period in the main query.

The solution to this SQL Challenge is the following TSQL Query. Do not forget to delete (DROP) your temp table when done with it! 

-- get the running total from the SQLTest table for each period,
-- including periods not included in SQLTest, which have Amount = 0.00
SELECT periods.Period AS Period,
  CASE
  WHEN ((SELECT COUNT(sqltest.Period) FROM SQLTest sqltest WHERE sqltest.Period = periods.Period) = 0)
    THEN 0.00
  WHEN ((SELECT COUNT(sqltest.Period) FROM SQLTest sqltest WHERE sqltest.Period = periods.Period) = 1)
    THEN (SELECT sqltest.Amount FROM SQLTest sqltest WHERE sqltest.Period = periods.Period)
END
AS Amount,
(SELECT SUM(sqltest.Amount) FROM SQLTest sqltest WHERE sqltest.Period <= periods.Period) AS RunningTotal
FROM #Periods periods;

-- delete temp table
DROP TABLE #Periods;
GOO

Also, some people in their TSQL use the AS keyword to rename a table or table column, another great feature of SQL in general, you do not even need the keyword AS, simply have the name you want for the table after the table name with a space between them. The following SQL renames the #Periods temp table to periods: 

FROM #Periods periods;

The solution to this SQL challenge is a good example of how to use CASE statements and SUBQUERIES to get results for your data you are working with. I only found out about these solutions after years of working with Microsoft SQL Server databases, and I also have experience with other databases such as Oracle, MySQL, and MariaDB.

The following is the TSQL code for the entire solution, which can be copy and pasted:

Also copy and pastable text of this SQL script is available at my Azure DevOps source code repository at the following URL:

SQLChallenge1.sql 

-- create table for sql evaluation test
CREATE TABLE [dbo].[SQLTest] (
[ID] uniqueidentifier NOT NULL,
[Period] int,
[Amount] money,
CONSTRAINT [PK_SQLTest] PRIMARY KEY ([ID]))
GO

-- insert new ID as unique identifier with each new record
ALTER TABLE [dbo].[SQLTest] ADD CONSTRAINT [DF_SQLTest_ID] DEFAULT (newid()) FOR [ID]
GO

-- insert test data into table
INSERT INTO [dbo].[SQLTest] ([Period], [Amount])
SELECT 1,500.00 UNION ALL
SELECT 6,300.00 UNION ALL
SELECT 9,20.00 UNION ALL
SELECT 13,200.00 UNION ALL
SELECT 17,800.00 UNION ALL
SELECT 22,750.00 UNION ALL
SELECT 23,50.00 UNION ALL
SELECT 24,550.00 UNION ALL
SELECT 31,100.00
GO

-- create temp table to hold the periods
CREATE TABLE #Periods
(
  Period int NOT NULL
)

-- populate the Periods table with numbers up until the max period in SQLTest table, dynamically
DECLARE @Period int;
DECLARE @Limit int;
SELECT @Period = 1;

-- create temp table with 1 row for each period, up to 32 periods
SELECT @Limit = MAX(Period) FROM SQLTest;
SET NOCOUNT ON
WHILE @Period <= @Limit
BEGIN
    INSERT INTO #Periods VALUES (@Period);
    SELECT @Period = @Period + 1;
END;
SET NOCOUNT OFF

-- get the running total from the SQLTest table for each period,
-- including periods not included in SQLTest, which have Amount = 0.00
SELECT periods.Period AS Period,
  CASE
    WHEN ((SELECT COUNT(sqltest.Period) FROM SQLTest sqltest WHERE sqltest.Period = periods.Period) = 0)
      THEN 0.00
    WHEN ((SELECT COUNT(sqltest.Period) FROM SQLTest sqltest WHERE sqltest.Period = periods.Period) = 1)
	THEN (SELECT sqltest.Amount FROM SQLTest sqltest WHERE sqltest.Period = periods.Period)
END
AS Amount.
(SELECT SUM(sqltest.Amount) FROM SQLTest sqltest WHERE sqltest.Period <= periods.Period) AS RunningTotal
FROM #Periods periods;

-- delete temp table
DROP TABLE #Periods;
GO

To see my Curriculum Vitae, go to www.michaelgworkman.com

To see my projects on Azure DevOps, go to https://dev.azure.com/AbionTechnology/

To see my Posts and Answers on Stack Overflow, go to Michael G. Workman on Stack Overflow

If you have any questions about C, C++, Microsoft C# .NET,  Microsoft Azure Cloud, Unix, Linux, and/or Mobile Apps, please feel free to contact me by email at:

michael@abion.net

Popular Posts