What I want to do is return a single row (other than header, of course) with different sums of the 'widgets' based on different date ranges relative to the current date.
In this instance let's assume we've got a table which contains dates (in single date increments) for the past few years and a single metric column called 'widgets'. Let's call the dataset 'dataset1' for the sake of simplicity and the table can be called 'table1'. Sample data can be found at this Google Drive CSV export link - [login to view URL]
Here's a description of the columns I'd like to be returned:
Current date - not hard, let's say this was run on 2021-04-18 using CURRENT_DATE(+10).
Week commencing date for the last full week commencing on a Wednesday. We could use WEEK(WEDNESDAY) but that'll return a number in the range [0,53] - how do we convert that back to a date commencing?
Week commencing date for the last full week before the week now in Col2. Think this would be an easy DATE_SUB?
Week commencing date for the week in Col2 but year-on-year. Unfortunately we can't just subtract a year from the date otherwise that will be on a different day of week.
Date commencing of the last full month completed. I think this is relatively easy as we can use something like...
DATE_SUB(DATE CURRENT_DATE(+10), INTERVAL 1 MONTH)
I'm just unsure about this part of the documentation though and how that might throw things out:
Special handling is required for MONTH, QUARTER, and YEAR parts when the date is at (or near) the last day of the month. If the resulting month has fewer days than the original date's day, then the result day is the last day of the new month.
Sum of widgets filtered to the week commencing date described in Col2.
Col7 through to Col9:
Sum of widgets filtered to times matching Col3, Col4 and Col5 respectively (Col3/4 being a week and Col5 being a month).
Just say the query was run today, I'd be expecting the following as a result:
Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | Col8 | Col9
2021-04-18 | 2021-04-07 | 2021-03-31 | 2020-04-01^ | 2021-03-01 | 147,350 | 140,063 | 167,610^ | 632,891
^ NOTE - I think this should be correct since 2020 started on a Wednesday, so it should be 2020-04-01 but it could potentially be 2020-04-08. I've based Col4 and Col8 on 2020-04-01 though.
If anyone could provide a bit of expertise it would be very much appreciated.