Getting Complex Result With Balance-CodePudding

I have the two tables Issue and Received. I want to get result with same row of issue.

Issue table:

NO	LCODE	PCS
1	L0001	10
2	L0002	25
3	L0003	75
4	L0004	12
5	L0005	15

Received table:

NO	LCODE	PCS
1	L0001	5
2	L0001	5
3	L0003	48
4	L0003	12
5	L0003	15

Wanted result:

NO	LCODE	ISSPCS	RECPCS	BAL
1	L0001	10	5	5
2	L0001	0	5	0
3	L0003	75	48	27
4	L0003	0	12	15
5	L0003	0	15	0
6	L0004	12	0	12
7	L0005	15	0	15

SELECT ROW_NUMBER() OVER(PARTITION BY i.LCODE ORDER BY i.LCODE) as NO,
i.LCODE,i.PCS as ISSPCS,r.PCS as RECPCS,(i.PCS-r.PCS) as BAL 
FROM Issue i
LEFT JOIN Received  r ON i.LCODE = r.LCODE

Find The Best Way To Getting This Result Thank you.

CodePudding user response：

Try this code:

SELECT  ROW_NUMBER() OVER ( ORDER BY i.LCODE ASC, RunninngSum ASC) AS [NO]
,       i.LCODE
,       CASE WHEN RunninngSum > r.PCS THEN 0 ELSE i.PCS 
        END AS ISSPCS
,       ISNULL( r.PCS, 0) AS RECPCS
,       i.PCS - ISNULL( RunninngSum, 0) as BAL
FROM Issue i
LEFT JOIN Received r ON i.LCODE = r.LCODE 
CROSS APPLY (
    SELECT SUM( r1.PCS) AS RunninngSum 
    FROM Received r1 
    WHERE r1.LCODE = r.LCODE AND r1.[NO] <= r.[NO]
)x