SQL sum 12 weeks of data based on first sold date across different items

Question

The database has thousands of individual items, each with multiple first sold dates and sales results by week. I need a total sum for each products first 12 weeks of sales.

Code was used for previous individual queries when we know the start date using a SUM(CASE. This is too manual though with thousands of products to review and we are looking for a smart way to speed this up.

Can I build on this so the sum find the minimum first shop date, and then sums the next 12 weeks of results? If so, how do I structure it, or is there a better way?

Columns in database I will need to reference with sample data

PROD_ID | WEEK_ID | STORE_ID | FIRST_SHOP_DATE | ITM_VALUE

12345543 | 201607 | 10000001 | 201542 | 24,356

12345543 | 201607 | 10000002 | 201544 | 27,356

12345543 | 201608 | 10000001 | 201542 | 24,356

12345543 | 201608 | 10000002 | 201544 | 27,356

32655644 | 201607 | 10000001 | 201412 | 103,245

32655644 | 201607 | 10000002 | 201420 | 123,458

32655644 | 201608 | 10000001 | 201412 | 154,867

32655644 | 201608 | 10000002 | 201420 | 127,865

1)tag the dbms being used.2)show some sample data and the expected result. — Vamsi Prabhala, Jun 30 '17 at 12:28
Add some sample table data and the expected result - as formatted text. Tag the dbms you're using. Show us your current query attempt. — jarlh, Jun 30 '17 at 12:28

score 2 · Answer 1 · answered Jun 30 '17 at 12:29

You can do something like this:

select itemid, sum(sales)
from (select t.*, min(shopdate) over (partition by itemid) as first_shopdate
      from t
     ) t
where shopdate < first_stopdate + interval '84' day
group by id;

You don't specify the database, so this uses ANSI standard syntax. The date operations (in particular) vary by database.

score 0 · Answer 2 · answered Jun 30 '17 at 12:56

Hi Kirsty, Try like this -

select a.Item,sum(sales) as totla
from tableName a JOIN
(select Item, min(FirstSoldDate) as FirstSoldDate from tableName group by item) b
ON a.Item = b.Item
where a.FirstSoldDate between b.FirstSoldDate and (dateadd(day,84,b.FirstSoldDate))
group by a.Item

Thanks :)

SQL sum 12 weeks of data based on first sold date across different items

2 Answers2