Aggregating daily data by month in load script

[Anonymous]

I have Orders table which has daily orders. I would like to create a summary table which aggregates the data by month. Storing daily transactions is making my database huge and takes long time to process. I don't need to store the values by day. 

Tried the below code, but its not working.

OrdersSummaryTbl:

Load  Transaction_Date,

   MonthStart(Transaction_Date) as MonthStart,

    Country,

   Product_Name,

   Sum(Order_Count) as Orders,

   Sum(Order_Amount) as OrderAmt

Resident Orders

Group by  Transaction_Date,

      Country,

   Product_Name

   MonthStart;

I just need to aggregate the data by month and store it.  Please can you share your thoughts.

[aarkay29]

OrdersSummaryTbl:

Load

     Country,

     Product_Name,

     Month

     Sum(Order_Count) as Orders,

     Sum(Order_Amount) as OrderAmt

Group by

     Country,

     Product_Name,

     Month;

Load 

     Transaction_Date,

    Month(Transaction_Date) as Month,

    Country,

    Product_Name,

    Order_Count

    Order_Amount

Resident

     Orders;

[maxgro · Accepted solution]

try this

OrdersSummaryTbl:

Load 

   //Transaction_Date,

   MonthStart(Transaction_Date) as MonthStart,

   Country,

  Product_Name,

  Sum(Order_Count) as Orders,

  Sum(Order_Amount) as OrderAmt

Resident

     Orders

Group by 

    MonthStart(Transaction_Date),

    Country,

    Product_Name

    //MonthStart

;

[piotrtk]

Hi,

Try this:

//-------------------

OrdersSummaryTemp:

Load

   Year(Transaction_Date) & '-' & Month(Transaction_Date) as KeyDate,

   Country,

   Product_Name,

   Order_Count,

   Order_Amount

Resident Orders;

OrdersSummaryTbl:

     KeyDate

      Country,

      Product_Name,

      Sum(Order_Count) as Orders,

      Sum(Order_Amount) as OrderAmt,

      Resident OrdersSummaryTemp

      Group by KeyDate, Country, Product_Name;

   DROP TABLE OrdersSummaryTemp;

//--------------------------

Regards,

Piotr

[Anonymous]

Thank you all. It worked.