Hello,
I have the following code for displaying stock results which displays the result of the latest stock mutation of that month. This looks like this:
[MonthlyStockTEMP]:
LOAD
[TempMonth] as [Month],
[KeyPart],
FirstSortedValue([PartStockEnd], -[Date]) as [Voorraad]
Resident [FinalStock]
Group By [TempMonth], [KeyPart];
[MonthlyStockFinal]:
NOCONCATENATE LOAD
[KeyPart],
[Month],
[Voorraad]
Resident [MonthlyStockTEMP];
DROP TABLE [MonthlyStockTEMP];
This results in the following table:
| KeyPart | Month | Voorraad |
| ExamplePart1 | 01-02-2013 | 21 |
| ExamplePart1 | 01-03-2013 | 14 |
| ExamplePart1 | 01-04-2013 | 12 |
| ExamplePart1 | 01-05-2013 | 77 |
| ExamplePart1 | 01-07-2013 | 70 |
| ExamplePart2 | 01-02-2013 | 33 |
| ExamplePart2 | 01-03-2013 | 27 |
| ExamplePart2 | 01-04-2013 | 25 |
| ExamplePart2 | 01-07-2013 | 20 |
Cause some of the months are empty, I want to add them manually. I have the following table with months. (first days, just like in the stock table)
[AllMonths]:
| 01-01-2013 |
| 01-02-2013 |
| 01-03-2013 |
| 01-04-2013 |
| 01-05-2013 |
| 01-06-2013 |
| 01-07-2013 |
| 01-08-2013 |
I want to merge those 2 tables so the keyparts with no value in that specific month at least have an empty month.
How can i do this for each keypart? So it will look like this:
| KeyPart | Month | Voorraad |
| ExamplePart1 | 01-01-2013 | - |
| ExamplePart1 | 01-02-2013 | 21 |
| ExamplePart1 | 01-03-2013 | 14 |
| ExamplePart1 | 01-04-2013 | 12 |
| ExamplePart1 | 01-05-2013 | 77 |
| ExamplePart1 | 01-06-2013 | - |
| ExamplePart1 | 01-07-2013 | 70 |
| ExamplePart2 | 01-01-2013 | - |
| ExamplePart2 | 01-02-2013 | 33 |
| ExamplePart2 | 01-03-2013 | 27 |
| ExamplePart2 | 01-04-2013 | 25 |
| ExamplePart2 | 01-05-2013 | - |
| ExamplePart2 | 01-06-2013 | - |
| ExamplePart2 | 01-07-2013 | 20 |
