using MAX - 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 2005 Forums

Transact-SQL (2005)

using MAX

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-07-13 : 08:55:36

I have a currency rate table with the following columns
t_stdt = startdate for this type of conversion
t_tcur = To Currency
t_fcur = From Currency
t_rate = Actual rate.

The table keeps all existing rates. So when a new rate is added for example EUR to GBP then a new row is created for this particular type of conversion and old ones are also still kept in the table.
So I want to pul out the max startdate for each type (ps am only dealing with four types EUR to GBP, GBP to EUR, USD to GBP and GBP to USD.
example data at bottom of page

My query seems to work and it pulls out only four rows (which is what I want), but i wanted to check I am doing it right.
here's the query:

-----------------------------------------
Select
t_stdt as "StartDate",
t_tcur as "ToCurrency",
t_fcur as "FromCurrency",
t_rate as "Rate"
From ttcmcs903530
where t_crty = '7'
and t_stdt in
(Select
MAX(t_stdt) as "StartDate"
From ttcmcs903530
where t_crty = '7'
group by t_tcur, t_crty, t_fcur)

------------------------------------------

2006-11-23 00:00:00.000 EUR GBP 0.689993
2007-01-12 00:00:00.000 EUR GBP 0.67
2006-11-23 00:00:00.000 GBP EUR 1.44929
2007-01-12 00:00:00.000 GBP EUR 1.492537
2007-01-19 00:00:00.000 GBP USD 0.526316
2007-01-19 00:00:00.000 USD GBP 1.9
2007-10-05 00:00:00.000 EUR GBP 1.47
2007-10-05 00:00:00.000 GBP EUR 0.680272
2008-03-11 00:00:00.000 EUR GBP 1.4563
2008-03-11 00:00:00.000 GBP EUR 0.686672
2008-10-07 00:00:00.000 GBP USD 0.561136
2008-10-07 00:00:00.000 USD GBP 1.7821
2008-11-04 00:00:00.000 USD GBP 1.57915
2008-11-04 00:00:00.000 GBP USD 0.633252
2008-12-01 00:00:00.000 USD GBP 1.50522
2008-12-01 00:00:00.000 GBP USD 0.664355
2009-02-03 00:00:00.000 EUR GBP 1.2
2009-02-03 00:00:00.000 GBP EUR 0.833333
2009-02-03 00:00:00.000 USD GBP 1.4416
2009-02-03 00:00:00.000 GBP USD 0.693674
2009-03-02 00:00:00.000 USD GBP 1.4315
2009-03-02 00:00:00.000 GBP USD 0.698568
2010-01-01 00:00:00.000 EUR GBP 1.1115
2010-01-01 00:00:00.000 GBP EUR 0.899685
2010-01-01 00:00:00.000 USD GBP 1.6
2010-01-01 00:00:00.000 GBP USD 0.625
2009-02-04 00:00:00.000 EUR GBP 1.105
2009-02-04 00:00:00.000 GBP EUR 0.904977

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-13 : 09:12:48

quote:
My query seems to work and it pulls out only four rows (which is what I want), but i wanted to check I am doing it right.

try adding another line like and see how your query behave

2010-01-02 00:00:00.000 USD GBP 1.7

you should find the max of t_stdt by t_tcur, t_crty, t_fcur and then INNER JOIN back to the original table


Select	t.t_stdt as [StartDate],
	t.t_tcur as [ToCurrency],
	t.t_fcur as [FromCurrency],
	t.t_rate as [Rate]
From 	ttcmcs903530 t
	inner join
	(
		Select	t_crty, t_tcur, t_fcur, MAX(t_stdt) as [StartDate]
		From 	ttcmcs903530
		group by t_tcur, t_crty, t_fcur
	) m	on	t.t_crty	= m.t_crty
		and	t.t_stdt	= m.[StartDate]
		and	t.t_tcur	= m.t_tcur
		and	t.t_fcur	= m.t_fcur

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-13 : 09:15:00

since you are using SQL 2005, it will be simple just to use the row_number() with over()


; with data
as
(
	Select	t.t_stdt as [StartDate],
		t.t_tcur as [ToCurrency],
		t.t_fcur as [FromCurrency],
		t.t_rate as [Rate],
		row_no	= row_number() over (partition by t_crty, t_tcur, t_fcur order by t_stdt desc)
	From 	ttcmcs903530 t
)
select	*
from	data
where	row_no	= 1

KH
[spoiler]Time is always against us[/spoiler]

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-07-13 : 09:20:09

Thats really helpful
thanks

Kristen
Test

22859 Posts

Posted - 2010-07-13 : 09:24:23

If you are going to do that for a single currency (i.e. "What's the current exchange rate for Currency=X" then I think its fine.

If you are going to do multiple rows (e.g. "What was the current exchange rate for this Order, given the Order's Currency field AND a field for the date/time of the Order") then I think it will be inefficient, and you should perhaps look at ROW_NUMBER() ... OVER syntax instead.

ROW_NUMBER() ... OVER would also have the benefit (for the single-row query above) of only returning ONE result row in the event that there were two, or more, rows in your [ttcmcs903530] currency rate table with identical date/time values (but you may already be preventing that with Primary Key or Unique constraint/index)

Kristen
Test

22859 Posts

Posted - 2010-07-13 : 09:26:11

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources