calculate sales from 8 weeks behind

Question

calculate sales from 8 weeks behind

Anonymous

Hi

I need to calculate sales from 8 weeks behind of each starting_date based on Product_id and Code. I appreciated it if you can help me.

Create table Test1 (starting_date Date, Ending_Date Date, Product_ID int, Code varchar(1), Sales Int)
insert into Test1
('2020-08-31','2020-09-06',1,O,10)
,('2020-08-24','2020-08-30',1,O,9)
,('2020-08-17','2020-08-23',1,B,0)
,('2020-08-10','2020-08-16',1,O,2)
,('2020-08-10','2020-08-16',2,O,23)
,('2020-08-10','2020-08-16',1,B,90)
,('2020-08-03','2020-08-09',1,O,5)
,('2020-08-03','2020-08-09',1,B,0)
,('2020-07-27','2020-07-02',1,B,0)
,('2020-07-20','2020-07-26',1,B,8)
,('2020-07-13','2020-07-19',1,O,0)
,('2020-07-06','2020-07-12',1,O,3)
,('2020-06-29','2020-07-05',1,B,90)
,('2020-06-22','2020-06-28',1,O,6)
,('2020-06-15','2020-06-21',1,B,7)
,('2020-06-08','2020-06-14',1,O,10)
,('2020-06-08','2020-06-14',1,B,0)
('2020-06-01','2020-06-07',1,O,2)

I need to have the below result:

Starting_Date, Ending_Date, Product_ID, Code, Sales, 8weeks_Behind

2020-08-31,2020-09-06,1,O,45,2020-06-08
2020-08-24,2020-08-30,1,O,60,2020-06-01
2020-08-17,2020-08-23,1,B,195,2020-06-08
....
....

MelissaMa-MSFT 24,221 Reputation points

2020-09-10T01:01:30.397+00:00

Hi anonymous user,

Could you please validate all the answers so far and provide any update about this?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards
Melissa

Accepted answer

3 additional answers

Your answer

MelissaMa-MSFT 24,221 Reputation points

2020-09-10T01:01:30.397+00:00

Hi anonymous user,

Could you please validate all the answers so far and provide any update about this?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards
Melissa

Answer 1

Hi anonymous user,

I checked manually with the second output of startdate '2020-08-24' and the total sales could be 37 instead of 60. Please help doublecheck it.

Please refer below query and check whether it is helpful to you. Thanks.

;with cte as   
(  
SELECT  starting_date,Ending_Date,PRODUCT_ID,CODE,SUM(SALES) OVER (PARTITION BY PRODUCT_ID,CODE ORDER BY starting_date DESC ROWS BETWEEN CURRENT ROW AND 7 FOLLOWING) SALES  
FROM TEST1 )  
  
SELECT A.starting_date,Ending_Date,PRODUCT_ID,CODE,SALES,start_calculation_date  
FROM CTE A  
 CROSS APPLY (  
         SELECT x.starting_date AS start_calculation_date  
         FROM (  
             SELECT TOP 8 *, ROW_NUMBER() OVER (ORDER BY starting_date DESC) AS RowNumber  
             FROM Test1   
             WHERE Product_ID = A.Product_ID AND Code = A.Code AND starting_date <= A.starting_date  
         ) AS x  
         WHERE x.RowNumber = 8   
     ) AS C  
ORDER BY A.starting_date DESC

Output:

starting_date Ending_Date PRODUCT_ID CODE SALES start_calculation_date  
2020-08-31 2020-09-06 1 O 45 2020-06-08  
2020-08-24 2020-08-30 1 O 37 2020-06-01  
2020-08-17 2020-08-23 1 B 195 2020-06-08

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

;WITH CTE_Start_Calculation_Date AS (
    SELECT *
    FROM Test1 AS t1
    CROSS APPLY (
        SELECT x.starting_date AS start_calculation_date
        FROM (
            SELECT TOP 8 *, ROW_NUMBER() OVER (ORDER BY starting_date DESC) AS RowNumber
            FROM Test1 
            WHERE Product_ID = t1.Product_ID AND Code = t1.Code AND starting_date <= t1.starting_date
        ) AS x
        WHERE x.RowNumber = 8 
    ) AS t2
)

SELECT 
    t.starting_date, 
    t.Ending_Date,
    t.Product_ID,
    t.Code,
    Sales = (SELECT SUM(Sales) FROM Test1 WHERE Product_ID = t.Product_ID AND Code = t.Code AND starting_date >= c.start_calculation_date GROUP BY Product_ID, Code),
    c.start_calculation_date AS [8weeks_Behind]
FROM Test1 AS t
INNER JOIN CTE_Start_Calculation_Date AS c
ON c.starting_date = t.starting_date AND c.Product_ID = t.Product_ID AND c.Code = t.Code
ORDER BY starting_date DESC;

Answer 3

Please rephrase your question, it's not clear what you want. Also use the 1010101 button to embed code into your posts to increase readability and working with your code samples, edit your post..

E.g. use this as starting point:

DECLARE @Test1 TABLE (
    starting_date DATE ,
    Ending_Date DATE ,
    Product_ID INT ,
    Code VARCHAR(1) ,
    Sales INT
);

INSERT INTO @Test1
VALUES ( '2020-08-31', '2020-09-06', 1, 'O', 10 ) ,
       ( '2020-08-24', '2020-08-30', 1, 'O', 9 ) ,
       ( '2020-08-17', '2020-08-23', 1, 'B', 0 ) ,
       ( '2020-08-10', '2020-08-16', 1, 'O', 2 ) ,
       ( '2020-08-10', '2020-08-16', 2, 'O', 23 ) ,
       ( '2020-08-10', '2020-08-16', 1, 'B', 90 ) ,
       ( '2020-08-03', '2020-08-09', 1, 'O', 5 ) ,
       ( '2020-08-03', '2020-08-09', 1, 'B', 0 ) ,
       ( '2020-07-27', '2020-07-02', 1, 'B', 0 ) ,
       ( '2020-07-20', '2020-07-26', 1, 'B', 8 ) ,
       ( '2020-07-13', '2020-07-19', 1, 'O', 0 ) ,
       ( '2020-07-06', '2020-07-12', 1, 'O', 3 ) ,
       ( '2020-06-29', '2020-07-05', 1, 'B', 90 ) ,
       ( '2020-06-22', '2020-06-28', 1, 'O', 6 ) ,
       ( '2020-06-15', '2020-06-21', 1, 'B', 7 ) ,
       ( '2020-06-08', '2020-06-14', 1, 'O', 10 ) ,
       ( '2020-06-08', '2020-06-14', 1, 'B', 0 ) ,
       ( '2020-06-01', '2020-06-07', 1, 'O', 2 );

SELECT   O.starting_date ,
         MAX(O.Ending_Date) AS Max_Ending_Date ,
         O.Product_ID ,
         O.Code ,
         DATEADD(WEEK, -8, O.starting_date) AS w8_starting_date ,
         SUM(O.Sales) AS Total_Sales ,
         (   SELECT SUM(I.Sales) AS Sum_Sales
             FROM   @Test1 I
             WHERE  I.starting_date <= DATEADD(WEEK, -8, O.starting_date)
                    AND I.Product_ID = O.Product_ID
                    AND I.Code = O.Code ) AS Sum_Sales
FROM     @Test1 O
GROUP BY O.starting_date ,
         O.Product_ID ,
         O.Code
ORDER BY O.starting_date DESC ,
         O.Product_ID ,
         O.Code;

Answer 4

Anonymous

Hi,

Thanks for your help.
Sorry, your query doesn't give me the result that I mentioned. Your query calculated 8 weeks behind of each starting_date even there are no sales for that week.

I'm looking for 8 weeks of sales available behind each starting_Date based on Product_Id and Code.

for example:
Product_Id=1 and Code='O' starting_Date='2020-08-31', the 8 weeks salesavailable behind of the starting_date, should be '2020-06-08' and sum(sales)=45

Create table Test1 (starting_date Date, Ending_Date Date, Product_ID int, Code varchar(1), Sales Int)
insert into Test1
('2020-08-31','2020-09-06',1,'O',10)
,('2020-08-24','2020-08-30',1,'O',9)
,('2020-08-17','2020-08-23',1,'B',0)
,('2020-08-10','2020-08-16',1,'O',2)
,('2020-08-10','2020-08-16',2,'O',23)
,('2020-08-10','2020-08-16',1,'B',90)
,('2020-08-03','2020-08-09',1,'O',5)
,('2020-08-03','2020-08-09',1,'B',0)
,('2020-07-27','2020-07-02',1,'B',0)
,('2020-07-20','2020-07-26',1,'B',8)
,('2020-07-13','2020-07-19',1,'O',0)
,('2020-07-06','2020-07-12',1,'O',3)
,('2020-06-29','2020-07-05',1,'B',90)
,('2020-06-22','2020-06-28',1,'O',6)
,('2020-06-15','2020-06-21',1,'B',7)
,('2020-06-08','2020-06-14',1,'O',10)
,('2020-06-08','2020-06-14',1,'B',0)
('2020-06-01','2020-06-07',1,'O',2)

I need to have the below result:

Starting_Date, Ending_Date, Product_ID, Code, Sales, 8weeks_Behind

2020-08-31,2020-09-06,1,O,45,2020-06-08
2020-08-24,2020-08-30,1,O,60,2020-06-01
2020-08-17,2020-08-23,1,B,195,2020-06-08

Guoxiong 8,206 Reputation points

2020-09-08T18:35:00.827+00:00

There are 12 weeks between 2020-06-08 and 2020-08-31. Just want to confirm the calculation is based on 8 or 12 weeks?
Anonymous

2020-09-08T18:55:17.203+00:00

there are 12 weeks, but we need to calculate 8 weeks behind available with sales (even sales is zero), for that product_id=1 and code='O' the available weeks should be calculated,
Anonymous

2020-09-08T18:57:36.79+00:00

please don't consider how many weeks are between 2020-06-08 and 2020-08-31.
I'm looking for 8 weeks behind of each starting_date and Product_id, and code.

for example:
Product_Id=1 and Code='O' starting_Date='2020-08-31', the 8 weeks salesavailable behind of the starting_date, should be '2020-06-08' and sum(sales)=45

Viorel 122.6K

Check these results:

;
with W as
(
 select *, 
    (
        select top(1) starting_date
        from (
            select top(7) starting_date
            from Test1
            where starting_date <= t.starting_date
            and Product_ID = t.Product_ID 
            and Code = t.Code
            and Sales <> 0
            order by starting_date desc
        ) as d 
        order by starting_date asc
    ) as [8weeks_Behind_with_Sales]
    from Test1 as t
)
select *, 
    ( select sum(Sales) 
      from (
            select Sales
            from Test1
            where starting_date between W.[8weeks_Behind_with_Sales] and W.starting_date
            and Product_ID = W.Product_ID 
            and Code = W.Code
        ) d
    ) as Sales8Weeks
from W
order by starting_date desc

Share via

calculate sales from 8 weeks behind

3 additional answers

Your answer