SQL: MTD Calculation on specific dates

user_13567788 0
Viewed 69

I am trying to Calculate MTD sales on daily sales number but my month starts from 26th of previous month to 25th of next month.

data contains only 3 columns (date, vendor_id, total_sales). Below Code is working fine for calculating months starting from the 1st. I tried to do this by using the below approaches but it does not works

  1. date(date - interval '25 day') : Not working
  2. Mapping table creation for each day, but will not work for 30/31 days month

Need suggestion on above.

SUM(sales) OVER (
            PARTITION BY 
                vendor_id, 
                EXTRACT(YEAR FROM date), 
                EXTRACT(MONTH FROM date) 
            ORDER BY 
                date ROWS UNBOUNDED PRECEDING
        ) AS mtd_total_sales,

enter image description here

amazon-redshift sql
1 Answers

So, if the date is >= 26, then "it is the next month".

As a result, something like

CASE
    WHEN EXTRACT(DAY FROM date) >= 26 THEN ADD_MONTHS(date, 1)
    ELSE date
END

should suffice, since you extract the year and month anyway.

user_4365600
Related