CODEWITHSUNDEEP
sqlsql-server
eman_invok05
eman_invok05

Reputation: 27

Select the matching/sum up total in SQL Server

I want to get all the id's that matches/sum up my total qty.

Example if my total qty is 40 then my query will stops until it sums up all the qty at exactly or greater than 40.

See screenshots

enter image description here

Upvotes: 0

Views: 86

Answers (1)

Serkan Arslan
Serkan Arslan

Reputation: 13393

If you are using sql server 2012 and above, you can use this script. 

;WITH CTE AS (
    SELECT PK_TRXNO, FK_iwItems, qty, 
        total = SUM(qty) OVER( PARTITION BY FK_iwItems ORDER BY PK_TRXNO DESC ROWS UNBOUNDED PRECEDING ) 
    FROM @MyTable
)
, CTE2 AS (
    SELECT *, 
       RN = ROW_NUMBER() OVER(PARTITION BY (CASE WHEN total > 40 THEN 1 ELSE 0 END) ORDER BY PK_TRXNO DESC)  
    FROM CTE 
)
SELECT * FROM CTE2
WHERE total <= 40 OR ( total> 40 AND RN = 1)

Upvotes: 1

Related Questions