How do I create a common column key or string for currency bought and sold dynamically to cater for all opposite currencies. E.g. USD/GBP and GBP/USD

Question

1.00/5 (2 votes)

See more:

I would like to create a common column whereby I can later on group bought and sold currency in a pair.

If USD is traded to GBP, then GBP is traded for USD. They are a pair regardless of the date
If EURO is traded to USD, then USD is traded for EURO. Then this is another pair.
And so forth with all the other currencies, but other currencies are always paired with USD

Sample data below

SQL

CREATE TABLE FX_Buy_Sell
(
	--Common_key VARCHAR(12) NULL, 
	Tradedate DATE NULL, 
	Symbol_from VARCHAR(4) NULL, 
	SourceAmount DECIMAL(18,2) NULL,
	Symbol_to VARCHAR(4) NULL,
	TradeAmount DECIMAL(18,2) NULL,
);


INSERT INTO FX_Buy_Sell (Tradedate,Symbol_from,SourceAmount,Symbol_to,TradeAmount)
VALUES
('2019-01-29','USD',39394.8,'GBP',31000),
('2019-01-29','USD',27914.34,'GBP',21700),
('2019-01-29','USD',77342.97,'GBP',59300),
('2019-03-29','USD',81908.2,'GBP',620000),
('2019-05-23','USD',101776,'GBP',80000),
('2019-07-25','USD',105706,'GBP',85000),
('2019-09-06','USD',127459.49,'GBP',106000),
('2019-11-08','USD',158937.77,'GBP',123000),
('2019-12-09','USD',90213.62,'GBP',70000),
('2019-12-09','USD',76256.43,'GBP',58000),
('2019-01-29','GBP',112000,'USD',143262),
('2019-03-29','GBP',62000,'USD',81102.2),
('2019-05-23','GBP',80000,'USD',105976),
('2019-07-25','GBP',85000,'USD',108483.38),
('2019-09-06','GBP',106000,'USD',132079.18),
('2019-11-08','GBP',123000,'USD',148276.5),
('2019-12-09','GBP',128000,'USD',165548.8),
('2019-01-29','EURO',17000,'USD',19322.2),
('2019-09-06','EURO',19000,'USD',21289.5),
('2019-11-08','EURO',10000,'USD',10996),
('2019-01-29','USD',19532.83,'EURO',17000),
('2019-09-06','USD',20793.24,'EURO',19000),
('2019-11-08','USD',11163.42,'EURO',10000);

Expected results, I need a column with some sort of common key or string that I can use to group the currency pairs(bought and sold) with:

SQL

select * from
(
SELECT 
Rank() OVER (PARTITION BY fb.Symbol_from, fbs.Symbol_to   ORDER BY fbs.Symbol_to,fb.Symbol_to, fbs.Symbol_from ) as COMMON_STRING_KEY,
IIF(fb.Symbol_from = fbs.Symbol_to, fbs.Symbol_to + fb.Symbol_from +fb.Symbol_to + fbs.Symbol_from, 'Null') as COMMON_STRING_KEY2,
fb.* 
FROM FX_Buy_Sell fb
left outer  JOIN FX_Buy_Sell fbs
ON fb.Symbol_from = fbs.Symbol_to
) a
where a.COMMON_STRING_KEY = 1

HTML

   table, th, td {
   border: 1px solid black;
   }

<h1>Desired outcome</h1>
   <table><thead><tr>               <th>Common_String_Key</th>               <th>Tradedate</th>               <th>Symbol_From</th>               <th>SourceAmount</th>               <th>DestSymbol_To</th>               <th>TradeAmount</th>            </tr></thead>         <tbody><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-01-29</td>               <td>USD</td>               <td>-39394.8</td>               <td>GBP</td>               <td>31000</td>            </tr><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-01-29</td>               <td>USD</td>               <td>-27914.34</td>               <td>GBP</td>               <td>21700</td>            </tr><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-01-29</td>               <td>USD</td>               <td>-77342.97</td>               <td>GBP</td>               <td>59300</td>            </tr><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-03-29</td>               <td>USD</td>               <td>-81908.2</td>               <td>GBP</td>               <td>62000</td>            </tr><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-05-23</td>               <td>USD</td>               <td>-101776</td>               <td>GBP</td>               <td>80000</td>            </tr><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-07-25</td>               <td>USD</td>               <td>-105706</td>               <td>GBP</td>               <td>85000</td>            </tr><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-09-06</td>               <td>USD</td>               <td>-127459.49</td>               <td>GBP</td>               <td>106000</td>            </tr><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-11-08</td>               <td>USD</td>               <td>-158937.77</td>               <td>GBP</td>               <td>123000</td>            </tr><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-12-09</td>               <td>USD</td>               <td>-90213.62</td>               <td>GBP</td>               <td>70000</td>            </tr><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-12-09</td>               <td>USD</td>               <td>-76256.43</td>               <td>GBP</td>               <td>58000</td>            </tr><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-01-29</td>               <td>GBP</td>               <td>-112000</td>               <td>USD</td>               <td>143262</td>            </tr><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-03-29</td>               <td>GBP</td>               <td>-62000</td>               <td>USD</td>               <td>81102.2</td>            </tr><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-05-23</td>               <td>GBP</td>               <td>-80000</td>               <td>USD</td>               <td>105976</td>            </tr><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-07-25</td>               <td>GBP</td>               <td>-85000</td>               <td>USD</td>               <td>108483.38</td>            </tr><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-09-06</td>               <td>GBP</td>               <td>-106000</td>               <td>USD</td>               <td>132079.18</td>            </tr><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-11-08</td>               <td>GBP</td>               <td>-123000</td>               <td>USD</td>               <td>148276.5</td>            </tr><tr>               <td>USDGBP_BGPUSD</td>               <td>2019-12-09</td>               <td>GBP</td>               <td>-39394.8</td>               <td>USD</td>               <td>165548.8</td>            </tr><tr>               <td>USDEURO_EURO_USD</td>               <td>2019-01-29</td>               <td>EURO</td>               <td>-17000</td>               <td>USD</td>               <td>19322.2</td>            </tr><tr>               <td>USDEURO_EURO_USD</td>               <td>2019-09-06</td>               <td>EURO</td>               <td>-19000</td>               <td>USD</td>               <td>21289.5</td>            </tr><tr>               <td>USDEURO_EURO_USD</td>               <td>2019-11-08</td>               <td>EURO</td>               <td>-10000</td>               <td>USD</td>               <td>10996</td>            </tr><tr>               <td>USDEURO_EURO_USD</td>               <td>2019-01-29</td>               <td>USD</td>               <td>-19532.83</td>               <td>EURO</td>               <td>17000</td>            </tr><tr>               <td>USDEURO_EURO_USD</td>               <td>2019-09-06</td>               <td>USD</td>               <td>-20793.24</td>               <td>EURO</td>               <td>19000</td>            </tr><tr>               <td>USDEURO_EURO_USD</td>               <td>2019-11-08</td>               <td>USD</td>               <td>-11163.42</td>               <td>EURO</td>               <td>10000</td>            </tr></tbody>      </table>

What I have tried:

SQL

select * from
(
SELECT 
Rank() OVER (PARTITION BY fb.Symbol_from, fbs.Symbol_to   ORDER BY fbs.Symbol_to,fb.Symbol_to, fbs.Symbol_from ) as COMMON_STRING_KEY,
IIF(fb.Symbol_from = fbs.Symbol_to, fbs.Symbol_to + fb.Symbol_from +fb.Symbol_to + fbs.Symbol_from, 'Null') as COMMON_STRING_KEY2,
fb.* 
FROM FX_Buy_Sell fb
left outer  JOIN FX_Buy_Sell fbs
ON fb.Symbol_from = fbs.Symbol_to
) a
where a.COMMON_STRING_KEY = 1

Posted 18-Nov-21 1:40am

Craig Scoble

Updated 18-Nov-21 5:00am

Richard MacCutchan

v4

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2021-11-18T05:00:00

Try using UNPIVOT (Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^]) and FOR XML (FOR XML (SQL Server) - SQL Server | Microsoft Docs[^])

Approach - get a list of symbol_from and symbol_to for each row in alphabetic order, then combine those values into a single value

SQL

;with cte1 as 
(
	SELECT id, code
	FROM   
	   (SELECT id, symbol_from, symbol_to
	   FROM #FX_Buy_Sell) p  
	UNPIVOT  
	   (code FOR symbol IN   
		  (symbol_from, symbol_to)  
	)AS unpvt
),cte2 as
(
	select id, stuff((SELECT ' ' + code from cte1 b where a.id = b.id ORDER BY code
		for XML PATH('')),1,1,'') as combo from cte1 a
	GROUP BY id
)
update a set Common_key = b.combo
from #FX_Buy_Sell a
inner join cte2 b on a.id = b.id

NOTE: to get this to work I had to add a unique id to each row

SQL

CREATE TABLE #FX_Buy_Sell
(
	id int identity(1,1),
	Tradedate DATE NULL, 
	Symbol_from VARCHAR(4) NULL, 
	SourceAmount DECIMAL(18,2) NULL,
	Symbol_to VARCHAR(4) NULL,
	TradeAmount DECIMAL(18,2) NULL,
	Common_key VARCHAR(12) NULL
);