Click here to Skip to main content
15,506,025 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
My Table is below here :-

TranType	Trandt	ItemID	OpQty	OpRt	InQty	InRt	OutQty	OutRt
-------------------------------------------------------------------------
Opening		            1	50	    10	    0	    0	    0	    0
Purchase	05.04.21	1	0	    0	    60	    12	    0	    0
Purchase	25.01.22	1	0	    0	    80	    15	    0	    0
Purchase	31.03.22	1	0	    0	    100	    18	    0	    0
Sale	    04.04.22	1	0	    0	    0	    0	    20	    0
Sale	    05.04.22	1	0	    0	    0	    0	    25	    0
Sale	    06.04.22	1	0	    0	    0	    0	    15	    0
Purchase	08.40.22	1	0	    0	    10	    0	    0	    0
Sale)	    09.04.22	1	0	    0	    0	    0	    120	    0


My Formula is shown at Formula Column :-

TranType Trandt	ItemID OpQty OpRt InQty	InRt OutQty	OutRt Formula                        BalQty  ClQty   ClVal
--------------------------------------------------------------------------------------------------------------
Opening		        1  50	 10	  0	    0	 0	    0			                                 50 	 500
Purchase 05.04.21	1	0	 0	  60	12	 0	    0			                                 110	 1220
Purchase 25.01.22	1	0	 0	  80	15	 0	    0			                                 190	 2180
Purchase 31.03.22	1	0	 0	  100	18	 0	    0			                                 290	 3380
Sale	 04.04.22	1	0	 0	  0	    0	 20	    200	  if(50>20) then outqty*oprt    50-20=30 270	 3180
Sale	 05.04.22	1	0	 0	  0	    0	 25	    250	  if(30>25) then outqty*oprt	30-25=5	 245	 2930
Sale	 06.04.22	1	0	 0	  0	    0	 15	    170	  if(5<15) then(BalQty*oprt)+(15-5)*12) 60-10=50	230	2760
Purchase	08.40.22	1	0	0	10	20	0	0			240	2910
Sale	09.04.22	1	0	0	0	0	120	750	if(BalanceQty(50) < outqty(120)) then (BalanceQty * inrate) + ((OutQty(80) - BalanceQty(50) * InRate(12))) i.e. (50*12) + ((80-70)* 15)=750	80-70=10	120	2160

Actually I want to calculate the outrate of table coulmn by formula through sql query as any database version

My output result is below table:-


TranType	Trandate	ItemID	OpQty	OpRate	InQty	InRate	OutQty	OutRate	ClQty	ClVal
OP(Opening)		1	50	10	0	0	0	0	50	500
PU(Purchase)	05.04.21	1	0	0	60	12	0	0	110	1220
PU(Purchase)	25.01.22	1	0	0	80	15	0	0	190	2180
PU(Purchase)	31.03.22	1	0	0	100	18	0	0	290	3380
SL(Sale)	04.04.22	1	0	0	0	0	20	200	270	3180
SL(Sale)	05.04.22	1	0	0	0	0	25	250	245	2930
SL(Sale)	06.04.22	1	0	0	0	0	15	170	230	2760
PU(Purchase)	08.40.22	1	0	0	10	20	0	0	240	2910
SL(Sale)	09.04.22	1	0	0	0	0	120	750	120	2160


What I have tried:

Anybody can help me above problem.....
Thanks
Posted
Updated 15-Sep-22 3:39am
Comments
CHill60 14-Sep-22 7:48am    
Where are you getting the "50" from in if (50>20) - which is always true
Member 7678194 14-Sep-22 8:00am    
If(50>20) means OpQty column value of 1st row is 50 and OutQty column value of 5th row is 20
CHill60 14-Sep-22 9:53am    
Clear as mud. Please clearly explain the algorithm you want applied and share the code you used to extract ClQty and ClVal

1 solution

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
SQL
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.
SQL
;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[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900