Post reply

Straight & Reverse Check - kcehc eserveR & Straight

  • December 4, 2012 at 3:59 am

    #265730

    All,

    I have two table as below.

    create table currency

    (

    currencypair varchar(30) not null,

    rate_value numeric(18,6) null

    )

    insert into currency

    select 'BNGCZK',null

    union

    select 'CHFCLP',null

    union

    select 'CHFLKR',null

    union

    select 'CHFPEN',null

    union

    select 'CHFSIT',null

    union

    select 'CHFZMK',null

    union

    select 'CZKSIT',null

    union

    select 'DKKSIT',null

    union

    select 'EURAED',1.666000000

    union

    select 'EURBGN',1.9556000000

    union

    select 'EURBHD',1.4819000000

    union

    select 'EURBRL',1.3569000000

    union

    select 'EURCAD',1.3033000000

    union

    select 'EURCHF',1.2179300000

    union

    select 'EURCLP',null

    union

    select 'EURCZK',1.8650000000

    union

    select 'EURDKK',1.4365000000

    union

    select 'EUREEK',1.6426000000

    union

    select 'EUREGP',1.7137600000

    union

    select 'EURGBP',1.8253000000

    union

    select 'EURHKD',1.9230000000

    union

    select 'EURHRK',1.5305000000

    union

    select 'EURHUF',1.0000000000

    create table off_currency_pair

    (

    ccy1 varchar(10),

    ccy2 varchar(10),

    rate_value numeric(18,6) null

    )

    insert into off_currency_pair

    select 'BNG','CZK',1.0

    union all

    select 'CHF','CLP',1.0

    union all

    select 'CHF','LKR',1.0

    union all

    select 'CHF','PEN',1.0

    union all

    select 'CHF','SIT',1.0

    union all

    select 'CHF','ZMK',1.0

    union all

    select 'AED','AED',1.0

    union all

    select 'BGN','EUR',1.0

    union all

    select 'BHD','EUR',1.0

    union all

    select 'EUR','BRL',1.0

    union all

    select 'CAD','EUR',1.0

    union all

    select 'EUR','CHF',1.2

    union all

    select 'EUR','CLP',null

    union all

    select 'EUR','CZK',1.0

    union all

    select 'EUR','DKK',1.0

    #1)correct matching

    currency.currencypair = off_currency_pair.ccy1 & off_currency_pair.ccy2

    #2) Reverse matching

    currency.currencypair = off_currency_pair.ccy2 & off_currency_pair.ccy1

    My Query:

    select currencypair, ccy1, ccy2

    from currency_test , off_currency_pair

    where currency_test.currencypair = off_currency_pair.ccy1 + off_currency_pair.ccy2

    Output1:

    currencypairccy1ccy2

    BNGCZKBNGCZK

    CHFCLPCHFCLP

    CHFLKRCHFLKR

    CHFPENCHFPEN

    CHFSITCHFSIT

    CHFZMKCHFZMK

    EURBRLEURBRL

    EURCHFEURCHF

    EURCLPEURCLP

    EURCZKEURCZK

    EURDKKEURDKK

    #2)

    select currencypair, ccy1, ccy2

    from currency_test , off_currency_pair

    where currency_test.currencypair = off_currency_pair.ccy2 + off_currency_pair.ccy1

    currencypairccy1ccy2

    EURBGNBGNEUR

    EURBHDBHDEUR

    EURCADCADEUR

    I am looking for some alternate way (Interesting one! especially in a single query) to achieve the same.

    Inputs are welcome!

    karthik

  • December 4, 2012 at 4:15 am

    #1565210

    I don't want to use

    select currencypair, ccy1, ccy2

    from currency_test , off_currency_pair

    where (currency_test.currencypair = off_currency_pair.ccy2 + off_currency_pair.ccy1

    or currency_test.currencypair = off_currency_pair.ccy1 + off_currency_pair.ccy2)

    karthik

  • December 4, 2012 at 4:17 am

    #1565211

    I need a nice trick to do this 🙂

    karthik

  • December 4, 2012 at 4:18 am

    #1565213

    select currencypair, ccy1, ccy2

    from currency_test , off_currency_pair

    where currency_test.currencypair in (off_currency_pair.ccy2 + off_currency_pair.ccy1, off_currency_pair.ccy1 + off_currency_pair.ccy2)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • December 4, 2012 at 5:32 am

    #1565244

    great job on providing sample ddl and data!

    does this do what you are after?

    With CurrencySplit(currencypair,rate_value ,ccy1,ccy2)

    AS

    (

    SELECT

    currencypair,

    rate_value,

    LEFT(currencypair,3),

    RIGHT (currencypair,3)

    FROM currency

    )

    select

    CurrencySplit.*,

    T1.*

    from CurrencySplit

    INNER JOIN off_currency_pair T1

    ON CurrencySplit.ccy1 = T1.ccy1

    AND CurrencySplit.ccy2 = T1.ccy2

    UNION

    select

    CurrencySplit.*,

    T2.*

    from CurrencySplit

    INNER JOIN off_currency_pair T2

    ON CurrencySplit.ccy2 = T2.ccy1

    AND CurrencySplit.ccy1 = T2.ccy2

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 4, 2012 at 6:25 am

    #1565265

    Here's another

    SELECT currencypair,LEFT(currencypair,3) AS ccy1,RIGHT(currencypair,3) AS ccy2

    FROM currency

    INTERSECT

    (SELECT ccy1 + ccy2,ccy1,ccy2

    FROM off_currency_pair

    UNION

    SELECT ccy2 + ccy1,ccy2,ccy1

    FROM off_currency_pair);

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • December 4, 2012 at 6:42 am

    #1565285

    And another...but it does involve two tables scans, so I wouldn't recommend it...

    SELECT currencypair

    , ccy1

    , ccy2

    FROM

    currency

    , off_currency_pair

    WHERE charindex(ccy1, currencypair) * charindex(ccy2, currencypair) = 4

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • December 4, 2012 at 6:59 am

    #1565301

    Are you looking for interesting/clever, or effective?

    Honestly, all of the answers given so far are just different ways to tell the optimizer to do (essentially) the same thing. Of course, that's the whole point of a declarative language like SQL, where you say what end result you want and let the optimizer figure out how to do it.

    If all you want it alternative ways to write it, here's my attempt at "clever":

    ON ccy1 + ccy2 IN (currencypair, right(currencypair, 3) + left(currencypair, 3))

    And here's "efficient":

    ON currencypair in (ccy1 + ccy2, ccy2 + ccy1)

    The second one might actually allow an index seek on the join, instead of a full scan. You'd need to test with a lot more data than your provided sample. It doesn't look at "second halfs" of any strings, so it's all leading edges, if the columns are indexed. It's just the OR/IN construct that might (or might not) ruin SARGability.

    Note, this is the same as the first answer given, by Mark. There are only so many ways to skin this particular cat, without going into really silly possibilities. It's also the same as the one you said you don't want to use, it just has IN instead of OR. But IN is just shorthand for OR.

    The Union versions are also the same. Just more typing to get the same result.

    So, why the desire for a clever answer? And why the rejection of an effective one? Just curiousity, or some real need?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • December 4, 2012 at 10:48 pm

    #1565566

    Gsquard,

    So, why the desire for a clever answer? And why the rejection of an effective one? Just curiousity, or some real need?

    #1 --> Curiosity to get the diffrent answer

    As you said (There are only so many ways to skin this particular cat)

    #2 --> real need

    karthik

  • December 10, 2012 at 4:37 am

    #1566973

    The concatenated currencies iviolates of First Normal Form

    How to design the first table?

    Do I need to follow the same approach which you designed for both the tables?

    karthik

  • December 10, 2012 at 4:03 pm

    #1567193

    Yes, if you can, it would be much better to store from and to currencies as separate columns.

    But, either way, you don't need to store 2 conversion rates, only one, unless you allow non-standard rates that only apply one way. So, for example, always BNGCZK and never CZKBNG.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

    • Viewing 11 posts - 1 through 10 (of 10 total)

    You must be logged in to reply to this topic. Login to reply