CODEWITHSUNDEEP
t-sqlmissing-data
Preet Sangha
Preet Sangha

Reputation: 65496

How to fill in a missing value from previous rows

Consider this table (test):

C1  C2  Data
----------------
1   101 A1
1   104 A4
1   105 A5
2   101 B1
2   102 B2
2   103 B3
2   105 B5

and this table (OtherTable)

C2
---
100
101
102
103
104
105
106

What I want is for the missing rows above to be filled in from the last available row and from the OtherTable - ie. C1 gets same value, C2 comes from OtherTable and Data gets same value.

C1  C2  Data
----------------
1   101 A1
1   102 A1   -- <<< Filled in from OtherTable & last available row 'A1'
1   103 A1   -- <<< Filled in from OtherTable & last available row 'A1'
1   104 A4
1   105 A5
2   101 B1
2   102 B2
2   103 B3
2   104 B3  -- <<< Filled in from OtherTable &  last available row 'B3'
2   105 B5

I've built this:

WITH Keys AS 
(
    SELECT 
        T.C1, 
        O.C2
    FROM
        TEST T
        CROSS APPLY (SELECT C2 FROM OtherTable) O 
    GROUP BY
        T.C1, 
        O.C2
),
MaxMin AS
(
    SELECT
        C1,
        MIN(C2) LowerBound,
        MAX(C2) UpperBound
    FROM
        Test 
    GROUP BY
        C1
)
SELECT
    K.C1, 
    K.C2,
    T.Data
FROM
    Keys K
    LEFT JOIN Test T 
        ON      
            T.C1 = K.C1 
            AND T.C2 = K.C2 
    INNER JOIN MaxMin M 
        ON
            K.C1 = M.C1 
            AND K.C2 BETWEEN M.LowerBound AND M.UpperBound

I get this far

C1  C2  Data
----------------
1   101 A1
1   102 NULL
1   103 NULL
1   104 A4
1   105 A5
2   101 B1
2   102 B2
2   103 B3
2   104 NULL
2   105 B5

But I can't see how to apply Data column, and I also feel a recursive CTE is in order reduce the above query but I cannot see how.

(Here is TSQL to get the sample up)

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Test') 
    DROP TABLE Test;

CREATE TABLE Test (C1 INT, C2 INT, Data NVARCHAR(10));

INSERT INTO Test (C1, C2, Data) VALUES
(1, 101, 'A1'),
--(1, 102, 'A1'),
--(1, 103, 'A1'),
(1, 104, 'A4'),
(1, 105, 'A5'),
(2, 101, 'B1'),
(2, 102, 'B2'),
(2, 103, 'B3'),
--(2, 104, 'B3'),
(2, 105, 'B5');


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'OtherTable') 
    DROP TABLE OtherTable;

CREATE TABLE OtherTable (C2 INT);

INSERT INTO OtherTable (C2) VALUES
(100),
(101),
(102),
(103),
(104),
(105),
(106);

Upvotes: 2

Views: 7367

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

Find min and max C2 per C1 from test in a CTE (does not have to be a CTE) and join that to OtherTable using between. Fetch the Data value correlated sub query using top(1) ordered by C2 desc

with C as
(
  select C1, 
         min(C2) minC2,
         max(C2) maxC2
  from test
  group by C1
)
select C.C1,
       O.C2,
       (
       select top(1) T.Data
       from test as T
       where T.C1 = C.C1 and
             T.C2 <= O.C2
       order by T.C2 desc
       ) as Data
from C
  inner join OtherTable as O
    on O.C2 between C.minC2 and C.maxC2

SQL Fiddle

Upvotes: 2

Related Questions