June 9, 2010 at 12:09 pm
Dear Team,
I have stuck with a problem, I have a scrip that is demerged into two scrips: the same scrip and a new one. So a sell entry is made for the scrip and two buy entries are made for the two scrips (asume 1 is previous and another onew is new) such that the holding amount of the previous scrip.
Sample Script :
-- Table for scrip which got demerge
Create table #holding
(
Type CHAR(1),
TranNo INT,
ScripName VARCHAR(250),
Date DATETIME,
NetRate NUMERIC(18, 4),
QuantityNUMERIC(18, 0),
Amount NUMERIC(18, 4)
)
insert into #holding(Type, TranNo, ScripName, Date, NetRate,Quantity,Amount)
SELECT 'P', 12345, 'MediaWorks Limited', '01-Jul-2009', 351.3, 1000, 351300
-- Main Table which hold demerged data
--drop table #hold
Create table #hold
(
TranNo int,
ScripName VARCHAR(250),
Exchange CHAR(5),
Date DATETIME,
BuySell CHAR(1),
NetRate NUMERIC(18, 4),
QuantityNUMERIC(18, 0),
Amount NUMERIC(18, 4),
Ratio NUMERIC(18, 4),
Type CHAR(1)
)
insert into #hold(TranNo, ScripName, Exchange, Date, BuySell, NetRate, Quantity, Amount, Type)
SELECT 12345, 'Media World Limited', 'DM*', '31-Jul-2009', 'B', 174.72, 500, 87360.375, 'S'
UNION ALL
SELECT 12345, 'Media World Limited', 'DM*', '31-Jul-2009', 'B', 174.72, 500, 87360.375, 'S'
UNION ALL
SELECT 12345, 'MediaWorks Limited', 'DM*', '31-Jul-2009', 'B', 580.01, 500, 290004.875, 'P'
UNION ALL
SELECT 12345, 'MediaWorks Limited', 'DM*', '31-Jul-2009', 'B', 580.01, 500, 290004.875, 'P'
UNION ALL
SELECT 12345, 'MediaWorks Limited', 'DM*', '31-Jul-2009', 'S', 754.73, 500, 377365.25, 'P'
UNION ALL
SELECT 12345, 'MediaWorks Limited', 'DM*', '31-Jul-2009', 'S', 754.73, 500, 377365.25, 'P'
Now Firstly I want to find the Ratio Based on This Formula
1. If Type is P then Sum of Buy Amount devide by Sum of Sell Amount from#hold table and Multiply by Amount field from #holding table
2. If Type is S then Sum of Buy Amount for S Type devide by Sum of Sell Amount for 'P' Type from #hold table multiply by Multiply by Amount field from #holding table.
Note You Can avoid the column type as required, For refrence excel is attached.
Kindly replay with possible solution
June 9, 2010 at 2:01 pm
I would like to help you but not enough information is included. What is the result set you are looking for? You refer to multiple 'scripts', but I'm not sure what you've included is part of the base data and what is the result of a script. So please make clear what your actual tables are. Also, keep in mind that many people (like myself) either can't or won't open up MS Office attachments so please include all this information in the actual post.
June 9, 2010 at 11:02 pm
expected result already present in attached excel.
But for your refrence mentioned below
ScripQtyRateAmountRatio
Media World Limited7/1/20091000351.3351300From #holding
Buy-Media World Limited7/24/2009100081.3262475581326.25269973.75From #hold
Buy-MediaWorks Limited7/24/20091000269.9737525269973.7581326.25From #hold
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply