Group By week and week starts from Tuesday

Question

Here is my data table:

invoice_id  invoice_line    store_id    time_stamp  product units   sales   cogs
10000001    31215           3           9/3/17      3000    1       99.99   58.00
10000001    31216           3           9/3/17      3354H   3       32.99   18.00
10000002    91455           1           9/5/17      1234    2       24.99   20.00
10000003    59943           2           9/5/17      3000    1       19.99   14.99
10000004    95027           2           9/5/17      18518   1       9.99    3.00
10000005    73994           2           9/5/17      12HA12  15      3.99    1.99
10000006    98464           1           10/1/17     wh30000 1       199.99  75.00

Please know that the table is not organized well.

I need to summarize total sales, total quantity sold, and total profit (which can be calculated as total sales less cogs) by the week number, store id.

How can I do this group by week where week starts on a Tuesday?

Answer 1

I created a sample out on SqlFiddle

To help sample your Week() starting on a Tuesday, I added about 11 rows at the end of the sample data you provided but gave dates Feb 1, 2020 to Feb 11, 2020.

MySQL appears to have SUNDAY as the default start of a Week. So, to have TUESDAY, I am taking whatever the transaction date is and shifting it backwards 2 days (Tuesday -1 = Monday - 1 = Sunday). So now the "WEEK" function will return the week of the year. You can see the results of that from the first query which differentiates between the original date and its day perception for WEEK(), then again AFTER that with the -2 shift to see the week changes based on TUESDAY of the week.

select
        week( SI.Time_Stamp ) DefWeek,
        dayName( SI.Time_Stamp ) DefWeekName,
        week( date_add( SI.Time_Stamp, interval -2 day )) TuesWeek,
        dayName( date_add( SI.Time_Stamp, interval -2 day )) TuesWeekName,
        SI.*
    from
        StackInvoice SI
    order by
        SI.Time_Stamp;

Now that you can see how that shift is applied, now its a simple aggregation.

select
        week( date_add( SI.Time_Stamp, interval -2 day )) TuesWeek,
        SI.Store_ID,
        sum( SI.Sales ) TotalSales,
        sum( SI.Units ) TotalUnits,
        sum( SI.Sales - SI.Cogs ) TotalProfit
    from
        StackInvoice SI
    group by
        week( date_add( SI.Time_Stamp, interval -2 day )),
        SI.Store_ID
    order by
        week( date_add( SI.Time_Stamp, interval -2 day )),
        SI.Store_ID