Database
Enthusiast

## Weekly sales for different start dates

Hi everyone,
I’m trying to develop a report with customer sales behaviour each time a new product is Launched.
The columns in the report would be : sales in time t (the time product was launched ) , sales in time t-1 (week before) , sales in time (t-2) (2 weeks before) and sales in reporting week (this report will be refreshed every week)
I have a table with :
Product id, day launched

Then I have transactional data

My problem is that time t is different for each product so i am not really aware how can I make this in a optimized way..

Tags (3)

Accepted Solutions

## Re: Weekly sales for different start dates

Ok I've done some adaptation but I don't see any flaw in my logic.

Let the pivot aside for now, just focus on the comparaison between sales and launch dates :

```    select prd.product_id
, prd.launch_date
, sal.sale_date
, trunc(prd.launch_date, 'iw') as launch_week
, trunc(sal.sale_date, 'iw')   as sale_week
, coalesce(sal.sale_amount, 0) as sale_amount
, case
when trunc(sal.sale_date, 'iw') < trunc(prd.launch_date, 'iw') - 14 then 't--'
when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw') - 14 then 't-2'
when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw') -  7 then 't-1'
when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw')      then 't'
when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw') +  7 then 't+1'
when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw') + 14 then 't+2'
when trunc(sal.sale_date, 'iw') > trunc(prd.launch_date, 'iw') + 14 then 't++'
else 't_nul'
end as category
from mvt_products as prd
left join mvt_sales    as sal  on sal.product_id = prd.product_id
order by prd.product_id asc
, sal.sale_date  asc;

product_id launch_date sale_date  launch_week sale_week  sale_amount category
---------- ----------- ---------- ----------- ---------- ----------- --------
1 2018-12-15  2018-01-10 2018-12-10  2018-01-08        2.00 t--
1 2018-12-15  2018-12-17 2018-12-10  2018-12-17        5.00 t+1
1 2018-12-15  2018-12-19 2018-12-10  2018-12-17        4.00 t+1
1 2018-12-15  2018-12-27 2018-12-10  2018-12-24        3.00 t+2
1 2018-12-15  2019-01-15 2018-12-10  2019-01-14        7.00 t++
2 2018-12-25  2018-12-25 2018-12-24  2018-12-24        9.00 t
2 2018-12-25  2019-01-01 2018-12-24  2018-12-31        6.00 t+1
3 2019-01-25  {null}     2019-01-21  {null}            0.00 t_nul   ```

Do you see any sales not in the right category ?

1 ACCEPTED SOLUTION
12 REPLIES 12

## Re: Weekly sales for different start dates

How do you define week, a calendar week (Sun to Sat or Mon to Sun) or 7 days (launch  date - 6 to launch date)?

How do you want to show the launch date in your report?

Is this for a single product or a list of products?

Enthusiast

## Re: Weekly sales for different start dates

Hi dnoeth,

A week is from Monday to Sunday.

The report will have the following columns:

 product_id date_launched t-2 t-1 t t+1 t+2 ….

And i have a list of products with different launched dates!

Thank you!

## Re: Weekly sales for different start dates

Hi ApprenticeVS,

You have to make a join between your tables, use a case to compare the launch and sale dates to attribute them a generic value of t-2, t-1, t...

Then you have to pivot the data.

Datas

```create multiset volatile table mvt_products, no log
( product_id    integer
, launch_date   date
)
unique primary index (product_id)
on commit preserve rows;

insert into mvt_products values (1, date '2018-12-15');
insert into mvt_products values (2, date '2018-12-25');
insert into mvt_products values (3, date '2019-01-25');

create multiset volatile table mvt_sales, no log
( product_id    integer
, sale_date     date
, sale_amount   decimal(5,2)
)
primary index (product_id)
on commit preserve rows;

insert into mvt_sales values (1, date '2018-01-10', 2);
insert into mvt_sales values (1, date '2018-12-17', 5);
insert into mvt_sales values (1, date '2018-12-19', 4);
insert into mvt_sales values (1, date '2018-12-27', 3);
insert into mvt_sales values (1, date '2019-01-15', 7);
insert into mvt_sales values (2, date '2018-12-25', 9);
insert into mvt_sales values (2, date '2019-01-01', 6);```

Query

```with cte_set_time as
(
select prd.product_id
, prd.launch_date
, sal.sale_date
, coalesce(sal.sale_amount, 0) as sale_amount
, case
when trunc(sal.sale_date, 'iw') <= trunc(prd.launch_date, 'iw') - 14 then 't--'
when trunc(sal.sale_date, 'iw') <= trunc(prd.launch_date, 'iw') -  7 then 't-2'
when trunc(sal.sale_date, 'iw') <= trunc(prd.launch_date, 'iw')      then 't-1'
when trunc(sal.sale_date, 'iw') <= trunc(prd.launch_date, 'iw') +  7 then 't'
when trunc(sal.sale_date, 'iw') <= trunc(prd.launch_date, 'iw') + 14 then 't+1'
when trunc(sal.sale_date, 'iw') <= trunc(prd.launch_date, 'iw') + 21 then 't+2'
when trunc(sal.sale_date, 'iw') >  trunc(prd.launch_date, 'iw') + 21 then 't++'
else 't_nul'
end as category
from mvt_products as prd
left join mvt_sales    as sal on sal.product_id = prd.product_id
)
select product_id
, launch_date
, sum(case category when 't--'   then sale_amount else 0 end) as "t--"
, sum(case category when 't-2'   then sale_amount else 0 end) as "t-2"
, sum(case category when 't-1'   then sale_amount else 0 end) as "t-1"
, sum(case category when 't'     then sale_amount else 0 end) as "t"
, sum(case category when 't+1'   then sale_amount else 0 end) as "t+1"
, sum(case category when 't+2'   then sale_amount else 0 end) as "t+2"
, sum(case category when 't++'   then sale_amount else 0 end) as "t++"
, sum(case category when 't_nul' then 1           else 0 end) as "t_nul"
from cte_set_time
group by product_id
, launch_date
order by product_id asc;

product_id launch_date t--  t-2  t-1  t    t+1  t+2  t++  t_nul
---------- ----------- ---- ---- ---- ---- ---- ---- ---- -----
1 2018-12-15  2.00 0.00 0.00 9.00 3.00 0.00 7.00     0
2 2018-12-25  0.00 0.00 9.00 6.00 0.00 0.00 0.00     0
3 2019-01-25  0.00 0.00 0.00 0.00 0.00 0.00 0.00     1```

Enthusiast

## Re: Weekly sales for different start dates

Hi Waldar,

Thank you for the reply i think it's almost there but the t-2,t-3...it's not commulative..is t-1=week before launch; t-2=week before the week before launch

and also, how can i include in where clause a minimumand maximum date (which is the same as the day of analysis) so that i can take only sales from table mtv_sales  from days i need based on product launch dates and t-1,t-2,t+1,t+2....?

Thank you so much for the help so far!

## Re: Weekly sales for different start dates

I don't get the cummulative thing, it's not in my answer, I only add the amounts within the same week.

Considering the data I provided, what do you expect as output ?

For the filter, you have to handle it at two places.

In the product table and in the sales tables.

For example, inside the cte_set_time :

```      from mvt_products as prd
left join mvt_sales    as sal  on sal.product_id = prd.product_id
and trunc(sal.sale_date, 'iw') >= trunc(date '2018-12-01', 'iw') - 14
and trunc(sal.sale_date, 'iw') <  trunc(date '2019-01-01', 'iw') + 21
where prd.launch_date >= date '2018-12-01'
and prd.launch_date <  date '2019-01-01'```
Enthusiast

## Re: Weekly sales for different start dates

I expect the following (as an example)

 id_prod launch_date t-- t-2 t-1 t 1 15/12/2018 2 0 0 0 2 25/12/2018 2 0 9 12

For example, for product 1 , sales on time t will be between '2018-12-10' and '2018-12-16' (Mon-Fri week of launch date). On time t-1 will be sales between '2018-12-03' and '2018-12-09' , on time t-2 will be sales between '2018-11-26' and '2018-12-02'.

Highlighted

## Re: Weekly sales for different start dates

As truncating to week returns a single date per week this can be simplified avoiding the nested CASEs:

```SELECT
prd.product_id
,prd.launch_date
,Sum(CASE WHEN Td_Monday(sal.sale_date) = Td_Monday(prd.launch_date) - 14 THEN sale_amount ELSE 0 END) AS "t-2"
,Sum(CASE WHEN Td_Monday(sal.sale_date) = Td_Monday(prd.launch_date) -  7 THEN sale_amount ELSE 0 END) AS "t-1"
,Sum(CASE WHEN Td_Monday(sal.sale_date) = Td_Monday(prd.launch_date)      THEN sale_amount ELSE 0 END) AS "t"
,Sum(CASE WHEN Td_Monday(sal.sale_date) = Td_Monday(prd.launch_date) +  7 THEN sale_amount ELSE 0 END) AS "t+1"
,Sum(CASE WHEN Td_Monday(sal.sale_date) = Td_Monday(prd.launch_date) + 14 THEN sale_amount ELSE 0 END) AS "t+2"
,Sum(CASE WHEN Td_Monday(sal.sale_date) = Td_Monday(prd.launch_date) + 21 THEN sale_amount ELSE 0 END) AS "t+3"
FROM mvt_products AS prd
LEFT JOIN mvt_sales AS sal
ON sal.product_id = prd.product_id
GROUP BY 1,2```

I preferred Td_Monday over Trunc to get the previous Monday because it clearly states the week day and could be easily changed to other start days

## Re: Weekly sales for different start dates

Ok I've done some adaptation but I don't see any flaw in my logic.

Let the pivot aside for now, just focus on the comparaison between sales and launch dates :

```    select prd.product_id
, prd.launch_date
, sal.sale_date
, trunc(prd.launch_date, 'iw') as launch_week
, trunc(sal.sale_date, 'iw')   as sale_week
, coalesce(sal.sale_amount, 0) as sale_amount
, case
when trunc(sal.sale_date, 'iw') < trunc(prd.launch_date, 'iw') - 14 then 't--'
when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw') - 14 then 't-2'
when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw') -  7 then 't-1'
when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw')      then 't'
when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw') +  7 then 't+1'
when trunc(sal.sale_date, 'iw') = trunc(prd.launch_date, 'iw') + 14 then 't+2'
when trunc(sal.sale_date, 'iw') > trunc(prd.launch_date, 'iw') + 14 then 't++'
else 't_nul'
end as category
from mvt_products as prd
left join mvt_sales    as sal  on sal.product_id = prd.product_id
order by prd.product_id asc
, sal.sale_date  asc;

product_id launch_date sale_date  launch_week sale_week  sale_amount category
---------- ----------- ---------- ----------- ---------- ----------- --------
1 2018-12-15  2018-01-10 2018-12-10  2018-01-08        2.00 t--
1 2018-12-15  2018-12-17 2018-12-10  2018-12-17        5.00 t+1
1 2018-12-15  2018-12-19 2018-12-10  2018-12-17        4.00 t+1
1 2018-12-15  2018-12-27 2018-12-10  2018-12-24        3.00 t+2
1 2018-12-15  2019-01-15 2018-12-10  2019-01-14        7.00 t++
2 2018-12-25  2018-12-25 2018-12-24  2018-12-24        9.00 t
2 2018-12-25  2019-01-01 2018-12-24  2018-12-31        6.00 t+1
3 2019-01-25  {null}     2019-01-21  {null}            0.00 t_nul   ```

Do you see any sales not in the right category ?

Enthusiast

## Re: Weekly sales for different start dates

No, it is correct sorry it was my mistake when adapting to my data!

Thank you both for your help!!!!