Hi @Parvez Alam ,
Please refer below updated one and check whether it is working.
;with cte as (
Select A.PONO,A.ItemName,A.Color,A.POQty,CASE WHEN rn=1 then case when POQty>B.DCQty THEN B.DCQty ELSE POQty END else 0 end DCQty
,case when rn=1 then b.DCQty else 0 end DCQty1
FROM (select ROW_NUMBER() over (partition by ItemName,Color order by pono) rn,* from [po]) A
LEFT JOIN [DCQty] B On A.ItemName=B.ItemName AND A.Color=B.Color
Where A.ItemName='A1' and A.Color='C1')
,cte1 as (
select PONO,ItemName,Color,POQty,isnull(lag(DCQty1-DCQty) over (partition by ItemName,Color order by pono),DCQty) DCQty
from cte)
select * from (
select *,
POQty-DCQty POBalQty
from cte1
union
select null,null,null,null,sum(DCQty),sum(POQty-DCQty) from cte1) a
order by isnull(PONO,'ZZZ')
Output:
Best regards,
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
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.