patternsqlMinor

Get data that was present yesterday but is not present today

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

yesterdaypresenttodaybutgetthatwasdatanot

Problem

I have a table like this:

Id
Name
Value
Date

1
John
1
12/01/2022

2
Jane
2
12/01/2022

3
John
3
12/02/2022

4
Max
4
11/30/2022

Assuming that today is 12/02/2022, I want only Jane in the result because this row was present yesterday and is not today. John was present yesterday and is today, Max is not present today but also wasn't yesterday.

What would be the best way to write query to get those records? I know that probably a self-join would work but I would also like to consider performance here.

Solution

If someone was present yesterday but not today, the most recent record will be for yesterday.
Sample table and data

DECLARE @T table
(
    Id integer UNIQUE NONCLUSTERED,
    [Name] nvarchar(50) NOT NULL,
    [Value] integer NOT NULL,
    [Date] date NOT NULL,

    PRIMARY KEY CLUSTERED ([Name], [Date])
);

INSERT @T
    (Id, [Name], [Value], [Date])
VALUES
    (1, 'John', 1, '12/01/2022'),
    (2, 'Jane', 2, '12/01/2022'),
    (3, 'John', 3, '12/02/2022'),
    (4, 'Max',  4, '11/30/2022');

Returning qualifying names

DECLARE @Today date = CONVERT(date, '20221202', 112);

-- Just the name
SELECT
    T.[Name]
FROM @T AS T
WHERE
    T.[Date] <= @Today
GROUP BY 
    T.[Name]
HAVING
    MAX(T.[Date]) = DATEADD(DAY, -1, @Today);

Name

Jane

Returning whole qualifying rows

-- All attributes
SELECT
    Q1.Id, 
    Q1.[Name], 
    Q1.[Value], 
    Q1.[Date]
FROM 
(
    SELECT
        T.Id, 
        T.[Name], 
        T.[Value], 
        T.[Date],
        RowNum = 
            ROW_NUMBER() OVER (
                PARTITION BY T.[Name]
                ORDER BY T.[Date] DESC)
    FROM @T AS T
    WHERE
        T.[Date] <= @Today
) AS Q1
WHERE
    Q1.RowNum = 1
    AND Q1.[Date] = DATEADD(DAY, -1, @Today)
ORDER BY
    Q1.[Name] DESC,
    Q1.[Date] DESC;

Id
Name
Value
Date

2
Jane
2
2022-12-01

db<>fiddle demo

I wonder if it would be possible to expand it for 2 scenarios:

only those that are present today and yesterday

those not present today but were yesterday and 2 days ago

-- Present today and yesterday
SELECT
    T.[Name]
FROM @T AS T
WHERE
    -- Range of dates to consider
    T.[Date] >= DATEADD(DAY, -1, @Today)
    AND T.[Date] = DATEADD(DAY, -2, @Today)
    AND T.[Date] <= @Today
GROUP BY 
    T.[Name]
HAVING
    -- Most recently present yesterday
    MAX(T.[Date]) = DATEADD(DAY, -1, @Today)
    -- Two days in the range
    AND COUNT_BIG(T.[Date]) = 2;

Code Snippets

DECLARE @T table
(
    Id integer UNIQUE NONCLUSTERED,
    [Name] nvarchar(50) NOT NULL,
    [Value] integer NOT NULL,
    [Date] date NOT NULL,

    PRIMARY KEY CLUSTERED ([Name], [Date])
);

INSERT @T
    (Id, [Name], [Value], [Date])
VALUES
    (1, 'John', 1, '12/01/2022'),
    (2, 'Jane', 2, '12/01/2022'),
    (3, 'John', 3, '12/02/2022'),
    (4, 'Max',  4, '11/30/2022');

DECLARE @Today date = CONVERT(date, '20221202', 112);

-- Just the name
SELECT
    T.[Name]
FROM @T AS T
WHERE
    T.[Date] <= @Today
GROUP BY 
    T.[Name]
HAVING
    MAX(T.[Date]) = DATEADD(DAY, -1, @Today);

-- All attributes
SELECT
    Q1.Id, 
    Q1.[Name], 
    Q1.[Value], 
    Q1.[Date]
FROM 
(
    SELECT
        T.Id, 
        T.[Name], 
        T.[Value], 
        T.[Date],
        RowNum = 
            ROW_NUMBER() OVER (
                PARTITION BY T.[Name]
                ORDER BY T.[Date] DESC)
    FROM @T AS T
    WHERE
        T.[Date] <= @Today
) AS Q1
WHERE
    Q1.RowNum = 1
    AND Q1.[Date] = DATEADD(DAY, -1, @Today)
ORDER BY
    Q1.[Name] DESC,
    Q1.[Date] DESC;

-- Present today and yesterday
SELECT
    T.[Name]
FROM @T AS T
WHERE
    -- Range of dates to consider
    T.[Date] >= DATEADD(DAY, -1, @Today)
    AND T.[Date] <= @Today
GROUP BY 
    T.[Name]
HAVING
    -- Both days exist
    COUNT_BIG(T.[Date]) = 2;

-- Not present today; present both days prior
SELECT
    T.[Name]
FROM @T AS T
WHERE
    -- Range of dates to consider
    T.[Date] >= DATEADD(DAY, -2, @Today)
    AND T.[Date] <= @Today
GROUP BY 
    T.[Name]
HAVING
    -- Most recently present yesterday
    MAX(T.[Date]) = DATEADD(DAY, -1, @Today)
    -- Two days in the range
    AND COUNT_BIG(T.[Date]) = 2;

Context

StackExchange Database Administrators Q#320909, answer score: 3

Revisions (0)

No revisions yet.