CODEWITHSUNDEEP
sql-server
William 3N
William 3N

Reputation: 85

UNION Returns NULL When First SELECT Returns Nothing

Hi I have a table: T1 that contains two columns Date and Price

T1

---------------------------
DATE           | PRICE    |
---------------------------
2018-07-25     |2.00      |
---------------------------
2018-06-20     |3.00      |
---------------------------
2017-05-10     |3.00      |
---------------------------

Here are my requirements:

  1. If a user enters a date that is not in the DB I need to return the last price and date in the table T1.
  2. If a user enters a date that is superior or inferior to one of the dates in the table T1 -- for example if a user enters '2017-05-09' which is not in the table; I have to return the next date above the given date. In this case'2017-05-10'

I am using UNION in my script but it returns empty when one of the SELECT statements returns empty.

I am using a CTE table:

DECLARE @DateEntered DATE

WITH HistoricalCTE (Date, Price, RowNumber) AS (
    SELECT R.Date,
           R.Price,
           ROW_NUMBER() OVER (PARTITION BY R.Date, R.Price ORDER BY Date DESC)
    FROM T1 R
    WHERE Date = @DateEntered
    UNION
    SELECT R.Date,
           R.Price,
           ROW_NUMBER() OVER (PARTITION BY R.Date, R.Price ORDER BY Date DESC)                             
    FROM T1 R
    WHERE Date < @DateEntered
    UNION
    SELECT R.Date,
           R.Price,
           ROW_NUMBER() OVER (PARTITION BY R.Date, R.Price ORDER BY Date DESC)
    FROM T1 R
    WHERE Date > @DateEntered
)

The issue is when I enter superior to all the dates in the table T1, I get an empty result because the first select is returning empty. Any idea about how I would solve this?

Upvotes: 1

Views: 763

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520988

You might be overcomplicating this. If I read your question correctly, we can just take the smallest value greater than the input, or if that doesn't exist, then just take the max of the table.

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY Date) rn
    FROM T1
    WHERE Date > @DateEntered
)

SELECT
    CASE WHEN EXISTS (SELECT 1 FROM cte WHERE rn = 1)
         THEN (SELECT Date FROM cte WHERE rn = 1)
         ELSE (SELECT MAX(Date) FROM T1) END AS Date,
    CASE WHEN EXISTS (SELECT 1 FROM cte WHERE rn = 1)
         THEN (SELECT Price FROM cte WHERE rn = 1)
         ELSE (SELECT Price FROM T1 WHERE Date = (SELECT MAX(Date) FROM T1)) END AS Price;

Demo

All the edge cases seem to be working in the above demo, and you may test any input date against your sample data.

Upvotes: 1

Related Questions