11

I have a SQL query that looks for values in a table on a range of dates.

If no record is found, I would like to generate rows with default values.

Example of one of the table existing records:

DeviceId Time Context Value
1 2022-02-10 Connected False

So a query that restricts on the Time column between 2022-02-07 and 2022-02-10 must create fake rows for February 7th, 8th, and 9th but not for the 10th because that already exists.

Expected result:

DeviceId Time Context Value
1 2022-02-7 Fake False
1 2022-02-8 Fake False
1 2022-02-9 Fake False
1 2022-02-10 Connected False

How can I do that? With a recursive CTE?

0

3 Answers 3

10

When I think about what you're trying to accomplish, I would describe it in this way, using "plain English":

  • Return the results of some query
  • But if no results exist, then return some default values.

My thought process took the immediate leap of "What if I always include the defaults, but then somehow filter them out when there exist real results.

After pondering over my cup of morning coffee, I realized this is actually pretty easy to do with CTEs. No recursion needed, but I will use two CTEs.

That real query

Lets start by throwing your real query into a CTE. This makes it easy to reference the results from the query multiple times, pretty easily. In this example, I'm just going to query sys.objects, and put the whole darn thing into a CTE:

DECLARE @ObjectName nvarchar(128) = N'sysschobjs';

RealQuery AS (
    SELECT object_id, name
    FROM sys.objects
    WHERE name = @ObjectName
    )
SELECT *
FROM RealQuery;

Now for the defaults

I'm doing the same treatment here. Just making up some default placeholders, and abstracting them away into a CTE that I can reference easily. Maybe your default values are stored in some table somewhere, or maybe you prefer to stuff them into a #temp table or table @variable, in which case you wouldn't need to use a CTE here.

WITH Defaults AS (
    SELECT *
    FROM (VALUES (1,N'One'),(2,N'Two'),(3,N'Three')) AS x(Id,Name)
    )
SELECT *
FROM Defaults;

Time for a mashup

Now, with our real query in a CTE, and our "default" values in another, I simply UNION ALL the "real" results and the default place holders. The "magic" is to use WHERE NOT EXISTS (SELECT 1 FROM RealQuery) to control whether those defaults are included.

This returns a single row, matching the object name sysschobjs, and does not return the default placeholders:

DECLARE @ObjectName nvarchar(128) = N'sysschobjs';

WITH Defaults AS (
    SELECT *
    FROM (VALUES (1,N'One'),(2,N'Two'),(3,N'Three')) AS x(Id,Name)
    ),
RealQuery AS (
    SELECT object_id, name
    FROM sys.objects
    WHERE name = @ObjectName
    )
SELECT *
FROM RealQuery
UNION ALL
SELECT *
FROM Defaults
WHERE NOT EXISTS (SELECT 1 FROM RealQuery);

SSMS Results of one value for sysschobjs

And if you change that first line to a value that doesn't exist in sys.objects then you'll get the placeholder default results instead:

DECLARE @ObjectName nvarchar(128) = N'AMtwo';

image of three placeholder results, the numbers One, Two, Three spelled out in words

There are other ways, too.

My solution works, but it may not be ideal. For example, if you look at the execution plan, you'll see it's running the "real" query twice. That's totally fine for this trivial query, but for other cases that might not work as well.

You might be better off simply running your "real" query to insert into a #Results temp table, then checking how many rows are in the temp table.

IF EXISTS (SELECT 1 FROM #Results)
BEGIN
    INSERT INTO #Results(Id,Name)
    SELECT *
    FROM (VALUES (1,N'One'),(2,N'Two'),(3,N'Three')) AS x(Id,Name);
END;

SELECT *
FROM #Results;
1
  • This is great. I actually need a cartesian product to generate default calendar values in a recursive CTE because the deviceid column value is not unique. This will probably be the subject of another question. Feb 10 at 14:32
8

It is possible to use pivot and unpivot to achieve this efficiently.

Using slightly expanded test data:

DeviceId Time Context Value
1 2022-02-08 00:00:00 Connected False
1 2022-02-10 00:00:00 Connected False
2 2022-02-09 00:00:00 Connected False

To produce rows from the 7th to 11th inclusive:

WITH TheQuery AS
(
    SELECT V.*
    FROM 
    (
        VALUES
            (1, CONVERT(datetime2(0), '20220208', 112), 'Connected', 'False'),
            (1, CONVERT(datetime2(0), '20220210', 112), 'Connected', 'False'),
            (2, CONVERT(datetime2(0), '20220209', 112), 'Connected', 'False')
    ) AS V (DeviceId, [Time], Context, [Value])
)
SELECT
    U.DeviceId, 
    U.TheDate, 
    Context = IIF(U.Present = 1, U.Context, 'Fake'), 
    [Value] = IIF(U.Present = 1, U.[Value], 'False')
FROM TheQuery AS Q
PIVOT (COUNT([Time]) FOR [Time] IN 
    ([20220207],[20220208],[20220209],[20220210],[20220211])) AS P
UNPIVOT (Present FOR TheDate IN 
    ([20220207],[20220208],[20220209],[20220210],[20220211])) AS U;

The pivot part turns the data into:

DeviceId Context Value 20220207 20220208 20220209 20220210 20220211
1 Connected False 0 1 0 1 0
2 Connected False 0 0 1 0 0

The COUNT aggregate results in a zero if the value doesn't exist, or 1 otherwise. The unpivot rotates the set back into rows, with extra ones where the count produced a zero:

DeviceId Context Value Present TheDate
1 Connected False 0 20220207
1 Connected False 1 20220208
1 Connected False 0 20220209
1 Connected False 1 20220210
1 Connected False 0 20220211
2 Connected False 0 20220207
2 Connected False 0 20220208
2 Connected False 1 20220209
2 Connected False 0 20220210
2 Connected False 0 20220211

The 'present' column from the count gives an easy way to decide if default values should be used.

The final results are:

DeviceId TheDate Context Value
1 20220207 Fake False
1 20220208 Connected False
1 20220209 Fake False
1 20220210 Connected False
1 20220211 Fake False
2 20220207 Fake False
2 20220208 Fake False
2 20220209 Connected False
2 20220210 Fake False
2 20220211 Fake False

Execution plan:

plan

The approach lends itself to dynamic SQL because both the pivot and unpivot need a string of quoted comma-separated dates.

This string can be created in many ways, for example:

DECLARE 
    @Start datetime2(0) = '20220207',
    @End datetime2(0) = '20220211';

WITH R AS 
(
    SELECT TheDate = @Start
    UNION ALL
    SELECT DATEADD(DAY, 1, R.TheDate)
    FROM R
    WHERE R.TheDate < @End
)
SELECT 
    STRING_AGG(QUOTENAME(R.TheDate), ',')
FROM R;
7

Cool answers on this one, but for filling in missing date ranges, my preference is just kicking it classic by using a DateDimensions table. Here's an example of how to generate one by Aaron Bertrand.

Of course if you don't need all the additional fluff of dimensions, you can just generate it for the dates alone. Then all you need is a simple outer join to your DateDimensions table by your date field in your other table to fill the gaps of the dates you're missing like so:

SELECT 
    ISNULL(YT.DeviceId, 1) AS DeviceId, 
    DD.[Date] AS [Time], 
    ISNULL(YT.Context, 'Fake') AS Context,
    ISNULL(YT.[Value], 'False') AS [Value]
FROM DateDimensions AS DD
LEFT JOIN YourTable AS YT
    ON DD.[Date] = YT.[Time]
WHERE DD.[Date] >= '2022-02-07'
    AND DD.[Date] <= '2022-02-10'

For really large date ranges and datasets this might not be the #1 way for performance, but I find it the simplest and usually is good enough to get you across the finish line, especially when indexed properly.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.