T-SQL -RANGE VALUE GENERATION

BHVS 61 Reputation points
2022-03-29T17:12:40.837+00:00

Hi All,

I have a requirement to generate Range sales like below.

188043-source-data.png
Thanks in advance

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
{count} votes

2 answers

Sort by: Most helpful
  1. BHVS 61 Reputation points
    2022-03-29T17:17:11.053+00:00

    188024-finaloutput.png


  2. LiHong-MSFT 10,046 Reputation points
    2022-03-30T08:39:38.513+00:00

    Hi @BHVS
    Check this query:

    create table #test  
    (  
    PRODUCT varchar(10),  
    SALES int,  
    VALUE int  
    )  
    insert into #test values  
    ('PRD1',10,1),('PRD1',20,1),('PRD1',40,1),('PRD1',15,-1),('PRD1',23,-1),('PRD1',14,-1),  
    ('PRD1',21,1),('PRD1',29,1),('PRD1',33,1),('PRD1',17,-1),('PRD1',26,-1),('PRD1',22,-1),  
    ('PRD2',11,1),('PRD2',13,1),('PRD2',22,-1),('PRD2',28,-1)  
      
    ;WITH CTE1 AS  
    (  
     SELECT *,CASE WHEN VALUE = LAG(VALUE)OVER(ORDER BY PRODUCT) THEN 0 ELSE 1 END AS TO_SUM  
     FROM #test  
    ),CTE2 AS  
    (  
     SELECT *,SUM(TO_SUM)OVER(ORDER BY PRODUCT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PART  
     FROM CTE1  
    ),CTE3 AS  
    (  
     SELECT *,SUM(SALES)OVER(PARTITION BY PART ORDER BY PRODUCT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS RANGE_END  
     FROM CTE2  
    )  
    SELECT PRODUCT,SALES,VALUE,CAST((LAG(RANGE_END,1,0)OVER(PARTITION BY PART ORDER BY PRODUCT)+1)AS VARCHAR(10))+' - '+CAST(RANGE_END AS VARCHAR(10))AS [FINAL SALES RANGES]  
    FROM CTE3  
    

    Output:
    188312-image.png

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    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.

    0 comments No comments