Conceptualizing SQL Query for data that isn't there

Question

SQL Server 2017. Having been running simple-to-intermediate SQL queries for many years, I'm having trouble wrapping my head around this one, as it's querying for information that doesn't actually exist.

Given a table called Activity with ProductId (int), PurchaseDate (datetime)

and some rows that look like this:

1   2020-10-31
1   2020-11-01
1   2020-11-02
1   2020-11-03
2   2020-10-31
2   2020-11-01
2   2020-11-03
2   2020-11-04
3   2020-10-31
3   2020-11-01
4   2020-10-31
4   2020-11-01
4   2020-11-03
5   2020-10-20
6   2020-10-31
6   2020-11-01
6   2020-11-02

And then another table called ProductIds with column ProductId (int) and 7 rows, with values 1-7:

I need to return from the Activity table any ProductIds that do not have an entry for a date from a date range, as well as the date that doesn't have the entry. This would be the results:

2   2020-11-02
3   2020-11-02
3   2020-11-03
4   2020-11-02
5   2020-10-31
5   2020-11-01
5   2020-11-02
5   2020-11-03
6   2020-11-03
7   2020-10-31
7   2020-11-01
7   2020-11-02
7   2020-11-03

So the query would be looking for any ProductId from the ProductIds table that does not have an associated entry in the Activity table for dates between 2020-10-31 and 2020-11-03.

This is what I have so far, but bangin' my head trying to figure it out:

SELECT ProductId, PurchaseDate
FROM dbo.Activity
WHERE ProductId NOT IN (SELECT ProductId FROM dbo.ProductIds);

I know there are at least a couple things wrong with that query and I just can't figure out how to go about this. As you can see, the results set is returning information that doesn't exist in the table, hence my confusion.

user_10676716 · Answer

You can generate the date series with a recursive query, then cross join that with the list of products to generate all possible combinations. Finally, you can use not exists to filter on tuples that do not exists in the activity table.

with dates as (
    select convert(date, '20201031') purchasedate
    union all select dateadd(day, 1, purchasedate) from dates where purchasedate < '20201103'
)
select p.productid, d.purchasedate
from productids p
cross join dates d
where not exists (
    select 1
    from activity a 
    where a.productid = p.productid and a.purchasedate = d.purchasedate
)

If you have a date range that spans over more than 100 days, you need to add option (maxrecusion 0) at the very end of the query.

user_1080354 · Answer

Try this:

DECLARE @Activity TABLE
(
    [ProductId] INT
   ,[PurchaseDate] DATE
);

DECLARE @ProductIds TABLE
(
    [ProductId] INT
);

INSERT INTO @Activity ([ProductId], [PurchaseDate])
VALUES (1, '2020-10-31')
      ,(1, '2020-11-01')
      ,(1, '2020-11-02')
      ,(1, '2020-11-03')
      ,(2, '2020-10-31')
      ,(2, '2020-11-01')
      ,(2, '2020-11-03')
      ,(2, '2020-11-04')
      ,(3, '2020-10-31')
      ,(3, '2020-11-01')
      ,(4, '2020-10-31')
      ,(4, '2020-11-01')
      ,(4, '2020-11-03')
      ,(5, '2020-10-20')
      ,(6, '2020-10-31')
      ,(6, '2020-11-01')
      ,(6, '2020-11-02');

INSERT INTO @ProductIds ([ProductId])
VALUES (1), (2), (3), (4), (5), (6), (7);

DECLARE @date_beg DATE = '2020-10-31'
       ,@date_end DATE = '2020-11-03';


SELECT P.[ProductId]
      ,Dates.[Date]
FROM @ProductIds P
CROSS APPLY
(
    SELECT  DATEADD(DAY, nbr - 1, @date_beg)
    FROM    
    ( 
        SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
        FROM sys.columns c
    ) nbrs
    WHERE  nbr - 1 <= DATEDIFF(DAY, @date_beg, @date_end)
) Dates ([Date])
LEFT JOIN @Activity A
    ON P.[ProductId] = A.[ProductId]
    AND Dates.[Date] = A.[PurchaseDate]
WHERE A.[ProductId] IS NULL
ORDER BY P.[ProductId]
        ,Dates.[Date];

user_2497335 · Answer

Relational databases have a basis in set theory, so naturally they have set operators built for all sets operations. We're just used to using operators that result in intersections that we overlook other possibilities.

If you don't have a date table, you can generate a range of dates with a recursive query. Then create a set that's a cross product of ProductId and Date, and exclude and members that are in Activity using Except.

DECLARE @MinDate DATE = '2020-10-31';
DECLARE @MaxDate DATE = '2020-11-03';

WITH Dates AS (
    SELECT @MinDate [PurchaseDate]
    UNION ALL
    SELECT DATEADD(DAY, 1, PurchaseDate)
    FROM Dates
    WHERE PurchaseDate < @MaxDate
)
SELECT p.ProductId, d.PurchaseDate FROM ProductIds p CROSS JOIN Dates d
EXCEPT SELECT ProductId, PurchaseDate FROM Activity

Conceptualizing SQL Query for data that isn't there

3 Answers