How to extend data with respect to incomplete dates in T-SQL?-CodePudding

I have the first table like below:

Node    Date        Value
01R-123 2023-01-10  09
01R-123 2023-01-09  11
01R-123 2023-01-08  18
01R-123 2023-01-07  87
01R-123 2023-01-06  32
01R-123 2023-01-05  22
01R-123 2023-01-04  16
01R-123 2023-01-03  24
01R-123 2023-01-02  24
01R-123 2023-01-01  24

And second table like this :

Node    Timestamp    Method
01R-123 2023-01-10   Jet
01R-123 2023-01-09   Jet
01R-123 2023-01-08   Jet
01R-123 2023-01-05   Jet
01R-123 2023-01-04   Jet
01R-123 2023-01-03   Jet
01R-123 2022-12-30   Jet
01R-123 2022-12-29   Jet
01R-123 2022-12-28   Jet
01R-123 2022-12-25   Jet

These two tables are joined according below detail:

Based on two conditions-

First: a.[Node] = b.[Node]

Second: a.[Date] = b.[Timestamp]

Now the question is:

In the first table, date is continuous but not in the second table AND when both tables are joined using above condition the dates and correspondent values available in the second table are shown. But I need to get the date in a continuous manner.

After all, I need data like below table:

Node    Date        Value   Method
01R-123 2023-01-10  09      Jet
01R-123 2023-01-09  11      Jet
01R-123 2023-01-08  18      Jet
01R-123 2023-01-07  87      Jet
01R-123 2023-01-06  32      Jet
01R-123 2023-01-05  22      Jet
01R-123 2023-01-04  16      Jet
01R-123 2023-01-03  24      Jet
01R-123 2023-01-02  24      Jet
01R-123 2023-01-01  24      Jet

Again, joining condition for both table is also DATE

CodePudding user response：

This will work up to 3 days gap, you can easily extend it.



CREATE TABLE Table1
    ([Node] varchar(7), [Date] datetime, [Value] int)
;
    
INSERT INTO Table1
    ([Node], [Date], [Value])
VALUES
    ('01R-123', '2023-01-10 00:00:00', 09),
    ('01R-123', '2023-01-09 00:00:00', 11),
    ('01R-123', '2023-01-08 00:00:00', 18),
    ('01R-123', '2023-01-07 00:00:00', 87),
    ('01R-123', '2023-01-06 00:00:00', 32),
    ('01R-123', '2023-01-05 00:00:00', 22),
    ('01R-123', '2023-01-04 00:00:00', 16),
    ('01R-123', '2023-01-03 00:00:00', 24),
    ('01R-123', '2023-01-02 00:00:00', 24),
    ('01R-123', '2023-01-01 00:00:00', 24)
;


CREATE TABLE Table2
    ([Node] varchar(7), [Timestamp] datetime, [Method] varchar(3))
;
    
INSERT INTO Table2
    ([Node], [Timestamp], [Method])
VALUES
    ('01R-123', '2023-01-10 00:00:00', 'Jet'),
    ('01R-123', '2023-01-09 00:00:00', 'Jet'),
    ('01R-123', '2023-01-08 00:00:00', 'Jet'),
    ('01R-123', '2023-01-05 00:00:00', 'Jet'),
    ('01R-123', '2023-01-04 00:00:00', 'Jet'),
    ('01R-123', '2023-01-03 00:00:00', 'Jet'),
    ('01R-123', '2022-12-30 00:00:00', 'Jet'),
    ('01R-123', '2022-12-29 00:00:00', 'Jet'),
    ('01R-123', '2022-12-28 00:00:00', 'Jet'),
    ('01R-123', '2022-12-25 00:00:00', 'Jet')
;


select a.*, coalesce(b.method,c.method,d.method,e.method) Method
from table1 a
left join table2 b on
a.[Node] = b.[Node]
and a.[Date] = b.[Timestamp]
left join table2 c on
a.[Node] = c.[Node]
and a.[Date] = dateadd(d,1,c.[Timestamp])
left join table2 d on
a.[Node] = d.[Node]
and a.[Date] = dateadd(d,2,d.[Timestamp])
left join table2 e on
a.[Node] = e.[Node]
and a.[Date] = dateadd(d,3,e.[Timestamp])
order by a.[Date]

TEST

http://sqlfiddle.com/#!18/dd9a3/14

CodePudding user response：

I suggest using last_value with the IGNORE NULLS option. Something like:

SELECT a.*, LAST_VALUE(b.method) IGNORE NULLS OVER (PARTITION BY a.node ORDER BY a.date DESC) method
FROM a LEFT OUTER JOIN b
  ON a.node = b.node AND a.date = b.timestamp
ORDER BY a.node, a.date DESC

You can see a Fiddle of it here.

This looks for the previous non-null value of b.method within this a.node grouping (because of the partition by a.node) ordered by a.date descending. Since this looks at all previous values including the current row (default behavior if no rows/range is specified in the OVER clause), there's no need to have a special case (e.g. CASE or COALESCE) to handle the non-null scenario.