starting SUM values from a specific

Question

i need to create select sum (trn_qnty) that start from last trn_typ=26 that means start sum from row number 5

i create statement but return only item have typ=26

select a.trn_itmsrl,SUM(a.trn_qnty) from EnterItemsTransaction a
where a.tran_date>=(select top 1 isnull(b.tran_date,'2000/01/01')
from EnterItemsTransaction b where b.trn_typ=26 and b.trn_itmsrl=a.trn_itmsrl order by tran_date desc)
group by a.trn_itmsrl

i need all item(trn_itmsrl) that have trn_typ=26 or no if have trn_typ=26 start from last

Answer 1

Hi @mohamed basha
There is one thing you didn't make clear. If an item(trn_itmsrl) does not have trn_typ=26, then you want to sum all trn_qnty for that item, or not sum and return '0' instead.
If you want to sum all trn_qnty for item that does not have trn_typ=26, then check this :

;WITH CTE AS   
(  
SELECT trn_itmsrl,MAX(CASE WHEN trn_typ=26 THEN tran_date ELSE '2000/01/01' END) AS LAST_DATETIME  
FROM EnterItemsTransaction    
GROUP BY trn_itmsrl  
)  
SELECT T.trn_itmsrl,SUM(T.trn_qnty) AS Total_qnty   
FROM EnterItemsTransaction  T JOIN CTE C ON T.trn_itmsrl=C.trn_itmsrl AND T.tran_date>=C.LAST_DATETIME  
GROUP BY T.trn_itmsrl  

If you don't want to sum trn_qnty for item that does not have trn_typ=26, then check this :

;WITH CTE AS   
(  
SELECT trn_itmsrl,MAX(tran_date) AS LAST_DATETIME  
FROM EnterItemsTransaction      
WHERE trn_typ=26  
GROUP BY trn_itmsrl  
)  
SELECT T.trn_itmsrl,SUM(CASE WHEN C.trn_itmsrl IS NULL THEN 0 ELSE T.trn_qnty END) AS Total_qnty   
FROM EnterItemsTransaction  T LEFT JOIN CTE C ON T.trn_itmsrl=C.trn_itmsrl AND T.tran_date>=C.LAST_DATETIME  
GROUP BY T.trn_itmsrl  

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.