get no of quantity from sql table

Question

Hello
This is my sql resultset data

I want like this out put how to do so

Thank You

Answer 1

DECLARE @Products TABLE (
ORDERNO INT, RefNO int, Size int, quantity int)
INSERT INTO @Products(ORDERNO, RefNO, Size, quantity)
VALUES ( 26, 1, 7,20),(26, 2, 7,10),(27,4, 8,40)

;with mycte as (
SELECT a.ORDERNO,a.RefNO,a.Size,a.quantity, (
SELECT SUM(b.quantity)
FROM @Products b WHERE a.ORDERNO=b.ORDERNO
AND b.RefNO <= a.RefNO) AS runningTotal
,row_number() over(partition by ORDERNO order by RefNO ) rn
FROM @Products AS a
)

Select m1.ORDERNO,m1.RefNO,m1.Size,m1.quantity,
Case when m1.rn=1 then '01-'+Cast(m1.quantity as varchar(10))
else Cast(m2.runningTotal+1 as varchar(10))+'-'+Cast(m1.runningTotal+1 as varchar(10)) end NoOfQuantity
from mycte m1
left join mycte m2 on m1.ORDERNO=m2.ORDERNO
and m1.rn=m2.rn+1
ORDER BY m1.ORDERNO,m1.RefNO;

/*
ORDERNO RefNO Size quantity NoOfQuantity
26 1 7 20 01-20
26 2 7 10 21-31
27 4 8 40 01-40

*/

Answer 2

Hi @RAVI
Check this query:

;WITH CTE AS  
(  
 SELECT ORDERNO,Ref_No,SIZE,Quantity  
        ,LAG(Quantity,1,0)OVER(PARTITION BY ORDERNO,SIZE ORDER BY Ref_No)+1 AS Range_Start  
        ,SUM(Quantity)OVER(PARTITION BY ORDERNO,SIZE ORDER BY Ref_No ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS Range_End  
 FROM Your_Table  
)  
SELECT ORDERNO,Ref_No,SIZE,Quantity  
      ,CAST(Range_Start AS VARCHAR(30))+'-'+CAST(Range_End AS VARCHAR(30)) AS [No Of Quantity]  
FROM CTE  

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.

Answer 3

can i get this sql code using old version like sql 2008
thanks