Add default rows to the query result if restrictions are not met

Question

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?

Answer 1

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);

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';

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;

Answer 2

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 7^th to 11^th 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:

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;

Answer 3

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.