How to handle Currency Rate Exceptions
-
Hello Everyone!
It is given the following table (DataTable):
UniqueKeyColumn1 UniqueKeyColumn2 UniqueKeyColumn3 ValueInOneCurrency
The UniqueKeyColumns are defining a unique record containing a value in Currency.
This ValueInOneCurrency has to be converted to a different ValueInOtherCurrency.
This is a standard task.
The twist in the task is, that there are some exceptions when the conversion should be done at different rate than the Default rate.
Examples of exception:
- for a given value of UniqueKeyColumn1, the rate should be different than for the rest
- for a given value of UniqueKeyColumn2, the rate should be different
- for a given value of UniqueKeyColumn3, the rate should be different
- for a given combination of values from UniqueKeyColumn1 and UniqueKeyColumn2 the rate should be different
- for a given combination of values from UniqueKeyColumn2 and UniqueKeyColumn3 the rate should be different
- and so on.
So there should be a possibility of giving a default conversion rate, but there should be also a possibility of defining the exceptions, which could be any of the combinations of the 3 UniqueKeyColumns.
So for this, I defined a second table (ConversionTable):
UniqueKeyColumn1 UniqueKeyColumn2 UniqueKeyColumn3 ConversionRate
And values from this table:
Default Default Default 270
ExceptionColumn11 Default Default 250
Default ExceptionColum21 Default 240
Default Default ExceptionColumn31 213
ExceptionColumn12 ExceptionColum22 Default 115
Default ExceptionColum23 ExceptionColumn32 150
And so on…
Question:
How can I build one select statement having the following output:
UniqueKeyColumn1 UniqueKeyColumn2 UniqueKeyColumn3 ValueInOneCurrency ConversionRate
which should contain the default ConversionRate for all records, except for those where the exception cases from the ConversionTable are met. Where the exception case is met, the different ConversionRate should be selected.
Any ideas or different approaches to the problem are welcome.
PS: Actually there are more UniqueKeyColumns than 3, just for the simplicity I used 3.
🙂 -
If I'm following you correctly, there are two basic options off the top of my head. You can write a big ol' CASE statement inline, to handle each of the exception combinations (just how many UniqueKeyColumns are we talking about?), or alternatively, you can make that a separate step, building a table with the final conversion rate, and then joining back to that instead.
Either way will require the CASE logic, but one separates it out into a different step. I'd definitely go with the latter if the final conversion rate is used in multiple places.
PS. How married are you to your current data model? While we'd need a lot more info, there might be a better way to handle this up front.
-
What about doing the following:
TABLE1 (UniqueKeyColumn1, UniqueKeyColumn2, UniqueKeyColumn3, ValueInOneCurrency)
TABLE2 (UniqueKeyColumn1, UniqueKeyColumn2, UniqueKeyColumn3, ConversionRate)
SELECT TABLE1.ValueInOneCurrency * TABLE2.ConversionRate AS Final_Value
FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.UniqueKeyColumn1 = TABLE2.UniqueKeyColumn1 AND TABLE1.UniqueKeyColumn2 = TABLE2.UniqueKeyColumn2 AND TABLE1.UniqueKeyColumn3 = TABLE2.UniqueKeyColumn3
I'm assuming the UniqueKey field values are not something that can be multiplied/added together via some sort of formula, and that there are a finite number of values allowed for each field. If the number of values allowed for the UniqueKey fields are not limited, then this won't work.
-
David!
The first solution is out of the question
It is too static solution. If one of the combinations changes, I have to change the (hardcoded) code. I don't want that I didn't understand clearly your second proposition. If you could give me an example, that would help.
I am pretty married to my current structure, but this task is important, so if you have any usable different aproach, than I am opened to change it. I am opened to any solution. Even if it seems dummy or out of the ordinary or whatever
You know: sometimes the most stupid ideas give us the best and simpliest solutions
Joshua!
As you said: the number of values allowed for the UniqueKey fields are not limited, so your solution won't work.
Still, thank you for the effort.So, the thread is still opened. I am waiting for other ideas (from David and Joshua, too
)
🙂 -
I think some sample data and expected output would be nice.
N 56°04'39.16"
E 12°55'05.25" -
I'm with Peter, and I'd like to see the "unsimplified" table structure in the samples. It would also help if you'd give us an example of what might change that would cause a CASE based solution to require changes.
-
Here is the sample data.
In the DesiredOutput you find with bright green background the records where the Exceptions have been aplied. In all other records, the Default ConversionRate has been applied.
DataTable UniqueKeyColumn1 UniqueKeyColumn2 UniqueKeyColumn3 ValueInOneCurrency Col1_Data1 Col2_Data1 Col3_Data1 10 Col1_Data1 Col2_Data2 Col3_Data2 100 ExceptionColumn11 Col2_Data3 Col3_Data3 20 Col1_Data4 Col2_Data4 Col3_Data4 200 Col1_Data5 ExceptionColumn21 Col3_Data5 30 ExceptionColumn11 ExceptionColumn21 Col3_Data6 300 Col1_Data7 Col2_Data7 Col3_Data7 40 ExceptionColumn12 ExceptionColumn22 Col3_Data8 400 Col1_Data9 Col2_Data9 Col3_Data9 50 Col1_Data10 ExceptionColumn23 Col3_Data10 500 ConversionRateTable UniqueKeyColumn1 UniqueKeyColumn2 UniqueKeyColumn3 ConversionRate Default Default Default 270 ExceptionColumn11 Default Default 250 Default ExceptionColumn21 Default 240 Default Default ExceptionColumn31 213 ExceptionColumn12 ExceptionColumn22 Default 115 Default ExceptionColumn23 ExceptionColumn32 150 DesiredOutput UniqueKeyColumn1 UniqueKeyColumn2 UniqueKeyColumn3 ValueInOneCurrency ConversionRate ValueInOtherCurrency Col1_Data1 Col2_Data1 Col3_Data1 10 270 2 700 Col1_Data1 Col2_Data2 Col3_Data2 100 270 27 000 ExceptionColumn11 Col2_Data3 Col3_Data3 20 250 5 000 Col1_Data4 Col2_Data4 Col3_Data4 200 270 54 000 Col1_Data5 ExceptionColumn21 Col3_Data5 30 240 7 200 ExceptionColumn11 ExceptionColumn21 Col3_Data6 300 270 81 000 Col1_Data7 Col2_Data7 Col3_Data7 40 270 10 800 ExceptionColumn12 ExceptionColumn22 Col3_Data8 400 115 46 000 Col1_Data9 Col2_Data9 Col3_Data9 50 270 13 500 Col1_Data10 ExceptionColumn23 Col3_Data10 500 270 135 000
🙂 -
This will get you an start
-- Prepare sample data
declare
@DataTable table (UniqueKeyColumn1 varchar(20), UniqueKeyColumn2 varchar(20), UniqueKeyColumn3 varchar(20), ValueInOneCurrency int)
insert
@datatable
select
'Col1_Data1', 'Col2_Data1', 'Col3_Data1', 10 union all
select
'Col1_Data1', 'Col2_Data2', 'Col3_Data2', 100 union all
select
'ExceptionColumn11', 'Col2_Data3', 'Col3_Data3', 20 union all
select
'Col1_Data4', 'Col2_Data4', 'Col3_Data4', 200 union all
select
'Col1_Data5', 'ExceptionColumn21', 'Col3_Data5', 30 union all
select
'ExceptionColumn11', 'ExceptionColumn21', 'Col3_Data6', 300 union all
select
'Col1_Data7', 'Col2_Data7', 'Col3_Data7', 40 union all
select
'ExceptionColumn12', 'ExceptionColumn22', 'Col3_Data8', 400 union all
select
'Col1_Data9', 'Col2_Data9', 'Col3_Data9', 50 union all
select
'Col1_Data10', 'ExceptionColumn23', 'Col3_Data10', 500
declare
@ConversionRateTable table (UniqueKeyColumn1 varchar(20), UniqueKeyColumn2 varchar(20), UniqueKeyColumn3 varchar(20), ConversionRate int)
insert
@ConversionRateTable
select
'Default', 'Default', 'Default', 270 union all
select
'ExceptionColumn11', 'Default', 'Default', 250 union all
select
'Default', 'ExceptionColumn21', 'Default', 240 union all
select
'Default', 'Default', 'ExceptionColumn31', 213 union all
select
'ExceptionColumn12', 'ExceptionColumn22', 'Default', 115 union all
select
'Default', 'ExceptionColumn23', 'ExceptionColumn32', 150
-- Show the expected output
SELECT
d.UniqueKeyColumn1,
d
.UniqueKeyColumn2,
d
.UniqueKeyColumn3,
d
.ValueInOneCurrency,
cr
.ConversionRate,
d
.ValueInOneCurrency * cr.ConversionRate AS ValueInOtherCurrency
FROM
@DataTable AS d
LEFT
JOIN @ConversionRateTable AS cr ON COALESCE(NULLIF(cr.UniqueKeyColumn1, 'Default'), d.UniqueKeyColumn1) = d.UniqueKeyColumn1
AND COALESCE(NULLIF(cr.UniqueKeyColumn2, 'Default'), d.UniqueKeyColumn2) = d.UniqueKeyColumn2
AND COALESCE(NULLIF(cr.UniqueKeyColumn3, 'Default'), d.UniqueKeyColumn3) = d.UniqueKeyColumn3
ORDER
BY LEN(d.UniqueKeyColumn3),
d
.UniqueKeyColumn3
N 56°04'39.16"
E 12°55'05.25" -
Based on your more complete description of the situation, this should work:
DATATABLE (UniqueKeyColumn1, UniqueKeyColumn2, UniqueKeyColumn3, ValueInOneCurrency)
EXCEPTIONTABLE (UniqueKeyColumn1, UniqueKeyColumn2, UniqueKeyColumn3, ConversionRate)
SELECT DATATABLE.ValueInOneCurrency * ISNULL(EXCEPTIONTABLE.ConversionRate, DefaultRate) AS Final_Value
FROM DATATABLE LEFT OUTER JOIN EXCEPTIONTABLE ON DATATABLE.UniqueKeyColumn1 = EXCEPTIONTABLE.UniqueKeyColumn1 AND DATATABLE.UniqueKeyColumn2 = EXCEPTIONTABLE.UniqueKeyColumn2 AND DATATABLE.UniqueKeyColumn3 = EXCEPTIONTABLE.UniqueKeyColumn3
This does assume a finite number of exceptions, although it would allow for unlimited variations in unique keys, and seems to also stay close to your existing data model.
-
Now everybody howl and kneel before Peter Larsson!
Peter, this is very close to what I was looking for. Thank you.
I just have to come up with a way to remove the extra combinations to get the desired output. Do you have a fast solution for this?
Botond
🙂 -
It depends on your business rules of which records to remove.
N 56°04'39.16"
E 12°55'05.25" -
Yes, you are right.
This is why I provided a desired output. Anyway, I did it differently, using multiple, nested joins. You can find my solution below.
But your solution helped me aproaching this problem in a different angle.
If you have suggestions for optimizing/simplifying the below select list, please let me know. Otherwise, I consider this thread closed.
-- select the final DesiredOutput
SELECT
linksTableLevel2.UniqueKeyColumn1,
linksTableLevel2.UniqueKeyColumn2,
linksTableLevel2.UniqueKeyColumn3,
linksTableLevel2.ValueInOneCurrency,
crLevel2.ConversionRate,
ValueInOtherCurrency = linksTableLevel2.ValueInOneCurrency * crLevel2.ConversionRate
FROM
(
-- for combinations of Exceptions
-- that are not in the ConversionRateTable
-- replace the LinkUniqueKeyColumn's value with 'Default'
SELECT
linksTable.UniqueKeyColumn1,
LinkUniqueKeyColumn1 = ISNULL(cr.UniqueKeyColumn1, 'Default'),
linksTable.UniqueKeyColumn2,
LinkUniqueKeyColumn2 = ISNULL(cr.UniqueKeyColumn2, 'Default'),
linksTable.UniqueKeyColumn3,
LinkUniqueKeyColumn3 = ISNULL(cr.UniqueKeyColumn3, 'Default'),
linksTable.ValueInOneCurrency
FROM
(
-- attach for each column in each record in the DataTable
-- the 'Default' value
-- if the value is not present in the Exceptions list
SELECT
d.UniqueKeyColumn1,
LinkUniqueKeyColumn1 = ISNULL(cr1.UniqueKeyColumn1, 'Default'),
d.UniqueKeyColumn2,
LinkUniqueKeyColumn2 = ISNULL(cr2.UniqueKeyColumn2, 'Default'),
d.UniqueKeyColumn3,
LinkUniqueKeyColumn3 = ISNULL(cr3.UniqueKeyColumn3, 'Default'),
d.ValueInOneCurrency
FROM @DataTable AS d
LEFT JOIN
(SELECT DISTINCT UniqueKeyColumn1 FROM @ConversionRateTable) AS cr1
ON d.UniqueKeyColumn1 = cr1.UniqueKeyColumn1
LEFT JOIN
(SELECT DISTINCT UniqueKeyColumn2 FROM @ConversionRateTable) AS cr2
ON d.UniqueKeyColumn2 = cr2.UniqueKeyColumn2
LEFT JOIN
(SELECT DISTINCT UniqueKeyColumn3 FROM @ConversionRateTable) AS cr3
ON d.UniqueKeyColumn3 = cr3.UniqueKeyColumn3
) linksTable
LEFT JOIN @ConversionRateTable cr
ON
linksTable.LinkUniqueKeyColumn1 = cr.UniqueKeyColumn1
AND linksTable.LinkUniqueKeyColumn2 = cr.UniqueKeyColumn2
AND linksTable.LinkUniqueKeyColumn3 = cr.UniqueKeyColumn3
) linksTableLevel2
INNER JOIN @ConversionRateTable crLevel2
ON
linksTableLevel2.LinkUniqueKeyColumn1 = crLevel2.UniqueKeyColumn1
AND linksTableLevel2.LinkUniqueKeyColumn2 = crLevel2.UniqueKeyColumn2
AND linksTableLevel2.LinkUniqueKeyColumn3 = crLevel2.UniqueKeyColumn3
🙂 -
Yes you did provide expected output, but you did not tell what the rules are!
I couldn't see a simple way to figure it out.
Please tell which duplicates to remove and which to keep...
N 56°04'39.16"
E 12°55'05.25"
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply