Running Fractional Quantity Calculation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Running Fractional Quantity Calculation

Author

Topic

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-06-27 : 17:39:05

I have following data. I want to calculate the effective fractional quantity based on below values. I have mentioned the required output too. Can anybody help me.

declare @temp table (FractionalQty decimal(22, 6), TradeDate datetime, TranType varchar(10))
insert into @temp values( 1.0000 ,'3/29/2007' ,'TSFR IN' )
insert into @temp values( 0.9863 ,'4/25/2007' ,'SINK' )
insert into @temp values( 0.9902 ,'5/25/2007' ,'SINK' )
insert into @temp values( 0.9911 ,'6/25/2007' ,'SINK' )
insert into @temp values( 0.9842 ,'7/25/2007' ,'SINK' )
insert into @temp values( 0.9868 ,'8/27/2007' ,'SINK' )
insert into @temp values( 0.9867 ,'9/25/2007' ,'SINK' )
insert into @temp values( 0.9888 ,'10/25/2007' ,'SINK' )
insert into @temp values( 0.9889 ,'11/26/2007' ,'SINK' )
insert into @temp values( 0.9872 ,'12/26/2007' ,'SINK' )
insert into @temp values( 0.9822 ,'1/25/2008' ,'SINK' )
insert into @temp values( 0.9830 ,'2/25/2008' ,'SINK' )
insert into @temp values( 0.9833 ,'3/25/2008' ,'SINK' )
insert into @temp values( 0.9829 ,'4/25/2008' ,'SINK' )
insert into @temp values( 0.9870 ,'5/26/2008' ,'SINK' )
insert into @temp values( 0.3671 ,'6/3/2008' ,'SELL' )
insert into @temp values( 0.3103 ,'6/4/2008' ,'SELL' )
insert into @temp values( 0.9113 ,'6/5/2008' ,'SINK' )
insert into @temp values( 0.7221 ,'6/6/2008' ,'SELL' )

select * from @temp Order by TradeDate

--- REQUIRED OUTPUT ---
--------------------------------------------------------------------
FractionalQty Trade_Date Tran_Type CalculatedQty
--------------------------------------------------------------------
1 3/29/2007 TSFR IN 1.0000 (IF no SELL before this transaction THEN 1.0000)
0.9863 4/25/2007 SINK 1.0000 (IF no SELL before this transaction THEN 1.0000)
0.9902 5/25/2007 SINK 1.0000 (IF no SELL before this transaction THEN 1.0000)
0.9911 6/25/2007 SINK 1.0000 (IF no SELL before this transaction THEN 1.0000)
0.9842 7/25/2007 SINK 1.0000 (IF no SELL before this transaction THEN 1.0000)
0.9868 8/27/2007 SINK 1.0000 (IF no SELL before this transaction THEN 1.0000)
0.9867 9/25/2007 SINK 1.0000 (IF no SELL before this transaction THEN 1.0000)
0.9888 10/25/2007 SINK 1.0000 (IF no SELL before this transaction THEN 1.0000)
0.9889 11/26/2007 SINK 1.0000 (IF no SELL before this transaction THEN 1.0000)
0.9872 12/26/2007 SINK 1.0000 (IF no SELL before this transaction THEN 1.0000)
0.9822 1/25/2008 SINK 1.0000 (IF no SELL before this transaction THEN 1.0000)
0.983 2/25/2008 SINK 1.0000 (IF no SELL before this transaction THEN 1.0000)
0.9833 3/25/2008 SINK 1.0000 (IF no SELL before this transaction THEN 1.0000)
0.9829 4/25/2008 SINK 1.0000 (IF no SELL before this transaction THEN 1.0000)
0.987 5/26/2008 SINK 1.0000 (IF no SELL before this transaction THEN 1.0000)
0.3671 6/3/2008 SELL 0.3671 (0.3671)
0.3103 6/4/2008 SELL 0.1139 (0.3103*0.3671)
0.9113 6/5/2008 SINK 0.1139 (0.3103*0.3671) Exclude SINK here
0.7221 6/6/2008 SELL 0.0822 (0.3103*0.3671*0.7221) Excluding SINK here
--------------------------------------------------------------------

Thanks in advance
- Vipin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-28 : 03:51:41

something like:-

SELECT IDENTITY(int,1,1) AS Seq,* INTO #Temp
FROM @temp
ORDER BY TradeDate

SELECT t1.FractionalQty,t1.TradeDate, t1.TranType,
CASE WHEN t1.TranType='SINK' AND COUNT(t2.TradeDate)=0 THEN 1.0000
     WHEN t1.TranType='SELL' AND COUNT(t2.TradeDate)=0 THEN t1.FractionalQty
     WHEN t1.TranType='SINK' AND COUNT(t2.TradeDate)>0 THEN EXP(SUM(LOG(t2.FractionalQty)))
     WHEN t1.TranType='SELL' AND COUNT(t2.TradeDate)>0 THEN t1.FractionalQty * EXP(SUM(LOG(t2.FractionalQty)))
FROM #Temp t1
LEFT JOIN #Temp t2
ON t2.TradeDate <t1.TradeDate
AND t2.TranType='SELL'
GROUP BY t1.FractionalQty,t1.TradeDate, t1.TranType

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources