SQL self join question
-
I have a table that has currency exchange rates as below CCY EX_RATE RATE_DT ---- ------- ------- AED 1.1 7/31/06 AED 1.0 6/30/06 AED 1.5 5/31/06 What that means is that the exchange rate was 1.5 from 5/31 to 6/30 and is at 7/31 till eternity. What I want is the output to look like this CCY EX_RATE START_DT END_DT ---- ------- -------- ------ AED 1.1 7/31/06 (NULL) AED 1.0 6/30/06 7/31/06 AED 1.5 5/31/06 6/30/06 I think an self-join will give me the correct data but i am getting excessive rows when i do a join.
-
I have a table that has currency exchange rates as below CCY EX_RATE RATE_DT ---- ------- ------- AED 1.1 7/31/06 AED 1.0 6/30/06 AED 1.5 5/31/06 What that means is that the exchange rate was 1.5 from 5/31 to 6/30 and is at 7/31 till eternity. What I want is the output to look like this CCY EX_RATE START_DT END_DT ---- ------- -------- ------ AED 1.1 7/31/06 (NULL) AED 1.0 6/30/06 7/31/06 AED 1.5 5/31/06 6/30/06 I think an self-join will give me the correct data but i am getting excessive rows when i do a join.
Asad, This should work.
Select CCY, EX_RATE, MIN(RATE_DT) as START_DT, MAX(RATE_DT) as END_DT From your_currency_table Group by CCY, EX_RATE
Farhan Noor Qureshi
-
Asad, This should work.
Select CCY, EX_RATE, MIN(RATE_DT) as START_DT, MAX(RATE_DT) as END_DT From your_currency_table Group by CCY, EX_RATE
Farhan Noor Qureshi
Thanks for your reply but that doesnt work. If I have this data
CCY EX_RATE RATE_DT
AED 1.1 7/31/06
AED 1.0 6/30/06
AED 1.5 5/31/06It will return something like
CCY EX_RATE START_DT END_DT
AED 1.1 7/31/06 7/31/06
AED 1.0 6/30/06 6/30/06
AED 1.5 5/31/06 5/31/06I want it to give me ranges like
CCY EX_RATE START_DT END_DT
AED 1.1 7/31/06 (NULL)
AED 1.0 6/30/06 7/31/06
AED 1.5 5/31/06 6/30/06 -
I have a table that has currency exchange rates as below CCY EX_RATE RATE_DT ---- ------- ------- AED 1.1 7/31/06 AED 1.0 6/30/06 AED 1.5 5/31/06 What that means is that the exchange rate was 1.5 from 5/31 to 6/30 and is at 7/31 till eternity. What I want is the output to look like this CCY EX_RATE START_DT END_DT ---- ------- -------- ------ AED 1.1 7/31/06 (NULL) AED 1.0 6/30/06 7/31/06 AED 1.5 5/31/06 6/30/06 I think an self-join will give me the correct data but i am getting excessive rows when i do a join.
select ccy,ex_rate,rate_dt as start_dt,(select top 1 cur2.rate_dt from tblcurrencyrate cur2 where cur.ccy = cur2.ccy and cur2.rate_dt > cur.rate_dt order by cur2.rate_dt) as end_dt from tblcurrencyrate cur--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters