November 12, 2009 at 10:07 am
Hi,
Only my second forum post so please don't be too harsh, I've read the forum etiquette notice and tried to follow it but please do not hesitate to let me know where I am falling short and where I need to improve.
My situation is that I am a new developer with C# & SQL server, converting a legacy application that used Btrieve (a cursor based record management system from the 1980's). The application contains a Foreign Exchange Trade database with a row for each executed deal. I need to take the data from the database and calculate the outstanding exposures i.e. amounts of currency bought and sold.
The script below creates and populates a stripped down version of the table and then calculates the resultant exposures, returning correctly GBP 100000 & USD -155000.
But to my newbie eye the script is:
a) overcomplicated - why do I need to create an intermediate table, can't SQL do this all directly?
b) contains duplication. In this very simplified model I've removed the WHERE clauses from the Bought currency block and Sold Currency block select statements (marked A & B below). In my current full example these are far from trivial.
Am I worrying unnecessarily? or is there a much neater way to do this?
Thanks in advance
Jeremy
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#myTrade','U') IS NOT NULL
DROP TABLE #myTrade
--===== Create the test table with
CREATE TABLE #myTrade
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
DateValue DATE,
BuyCurr CHAR(3),
BuyAmt DECIMAL,
SellCurr CHAR(3),
SellAmt DECIMAL
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #myTrade ON
--===== Insert the test data into the test tables
INSERT INTO #myTrade
(ID, DateValue, BuyCurr, BuyAmt, SellCurr, SellAmt)
SELECT '1','Jul 6 2009','GBP',100000, 'USD', -160000 UNION ALL
SELECT '2','Jul 7 2009','USD',170000, 'GBP', -100000 UNION ALL
SELECT '3','Jul 8 2009','GBP',100000, 'USD', -165000
SET IDENTITY_INSERT #myTrade OFF
-- Temp Table to accumulate exposures
DECLARE @exposure AS TABLE
(
Curr CHAR(3),
Amt DECIMAL
)
-- Fill our temp table
INSERT INTO @exposure
-- With our bought currencies - Block A
SELECT BuyCurr, SUM(BuyAmt) FROM #myTrade
GROUP BY BuyCurr
UNION
-- And our sold amounts - Block B
SELECT SellCurr, SUM(SellAmt) FROM #myTrade
GROUP BY SellCurr
-- Now return the summarised exposures
SELECT Curr, SUM(Amt) FROM @exposure
GROUP BY Curr
November 12, 2009 at 4:37 pm
One trick is to hit the table twice: the first pass selects the 'buy' columns and aliases their names to simply Curr and Amt, and the second pass select the 'sell' columns also aliases their names to simply Curr and Amt.
UNION ALL the two SELECT statements together, and you have a set ready to be grouped and summed in a single statement.
This produces the result of getting both 'buy' and 'sell' data into similar columns (not the final result yet):
SELECT BuyCurr AS Curr, BuyAmt AS Amt
FROM #myTrade
UNION ALL
SELECT SellCurr AS Curr, SellAmt AS Amt
FROM #myTrade
You can select directly from that as a derived table. The result:
-- Note: Use the code provided in your post to build the #myTrade
-- sample data table first
SELECT Curr, SUM(amt)
FROM ( SELECT BuyCurr AS Curr, BuyAmt AS Amt
FROM #myTrade
UNION ALL
SELECT SellCurr AS Curr, SellAmt AS Amt
FROM #myTrade
) AS TradeVals
GROUP BY Curr
-Eddie
Eddie Wuerch
MCM: SQL
November 13, 2009 at 3:18 am
Eddie Wuerch (11/12/2009)
One trick is to hit the table twice: the first pass selects the 'buy' columns and aliases their names to simply Curr and Amt, and the second pass select the 'sell' columns also aliases their names to simply Curr and Amt.UNION ALL the two SELECT statements together, and you have a set ready to be grouped and summed in a single statement.
-Eddie
Excellent, thanks Eddie, I knew there must be a better way (and also that I did not know what it was)!
So this removes the explicit need for a temp table, is there any way of using a single "where" clause for both branches of the union (replacing WHERE ALotOfConvolutedLogic below)?
Jeremy
SELECT Curr, SUM(amt)
FROM ( SELECT BuyCurr AS Curr, BuyAmt AS Amt
FROM #myTrade
WHERE ALotOfConvolutedLogic
UNION ALL
SELECT SellCurr AS Curr, SellAmt AS Amt
FROM #myTrade
WHERE ALotOfConvolutedLogic
) AS TradeVals
GROUP BY Curr
November 13, 2009 at 4:16 am
Do you have any chance to modify the table structure?
It would be a lot easier to query if you'd have a table with
ID, DateValue, Curr, and Amt.
To differentiate between Buy and Sell you could either use the Amt column (>=0 or < 0) or you could add a Flag column Type char(1).
How do you deal cases where you have more than one Buy or Sale currency per day?
Like
Buy
'Jul 6 2009','GBP',100000
'Jul 6 2009','USD',100000
Sell
'Jul 6 2009','USD', -160000
'Jul 6 2009','GBP', -110000
'Jul 6 2009','EUR',10000
November 16, 2009 at 4:41 am
select SUM (e.t) ,curr
from (select BuyCurr as curr, BuyAmt as t from #myTrade UNION all select sellCurr as curr, sellAmt as t from #myTrade ) as e
GROUP BY Curr
i think that may solve the craetion of temparary table problem
please if it is not usefull tell my why
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply