March 13, 2015 at 12:10 pm
I have below records coming in from source files
ProdName Amount TranType
P1 100 A
P1 100 S
P2 200 A
P2 205 S
In case the ProdName is same, and Amount = or (within +/- 5%) of Amount, I have to update the TranType column as IN/OUT respectively as shown below in the tables.
I am okay with using 2 different tables if needed as in the records comes in one table and then i can reference that table to upload the values in another.
ProdName Amount TranType
P1 100 IN
P1 100 OUT
P2 200 IN
P2 205 OUT
Can anyone help me with this ??? This is very urgent. The order of the records coming in can be different order, they need not be subsequent.
This is happening in SQl Server 2008.
March 13, 2015 at 4:04 pm
I have a solution but need a little clarification...
What exactly do you mean by
Amount = or (within +/- 5%) of Amount
For example, Let's say Amount1 is 1051 and Amount2 is 1000. Amount1 is 105.1% of Amount2; Amount2 is 95.147% of Amount1.
DECLARE
@a decimal(6,2) = 1051, --Amount1
@b decimal(6,2) = 1000; --Amount2
SELECT (@a/@b);--@a is 105.1% of @b (e.g. @a is 5.1% bigger than @b)
SELECT (@b/@a);--@b is 95.147% of @a (e.g. @b is 4.85% smaller than @b)
In the above scenario are the two numbers within 5% of one another?
-- Itzik Ben-Gan 2001
March 13, 2015 at 5:05 pm
Here's a solution.
I'm sure there is an easier way but this is a cursor/loop-free way that will work.
/*Assumptions
(1) There are alway 2 product names that are equal (never more, never less)
(2) One of the product names has a trantype of A, the other trantype of B
*/
DECLARE @YourTable TABLE
(
ProdName char(2) not null,
CharAmount int not null,
TranType char(3) not null
);
-- added a couple extra values
INSERT @YourTable
VALUES ('P1',100,'A'),('P1',100,'S'), ('P2',200,'A'),('P2',205,'S'),('P3',100,'A'),('P3',130,'S'),('P4',1051,'A'),('P4',1000,'S');
SELECT * FROM @YourTable;
WITH
prodPairs AS
(
SELECT
pairID = DENSE_RANK() OVER (ORDER BY ProdName),
ProdName,
CharAmount = CAST(CharAmount as decimal(6,2)),
TranType
FROM @YourTable
),
ComparePairs AS
(
SELECT pp.*, CharAmount2 = (SELECT TOP(1) pp2.charAmount FROM prodPairs pp2 WHERE TranType = 'S' AND pp2.pairID = pp.pairID)
FROM prodPairs pp
WHERE TranType = 'A'
),
results AS
(
SELECT
ProdName2 = ProdName,
TranType2 = TranType,
gt =
CASE
WHEN (
CASE
-- If you answered YES to my question then this is good:
WHEN CharAmount > CharAmount2 THEN (CharAmount-CharAmount2)/CharAmount
WHEN CharAmount < CharAmount2 THEN (CharAmount2-CharAmount)/CharAmount2
-- If you answered NO to my question then remove the above two lines and uncomment the 2 lines below:
--WHEN CharAmount > CharAmount2 THEN (CharAmount-CharAmount2)/CharAmount2
--WHEN CharAmount < CharAmount2 THEN (CharAmount2-CharAmount)/CharAmount
ELSE 0
END ) <= .05
THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END
FROM ComparePairs
)
UPDATE @YourTable
SET TranType = (CASE WHEN gt=1 AND TranType = 'A' THEN 'IN' WHEN gt=1 AND TranType = 'S' THEN 'OUT' ELSE TranType END)
FROM results
WHERE ProdName = results.ProdName2 ;
SELECT *
FROM @YourTable;
-- Itzik Ben-Gan 2001
April 1, 2015 at 9:34 pm
Earlier I was not able to describe the problem correctly.
Please find a detailed description and the Image file of the data, that I am working on.
Requirements:
1. If 'Channel' is not equal to "Omnibus" where the 'Trans Description'is equal to "Purchase" and "Redemption" for one purchase and one redemption that match on 'System' , 'Account TA Number' , 'Product Name' , 'Settled Date' , and where the 'Trade Amount' of the purchase and redemption is within 5%, then display those set of trades.
2. If deemed wash trades, allow user to update the purchase and redemption pair 'Trans Description' from "Purchase" to "Exchange In" and 'Trans Description' from "Redemption" with "Exchange Out".
SystemChannelProduct NameProduct Share ClassTrade IDSettled DateAccount TA NumberTrans Description Trade Amount
SCHWABPORTALUS - ASGStrategic Income FundA2980625930-Jan-15000BY00F2RWRedemption $25,68,458.15
SCHWABPORTALUS - ASGStrategic Income FundInstitutional2980626330-Jan-15000BY00F2RWPurchase $25,68,488.04
DSTUS - ASGSocially Responsive FundInstitutional2971141821-Jan-158010000418Redemption $99,82,249.13
DSTUS - ASGSocially Responsive FundR62971209921-Jan-151283100037Purchase $1,00,57,328.32
DSTUS - ASGSocially Responsive FundTrust2945991702-Jan-155870002374Redemption $1,39,85,707.98
DSTUS - ASGSocially Responsive FundInstitutional2945970302-Jan-158010000338Purchase $1,32,96,637.40
SCHWABPORTALUS - ASGHigh Income Bond FundInstitutional2561591016-Jan-14000BY00F351Redemption $8,37,11,868.09
SCHWABPORTALUS - ASGHigh Income Bond FundR62561596316-Jan-14000BY00F351Purchase $8,37,11,868.09
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply