I have a table "S8" displaying the Release Date(this is the Saturday of the week they were released), Units Sold, Revenue, and Estimated Weeks of Supply for several books. These values are generated on a weekly basis (Saturday week ending)
BOOKTITLE,BOOKDESCRIPTION,BOOKTYPE,RETAILER,WEEKSOLD,RELEASEDATEWEEK,UNITSSOLD,REVENUE,WEEKSOFSUPPLY
A,AAAAAAA,ORIGINAL,AMAZON,4/6/2019,4/20/2019,2,10,10
A,AAAAAAA,ORIGINAL,AMAZON,4/13/2019,4/20/2019,3,15,9
A,AAAAAAA,ORIGINAL,AMAZON,4/20/2019,4/20/2019,4,20,9
A,AAAAAAA,ORIGINAL,AMAZON,4/27/2019,4/20/2019,1,5,10
A,AAAAAAA,ORIGINAL,AMAZON,5/4/2019,4/20/2019,1,5,8
A,AAAAAAA,ORIGINAL,B&N,4/6/2019,4/20/2019,2,10,7
A,AAAAAAA,ORIGINAL,B&N,4/13/2019,4/20/2019,8,40,5
A,AAAAAAA,ORIGINAL,B&N,4/20/2019,4/20/2019,5,25,6
A,AAAAAAA,ORIGINAL,B&N,4/27/2019,4/20/2019,6,30,6
A,AAAAAAA,ORIGINAL,B&N,5/4/2019,4/20/2019,4,20,10
B,BBBBBBBB,SEQUEL,AMAZON,4/6/2019,4/13/2019,4,20,1
B,BBBBBBBB,SEQUEL,AMAZON,4/13/2019,4/13/2019,5,25,2
B,BBBBBBBB,SEQUEL,AMAZON,4/20/2019,4/13/2019,3,15,4
B,BBBBBBBB,SEQUEL,AMAZON,4/27/2019,4/13/2019,4,20,4
B,BBBBBBBB,SEQUEL,AMAZON,5/4/2019,4/13/2019,8,40,5
B,BBBBBBBB,SEQUEL,B&N,4/6/2019,4/13/2019,2,10,3
B,BBBBBBBB,SEQUEL,B&N,4/13/2019,4/13/2019,6,30,7
B,BBBBBBBB,SEQUEL,B&N,4/20/2019,4/13/2019,4,20,4
B,BBBBBBBB,SEQUEL,B&N,4/27/2019,4/13/2019,4,20,4
B,BBBBBBBB,SEQUEL,B&N,5/4/2019,4/13/2019,3,15,5
For reporting purposes, I need to build a view that rolls up all Pre-sales (book purchases occurring on the weeks before the week of release) into the first week of sales.
To clarify, every book has a pre-sell period where you can purchase the book before the book is actually released. I need to generate a view where all of those pre-sells that occur before the release date get added into the first week of sales. So if the book sold 4 units during its release week and 10 units during the presell period, I need to report week 1 as 14 units and remove the dates before the release week from the dataset.
Example:
BOOKTITLE,BOOKDESCRIPTION,BOOKTYPE,RETAILER,WEEKSOLD,RELEASEDATEWEEK,UNITSSOLD,REVENUE,WEEKSOFSUPPLY
A,AAAAAAA,ORIGINAL,AMAZON,4/20/2019,4/20/2019,9,45,9
A,AAAAAAA,ORIGINAL,AMAZON,4/27/2019,4/20/2019,1,5,10
A,AAAAAAA,ORIGINAL,AMAZON,5/4/2019,4/20/2019,1,5,8
A,AAAAAAA,ORIGINAL,B&N,4/20/2019,4/20/2019,15,75,6
A,AAAAAAA,ORIGINAL,B&N,4/27/2019,4/20/2019,6,30,6
A,AAAAAAA,ORIGINAL,B&N,5/4/2019,4/20/2019,4,20,10
B,BBBBBBBB,SEQUEL,AMAZON,4/13/2019,4/13/2019,9,45,2
B,BBBBBBBB,SEQUEL,AMAZON,4/20/2019,4/13/2019,3,15,4
B,BBBBBBBB,SEQUEL,AMAZON,4/27/2019,4/13/2019,4,20,4
B,BBBBBBBB,SEQUEL,AMAZON,5/4/2019,4/13/2019,8,40,5
B,BBBBBBBB,SEQUEL,B&N,4/13/2019,4/13/2019,8,40,7
B,BBBBBBBB,SEQUEL,B&N,4/20/2019,4/13/2019,4,20,4
B,BBBBBBBB,SEQUEL,B&N,4/27/2019,4/13/2019,4,20,4
B,BBBBBBBB,SEQUEL,B&N,5/4/2019,4/13/2019,3,15,5
I can generate the Units/Revenue roll-ups using this query:
SELECT BOOKTITLE,BOOKDESCRIPTION,BOOKTYPE,RETAILER,RELEASEDATEWEEK, SUM(UNITSSOLD), SUM(REVENUE)
FROM S8
WHERE WEEKSOLD <= RELEASEDATEWEEK
GROUP BY BOOKTITLE,BOOKDESCRIPTION,BOOKTYPE,RETAILER,RELEASEDATEWEEK
I'm not sure how I can use a view to replace the values with the rolled-up values where appropriate nor can I figure out how to only keep the most recent Weeks of Supply (no need to aggregate them I only need the most recent one on the release date week)
How about a conditional "if the weeksold date is before the releasedateweek date then use the releasedateweek date else use the weeksold date" ? In SQL that would look something like CASE WHEN WEEKSOLD < RELEASEDATEWEEK THEN RELEASEDATEWEEK ELSE WEEKSOLD END AS ROLLED_SOLD. YOu can then sum and group on that, given that you have the release date on each row of S8:
SELECT
BOOKTITLE,
BOOKDESCRIPTION,
BOOKTYPE,
RETAILER,
CASE WHEN WEEKSOLD < RELEASEDATEWEEK THEN RELEASEDATEWEEK ELSE WEEKSOLD END AS ROLLED_SOLD,
SUM(UNITSSOLD),
SUM(REVENUE)
FROM S8
GROUP BY
BOOKTITLE,
BOOKDESCRIPTION,
BOOKTYPE,
RETAILER,
CASE WHEN WEEKSOLD < RELEASEDATEWEEK THEN RELEASEDATEWEEK ELSE WEEKSOLD END AS ROLLED_SOLD
Hopefully these are dates and not "strings that look like dates". If they're strings then