-1

Can anyone help with calculating sales figures for month-until-date rolling sum on a data table carrying Datetime column and Sales figures column?

Using OVER in standard sql can help me calculate rows/dates preceding the current row, but I am having trouble with starting from day one of a month.

Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
AliveToLearn
  • 41
  • 8
  • It's always good to add an example for both: input and expected result table. Could you add these? – Martin Weitzmann Aug 08 '18 at 08:06
  • Thanks for your comment. I cannot put the exact tables here. But imagine just a simple two-column table--one with sales transaction timestamps and other with sales figures. Thanks a lot! – AliveToLearn Aug 08 '18 at 09:04

1 Answers1

1

If you create columns for day, month, year (see:date extract function), you can use the month and year in the "PARTITION BY" part of your OVER function and the day in the "ORDER BY" part.

UPDATE

AliveToLearn worked it out: AVG(events_US) OVER (Partition by event_month, event_year ORDER BY day) AS moving_avg_month

Rubén C.
  • 1,098
  • 6
  • 16
Bobbylank
  • 1,906
  • 7
  • 15
  • Bobby thanks a lot! My primary rolling query for 28 days cumulative has this: AVG(events_US) OVER (ORDER BY day RANGE BETWEEN 27 PRECEDING AND CURRENT ROW) AS mov_avg_28d //// Would you please elaborate the changed OVER clause? Thanks a lot – AliveToLearn Aug 08 '18 at 07:46
  • Alright, I worked it out: AVG(events_US) OVER (Partition by event_month, event_year ORDER BY day) AS moving_avg_month //thanks a lot for your help!! – AliveToLearn Aug 08 '18 at 08:06