SQL – Insert rows on missing value with previous row information

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
       ....

Answer

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.