Tsql script to fill holes on a time line

Question

Hi there Suppose I have this query result on a temp table:

Person | Week | Month | Kms

John   | 1    |  1    |  1
Mary   | 1    |  1    |  2
Bob    | 2    |  1    |  3

And I want to show 0 when there's no values, that is, filling the holes with 0 when there's no data for a person in a week ex:

 Person | Week | Month | Kms

    John   | 1    |  1    |  1
    Mary   | 1    |  1    |  2
  **Bob    | 1    |  1    |  0**
  **John   | 1    |  1    |  0**
  **Mary   | 1    |  1    |  0**
    Bob    | 2    |  1    |  3

(being ** the lines I want to add indicating 0 kms for a week)

any idea?

tks

score 1 · Accepted Answer · edited May 23 '17 at 09:58

1

Cross-join with a numbers table or left join from a numbers table.

See this example regarding date sequences.

edited May 23 '17 at 09:58

Community

1
1

answered Apr 06 '11 at 12:44

Cade Roux

88,164
40
182
265

Tsql script to fill holes on a time line

1 Answers1