3 Replies Latest reply: Jul 8, 2013 4:20 AM by test test RSS

    Join a table for each GROUP BY

      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:

      KeyPartMonthVoorraad
      ExamplePart101-02-201321
      ExamplePart101-03-201314
      ExamplePart101-04-201312
      ExamplePart101-05-201377
      ExamplePart101-07-201370
      ExamplePart201-02-201333
      ExamplePart201-03-201327
      ExamplePart201-04-201325
      ExamplePart201-07-201320

       

      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:

      KeyPartMonthVoorraad
      ExamplePart101-01-2013-
      ExamplePart101-02-201321
      ExamplePart101-03-201314
      ExamplePart101-04-201312
      ExamplePart101-05-201377
      ExamplePart101-06-2013-
      ExamplePart101-07-201370
      ExamplePart201-01-2013-
      ExamplePart201-02-201333
      ExamplePart201-03-201327
      ExamplePart201-04-201325
      ExamplePart201-05-2013-
      ExamplePart201-06-2013-
      ExamplePart201-07-201320