vendredi 31 juillet 2015

Fill in missing values in a table in SQL

This is how my table looks like..i want to fill in the missing week numbers in 2015 and 2016,say 1-52 weeks in 2015 and again 1-52 for 2016 for the same product and in that particular location.

row product loc     year    week    quantity
1   A       X      2015      2        30
2   A       X      2015      4        34
3   A       X      2015      5        45
4   A       X      2015      6        56
5   A       X      2015      7        324
6   A       X      2015      8        56
7   A       X      2015      9        67
8   A       X      2016      1        34
9   A       X      2016      2        76
10  A       X      2016      3        45

output would be like

row product loc     year    week    quantity
1   A       X      2015      1        0
2   A       X      2015      2        30
3   A       X      2015      3        0
4   A       X      2015      4        34
5   A       X      2015      5        45
6   A       X      2015      6        56
7   A       X      2015      7        324
8   A       X      2015      8        56
52  A       X      2015      52        0
53  A       X      2016      1        34
54  A       X      2016      2        76
55  A       X      2016      3        45
106 A       X      2016      53       0 ...

Updating the table with values did not work and also tried cross apply..

Aucun commentaire:

Enregistrer un commentaire