SQL - Insert rows on missing value with previous row information

Question

I have queried the tables in a SQL Server database that return a result set with CId (the CId can be in range from 0 to 6) with the quantities.

Result:

aNbr  kNbr  CId  Qty
----  ----  ---  ---
6814   77    0   10
6814   77    2   30
6814   77    3   48
6814   77    4   60
6855   32    2   20
6855   32    6   30
1768   10    0   0
1768   47    0   0

I want to insert the missing CId into result table with previous row CId -> qty, if there is no previous value in group then insert qty as 0

Expected result: I've added | character in-front of the row to indicate the difference between actual and expected.

    aNbr  kNbr  CId  Qty
    ----  ----  ---  ---
    6814   77    0   10
   |6814   77    1   10
    6814   77    2   30
    6814   77    3   48
    6814   77    4   60
   |6814   77    5   60
   |6814   77    6   60
   |6855   32    0   0
   |6855   32    1   0
    6855   32    2   20
   |6855   32    3   20
   |6855   32    4   20
   |6855   32    5   20
    6855   32    6   30
    1768   10    0   0
   |1768   10    1   0
   |1768   10    2   0
   |1768   10    3   0
   |1768   10    4   0
   |1768   10    5   0
   |1768   10    6   0
    1768   47    0   0
   |1768   47    1   0
   |1768   47    2   0
   |1768   47    3   0
   |1768   47    4   0
   |1768   47    5   0
   |1768   47    6   0

I thought joining the same table should work but not sure how to do that or didn't get any relevant information on google.

Can anyone tell me if my approach is correct or possible? (joining the table to itself and looking into previous row value )

Updated result after applying change:

select 
    ak.aNbr, ak.kNbr, v.cid, coalesce(t.qty, 0) as qty
from 
    (select distinct aNbr, kNbr 
     from t) ak  
cross join
    (values (0), (1), (2), (3), (4), (5), (6)) v(cid) 
left join
    t on t.aNbr = ak.aNbr and t.kNbr = ak.kNbr and t.cid = v.cid
order by 
    aNbr, kNbr, CId;

        aNbr  kNbr  CId  Qty
        ----  ----  ---  ---
        6814   77    0   10
       |6814   77    1   0
        6814   77    2   30
        6814   77    3   48
        6814   77    4   60
       |6814   77    5   0
       |6814   77    6   0
       |6855   32    0   0
       |6855   32    1   0
        6855   32    2   20
       |6855   32    3   0
       |6855   32    4   0
       |6855   32    5   0
        6855   32    6   30
        1768   10    0   0
       |1768   10    1   0
       |1768   10    2   0
       |1768   10    3   0
       ....

Gordon Linoff · Accepted Answer · 2019-08-05 21:03:39Z

1

Use a cross join to generate the rows and a left join to bring in the current values:

select ak.aNbr, ak.kNbr, c.cid, coalesce(t.qty, 0) as qty
from (select distinct aNbr, kNbr from t) ak cross join
     (values (0), (1), (2), (3), (4), (5), (6)
     ) v(cid) left join
     t
     on t.aNbr = ak.aNbr and t.kNbr = ak.kNbr and t.cid = v.cid
order by aNbr, kNbr, CId;

EDIT:

I see, you want to repeat the most recent quantity before hand. Such a good place for lag(ignore nulls). Alas, SQL Server doesn't support that. Instead, we can use outer apply:

select ak.aNbr, ak.kNbr, c.cid, coalesce(t.qty, 0) as qty
from (select distinct aNbr, kNbr from t) ak cross join
     (values (0), (1), (2), (3), (4), (5), (6)
     ) v(cid) outer apply
     (select top (1) t.*
      from t
      where t.aNbr = ak.aNbr and t.kNbr = ak.kNbr and t.cid <= v.cid
      order by t.cid desc
     ) t
order by aNbr, kNbr, CId;

If you know the quantities are always increasing then you can do:

select ak.aNbr, ak.kNbr, c.cid,
       max(coalesce(t.qty, 0)) over (partition by ak.aNbr, ak.kNbr order by c.cid) as qty
from (select distinct aNbr, kNbr from t) ak cross join
     (values (0), (1), (2), (3), (4), (5), (6)
     ) v(cid) left join
     t
     on t.aNbr = ak.aNbr and t.kNbr = ak.kNbr and t.cid = v.cid
order by aNbr, kNbr, CId;

This should have a bit better performance than outer apply.

edited Aug 5, 2019 at 21:03

answered Aug 5, 2019 at 20:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raj K Over a year ago

Hi Gordon, thanks for the reply. I'm able to get the table structure with missing values but the added rows qty shows as 0. Which is expected as coalesce(t.qty, 0) return 0 on no value found. Is it possible to check if I can add a previous quantity here if exist or 0 if doesn't exist?

Collectives™ on Stack Overflow

SQL - Insert rows on missing value with previous row information

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related