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

Question

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

what is the rule for completion? You don't have `Jet` for 2023-01-06 — Horaciux, Jan 11 '23 at 19:20
Your sample data is insufficient and description incomplete. What happens for the rows that do not join? Where does your `method` come from? Are there other values for method? Is it *always* 'Jet'? — Stu, Jan 11 '23 at 19:25
@Horaciux There are many other values that don't have the values. Ultimately what I want is, where is the data not available between two dates in second table then just extend the date and fill the `value` column from first table and `Method` column with value present in previous Date. Thanks — Robin, Jan 11 '23 at 19:34
@Stu, This data is for one `Node` only. Different nodes have different value of Method but value for `Method` for a particular `Node` remains same. — Robin, Jan 11 '23 at 19:36

score 0 · Answer 1 · answered Jan 11 '23 at 20:33

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

No, this is not a dynamic solution. What if, I have 10 days gap then the query will be bigger and bigger. — Robin, Jan 15 '23 at 18:49

EdmCoff · Answer 2 · 2023-01-16T16:25:33.947

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.

For an older version of SQL Server that cannot do last_value with IGNORE NULLS (as per request in comments), maybe you could do something like the following. I'm confident this works in SQL Server 2016, but I'm not sure if there are nicer ways of doing it.

SELECT node, date, value, method
FROM
(
 SELECT a.*, b.method, row_number() OVER (partition by a.node, a.date ORDER BY CASE WHEN method IS NULL THEN 1 ELSE 0 END, datediff(day, a.date, b.timestamp)) rn
 FROM a LEFT OUTER JOIN b
  ON a.node = b.node AND a.date <= b.timestamp
) sq
WHERE rn = 1
ORDER BY node, date desc

The idea here is to do the join with a <= (which will include the match we want as well as others) then use row_number to prioritize the best match (we sort the non-null values first, then datediff(day, a.date, b.timestamp) to get the closest timestamp).

This only works (IGNORE NULLS) with SQL Server 2022 (16.x). Can you provide some alternative solution with same approach. — Robin, Jan 15 '23 at 18:52
@Robin I've added a different method that will work in older versions and that I think will meet your needs. — EdmCoff, Jan 17 '23 at 15:18

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

2 Answers2

TEST