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
ClVal
from
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
select TranType,Trandt,ItemID,OpQty,OpRt,InQty,InRt,OutQty,OutRt
,sum(OpQty + InQty - OutQty) over (order by ISNULL(Trandt, '2000-01-01')) AS ClVal
from @demo
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
ItemID
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
TranType
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
from @demo
)
SELECT *
,sum(QTY) over (partition by ItemID order by Trandt) AS ClVal
FROM CTE
returns
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[
^]