Difficult Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Difficult Query

Author

Topic

JoshuaF
Starting Member

10 Posts

Posted - 2005-11-24 : 03:51:47

I need to select amounts from an 'invoices' table (ID, InvoiceDate, Currency {USD,ZAR}, Amount) joined to another table that stores a list of currency rates 'CurrencyRates' (ID, RateDate, ConversionRate) and where it is a USD Currency, to show the relevant ConversionRate for that record.

I tried this

Select case I.Currency when 'USD' then CR.ConversionRate end
from Invoices I
left join CurrencyRates CR on CR.RateDate < I.InvoiceDate

but that gives me duplication for x number of records where x = total number of CurrencyRates records that have a RateDate < the Invoices InvoiceDate

Then I tried this:

Select case I.Currency when 'USD' then CR.ConversionRate end
from Invoices I
left join (Select top 1 * from CurrencyRates order by RateDate desc) CR on CR.RateDate < I.InvoiceDate

That showed the correct number of results, but that only showed the latest CurrencyRates record. I couldn't get it to show the top 1 relevant to the InvoiceDate.

Does anyone have an easy solution?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-24 : 03:53:34

Can you post some sample data from two tables and the result you want?

Madhivanan

Failing to plan is Planning to fail

JoshuaF
Starting Member

10 Posts

Posted - 2005-11-24 : 04:17:36

CurrencyRates Table

ID | RateDate | Rate

50 | 2005-11-01 00:00:00.000 | 6.60000000
49 | 2005-10-01 00:00:00.000 | 6.40000000
47 | 2005-08-01 00:00:00.000 | 6.20000000
45 | 2005-07-01 00:00:00.000 | 6.60000000
43 | 2005-06-01 00:00:00.000 | 6.55000000
42 | 2005-05-01 00:00:00.000 | 6.10000000
34 | 2005-05-01 00:00:00.000 | 6.10000000
41 | 2005-04-01 00:00:00.000 | 6.00000000
40 | 2005-03-01 00:00:00.000 | 5.85000000
39 | 2005-02-01 00:00:00.000 | 6.00000000
38 | 2005-01-01 00:00:00.000 | 5.85000000

Invoices Table

ID | InvoiceDate | Currency | Amount

1 | 2005-07-01 15:36:00 | ZAR | 2940.00
2 | 2005-07-01 15:42:00 | ZAR | 20286.56
3 | 2005-07-01 15:57:00 | USD | 3700.00
4 | 2005-08-04 16:32:00 | ZAR | 20422.72
5 | 2005-08-05 09:13:00 | USD | 561.00
6 | 2005-09-10 09:04:00 | ZAR | 34176.46
7 | 2005-09-12 08:56:00 | ZAR | 42119.40
8 | 2005-09-12 09:54:00 | USD | 2400.00
9 | 2005-09-12 10:15:00 | USD | 2400.00
10 | 2005-09-12 10:23:00 | USD | 1780.00

I would like the result set to look like this:

1 | 2005-07-01 15:36:00 | ZAR | 2940.00
2 | 2005-07-01 15:42:00 | ZAR | 20286.56
3 | 2005-07-01 15:57:00 | USD | 3700.00 | 6.60000000
4 | 2005-08-04 16:32:00 | ZAR | 20422.72
5 | 2005-08-05 09:13:00 | USD | 561.00 | 6.20000000
6 | 2005-09-10 09:04:00 | ZAR | 34176.46
7 | 2005-09-12 08:56:00 | ZAR | 42119.40
8 | 2005-09-12 09:54:00 | USD | 2400.00 | 6.40000000
9 | 2005-09-12 10:15:00 | USD | 2400.00 | 6.40000000
10 | 2005-09-12 10:23:00 | USD | 1780.00 | 6.40000000

Can you do it?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-24 : 04:37:19

Is this OK ?

select [ID] , InvoiceDate , Currency , Amount,
	Rate = case when Currency = 'USD' then 
                         -- take the closest RateDate
			(select top 1 Rate from CurrencyRates x	
				order by abs(datediff(day, x.RateDate, i.InvoiceDate)))
		end
from	Invoices i

-----------------
[KH]

JoshuaF
Starting Member

10 Posts

Posted - 2005-11-24 : 05:51:03

That solved it totally!

Thanks for saving me another 3 hours of hair pulling.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources