CODEWITHSUNDEEP
sql-server
Stéphan F
Stéphan F

Reputation: 95

SQL Server Merge two tables and calculate the missing value

I have a table T1 containing dates and a table R2 containing dates and values. I would like to make a query to retrieve the dates and values of R2 and group this with the T1 dates for which I need to find the value from table R2

T1

Start Date
-----------------------
2018-08-09 09:42:00.000
2018-08-09 09:46:00.000
2018-08-09 09:48:00.000

R2

Start Date             | Value
-----------------------|-------
2018-08-09 09:40:00.000|1
2018-08-09 09:43:00.000|2
2018-08-09 09:44:00.000|3

And the Result

Start Date             | Value|Original|
-----------------------|----------------
2018-08-09 09:40:00.000|1     |yes     |
2018-08-09 09:43:00.000|2     |yes     |
2018-08-09 09:44:00.000|3     |yes     |
2018-08-09 09:42:00.000|1     |no      | --> Value found in R2 table (first value before the date)
2018-08-09 09:46:00.000|3     |no      | --> Value found in R2 table (first value before the date)
2018-08-09 09:48:00.000|3     |no      | --> Value found in R2 table (first value before the date)

Thank in advance

Upvotes: 1

Views: 17

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521794

One option, perhaps the not most performant, but should work on any version of SQL Server, it to use a correlated subquery to find the latest start date in R2 which is less than the start date in T1.

WITH cte AS (
    SELECT
        t1.StartDate,
        (SELECT TOP 1 t2.Value FROM R2 t2
         WHERE t2.StartDate < t1.StartDate ORDER BY t2.StartDate DESC) Value,
        'No' AS Original
    FROM T1 t1
)

SELECT StartDate, Value, 'Yes' AS Original FROM R2
UNION ALL
SELECT StartDate, Value, Original FROM cte
ORDER BY Original DESC, StartDate;

Demo

Upvotes: 1

Related Questions