I've looked at this long and hard and cannot work out how you are calculating your expected results particularly where you got the figures for
Therefore this solution is based on describing techniques which should help you get the results you want.
Firstly, research Window Functions in SQL - see OVER Clause (Transact-SQL) - SQL Server | Microsoft Docs
Then you will be able to do things like
,sum(OpQty + InQty - OutQty) over (order by ISNULL(Trandt, '2000-01-01')) AS ClVal
Things to note from that example:
- I've chosen an opening date of January 1st 2000 because I need to ensure the order is correct without relying on the interpretation of a null date.
- I haven't PARTITIONed by the
but you will need to consider that
- @demo is the name of my table variable containing the data you included in your post.
- I've used
OpQty + InQty - OutQty
because only 1 of those quantities should be non-zero based on the
and that is simpler than a load of CASE statements.
Which leads me to suggest that an unpivoted table might be easier to handle e.g.
;with cte as
select TranType, isnull(Trandt, '2020-01-01') as Trandt,ItemID
,Case when TranType = 'Opening' THEN OpQty
when TranType = 'Purchase' THEN InQty
else OutQty end as QTY
,Case when TranType = 'Opening' THEN OpRt
when TranType = 'Purchase' THEN InRt
else OutRt end as RATE
,sum(QTY) over (partition by ItemID order by Trandt) AS ClVal
TranType Trandt ItemID QTY RATE ClVal
Opening 2020-01-01 1 50 10 50
Purchase 2021-04-05 1 60 12 110
Purchase 2022-01-25 1 80 15 190
Purchase 2022-03-31 1 100 18 290
Sale 2022-04-04 1 20 0 310
Sale 2022-04-05 1 25 0 335
Sale 2022-04-06 1 15 0 350
Purchase 2022-04-08 1 10 0 360
Sale 2022-04-09 1 120 0 480
For an alternative method see Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs
If you want to look at the values from previous rows when calculating something for the current row then you might need to research LAG (Transact-SQL) - SQL Server | Microsoft Docs