T-SQL -RANGE VALUE GENERATION

Question

Hi All,

I have a requirement to generate Range sales like below.

Thanks in Advance...

Answer 1

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  

Best regards,
LiHong

Answer 2

I have a requirement to generate Range sales like below.

And now we have to guess the logic for it?

That's really to less on information. Please post table design as DDL, some sample data as DML statement and the expected result.