T-SQL -RANGE VALUE GENERATION

Question

Hi All,

I have a requirement to generate Range sales like below.

Thanks in advance

Answer 1

Answer 2

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:

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.