Filter by field, and if zero results then filter by other field



  • How should a WHERE clause be constructed, such that:

    • It filters by a certain field
    • If there are zero results for filtering by the aforementioned field, then it filters by another field?

    For example, if we have this schema:

    CREATE TABLE Items
    (
      [Id] INT PRIMARY KEY,
      [FirstDate] DATETIME,
      [SecondDate] DATETIME
    );
    

    ... with these records:

    INSERT INTO Items ([Id], [FirstDate], [SecondDate])
    VALUES 
    (1, '2021-1-1', '2022-1-1'),
    (2, '2022-1-1', '2023-1-1'), 
    (3, '2022-1-1', '2024-1-1'), 
    (4, '2024-1-1', '2025-1-1'),
    (5, '2024-1-1', '2026-6-1')
    

    ... then I want to filter by FirstDate and SecondDate, such that:

    • If we specify FirstDate and SecondDate to filter for 2022-1-1, records 2 & 3 will be returned (but not 1!).
    • If we specify FirstDate and SecondDate to filter for 2024-1-1, records 4 & 5 will be returned (but not 3!).
    • If we specify FirstDate and SecondDate to filter for 2025-1-1, record 4 will be returned.


  • You can essentially achieve your goal, with a single query, by leveraging a https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15 and a https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15 like so:

    DECLARE @SomeDate DATETIME = '2026-06-01';
    

    WITH _Items AS
    (
    SELECT
    Id,
    FirstDate,
    SecondDate,
    MAX(CASE WHEN FirstDate = @SomeDate THEN 1 ELSE 0) OVER (ORDER BY Id) AS IsMatchOnFirstDate
    FROM Items
    )

    SELECT
    Id,
    FirstDate,
    SecondDate
    FROM _Items
    WHERE (IsMatchOnFirstDate = 1 AND FirstDate = @SomeDate)
    OR (IsMatchOnFirstDate = 0 AND SecondDate = @SomeDate);

    The way this basically works is the IsMatchOnFirstDate field is calculated by checking every row of the Items table for if the FirstDate field matches on the @SomeDate variable, and returning the value of 1 for all rows (hence window function) when there is a match on any of the rows.

    The final select from the CTE then filters on the FirstDate only when IsMatchOnFirstDate = 1 (meaning only when at least one of the rows from the Items table had a match on FirstDate = @SomeDate) otherwise it filters on SecondDate (since none of the rows matched by FirstDate when calculating IsMatchOnFirstDate).




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2