3

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.

4 Answers 4

3

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:

  1. only those that are present today and yesterday
  2. 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] <= @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;
2
  • ok thanks, looks nice, I wonder if it would be possible to expand it for 2 scenraios: first- give me only those that are present today and yesterday, second- give me those that are not present today but were yesterday and 2 days ago :) Dec 13, 2022 at 18:27
  • 1
    @witkacy1986 Added one way to achieve those to the end of my answer. That said, the goal here is for you to understand the solutions and be able to extend them as needed yourself.
    – Paul White
    Dec 14, 2022 at 7:14
2

When you only want the names you can use EXCEPT

DECLARE @MeasureDate DATE = '2022-12-01';

SELECT Name
FROM   dbo.NotProvidedTableName AS nptn
WHERE  nptn.Date = @MeasureDate                 /*exists on measure date, which should be yesterday*/
EXCEPT
SELECT Name
FROM   dbo.NotProvidedTableName AS nptn
WHERE  nptn.Date = DATEADD(DAY,1,@MeasureDate); /*exists on measure date + 1, which should be today*/

If you want all data, you can use the same information, but need to join to the table:

DECLARE @MeasureDate DATE = '2022-12-01';

WITH CTE AS (
SELECT Name
FROM   dbo.NotProvidedTableName AS nptn
WHERE  nptn.Date = @MeasureDate                 /*exists on measure date, which should be yesterday*/
EXCEPT
SELECT Name
FROM   dbo.NotProvidedTableName AS nptn
WHERE  nptn.Date = DATEADD(DAY,1,@MeasureDate)  /*exists on measure date + 1, which should be today*/
)
SELECT * FROM CTE AS c
JOIN dbo.NotProvidedTableName AS nptn ON nptn.Name = c.Name
WHERE nptn.Date = @MeasureDate

Or use just a join on it:

DECLARE @MeasureDate DATE = '2022-12-01';

SELECT nptnYesterDay.*
    FROM   dbo.NotProvidedTableName AS nptnYesterDay
           LEFT JOIN dbo.NotProvidedTableName AS nptn ON nptnYesterDay.Name = nptn.Name AND nptn.Date = DATEADD(DAY,1,@MeasureDate)
    WHERE  nptnYesterDay.Date = @MeasureDate
           AND nptn.Id IS NULL;

Performance will depend on your indexes which aren't given.

0
-2

Just a WHERE clause with both days in it. Use an AND. Look for where it equals today minus one, but doesn't equal today. Really, that's about it.

2
  • 1
    I think that if I would only use AND then it would give me all rows ehre date equals todays minus one. Dec 13, 2022 at 14:49
  • As I said, "but doesn't equal today. For the minus one =, for the other, either IS NULL or <>, <, depends on what's there. Dec 14, 2022 at 13:25
-2

You can use a self-join and a NOT EXISTS condition to get the rows you want. The query would look something like this:

    SELECT t1.*
FROM table AS t1
JOIN table AS t2
ON t1.Name = t2.Name AND t1.Date = t2.Date - INTERVAL 1 DAY
WHERE t1.Date = CURRENT_DATE - INTERVAL 1 DAY
AND NOT EXISTS (
    SELECT *
    FROM table
    WHERE Name = t1.Name
    AND Date = CURRENT_DATE
)

This query will join the table to itself, using the Name column to match rows and the Date column to ensure that the joined rows have dates that are one day apart. Then it filters the results to only include rows where the Date is yesterday and where there is no corresponding row with the same Name and today's date.

If performance is a concern, you can try adding an index on the Name and Date columns to improve the query's performance. However, without knowing more about the size and structure of your table and the specifics of your database system, it's difficult to say for sure whether this will help or how much of a difference it will make.

1
  • The question is tagged for Microsoft SQL Server, where the syntax you use here is not available.
    – Paul White
    Dec 14, 2022 at 7:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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