Please advise me how to write the following T-SQL.

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

General SQL Server Forums

New to SQL Server Programming

Please advise me how to write the following T-SQL.

Author

Topic

Gold999
Starting Member

6 Posts

Posted - 2010-02-12 : 06:02:30

Dear All,

Please advise me how to write the following T-SQL query.

I have the table Investments:

Investments
-----------
Name Date Amount Currency

John 12/2/2010 00:15 1000.00 EURO
Linda 12/2/2010 01:30 1500.00 GBP
Jack 12/2/2010 02:30 1300.00 EURO

And I have the tables of Currencies rates:

Currencies
----------
Currency EffectiveDate RateInUSD

EURO 12/2/2010 00:00 1.24
EURO 12/2/2010 01:00 1.25
EURO 12/2/2010 02:00 1.26
GBP 12/2/2010 00:00 3.24
GBP 12/2/2010 01:00 2.25
GBP 12/2/2010 02:00 5.26

I need to write the T-SQL query to calculate amount of investments in USD depending on Rate at EffectiveDate.

The output will be:

Name, AmountInvestmentsInUsd

For example:

John 1240 (1.24*1000)
Linda 3375 (2.25*1500)
Jack 1638 (1.26*1300)

I've started to write the query, but it looks something wrong.

(not working)
select
Name,
Amount * RateInUSD
from
Investments i,
Currencies c
where
i.Currency = c.Currency
and
i.Date > (select
min(EffectiveDate)
from
Currencies
where
EffectiveDate < c.EffectiveDate)

Please advise.
Thanks in advance!

Sachin.Nand

2937 Posts

Posted - 2010-02-12 : 06:25:58

What if the effective rate changes more than once in a given hour?

PBUH

Gold999
Starting Member

6 Posts

Posted - 2010-02-12 : 06:33:59

quote:
Originally posted by Idera

What if the effective rate changes more than once in a given hour?

PBUH

Yes, it can happen.
But I think the "nature" of the T-SQL query will be the same I've provided. The point is I've missed something in join condition(s), as result the output is not correct. So, I need advise how to write the T-SQL query correct way.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 07:45:05

seems like this

if sql 2000


SELECT i.Name,i.Amount * (SELECT TOP 1 RateInUSD FROM Currencies WHERE Currency=i.Currency AND EffectiveDate < i.Date ORDER BY EffectiveDate DESC)
FROM Investments i

if sql 2005


SELECT i.Name,i.Amount * c.RateInUSD 
FROM Investments i
CROSS APPLY (SELECT TOP 1 RateInUSD FROM Currencies WHERE Currency=i.Currency AND EffectiveDate < i.Date ORDER BY EffectiveDate DESC)c

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Gold999
Starting Member

6 Posts

Posted - 2010-02-12 : 08:05:38

quote:
Originally posted by visakh16

seems like this

if sql 2000

SELECT i.Name,i.Amount * (SELECT TOP 1 RateInUSD FROM Currencies WHERE Currency=i.Currency AND EffectiveDate < i.Date ORDER BY EffectiveDate DESC)
FROM Investments i

if sql 2005

SELECT i.Name,i.Amount * c.RateInUSD
FROM Investments i
CROSS APPLY (SELECT TOP 1 RateInUSD FROM Currencies WHERE Currency=i.Currency AND EffectiveDate < i.Date ORDER BY EffectiveDate DESC)c

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks, it's very beautiful and good solutions.

If it possible to write something like this too (more ANSI-style I mean)?

select
Name,
Amount * RateInUSD,
c.EffectiveDate,
Date
from
Investments i,
Currencies c
where
i.Currency = c.Currency
and
i.Date > (select
max(EffectiveDate)
from
Currencies
where
EffectiveDate < c.EffectiveDate
and
Currency = c.Currency
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 08:10:00

[code]select
Name,
Amount * RateInUSD,
c.EffectiveDate,
Date
from
Investments i,
Currencies c
where
i.Currency = c.Currency
and
c.EffectiveDate = (select
max(EffectiveDate)
from
Currencies
where
Currency = c.Currency
and EffectiveDate < i.Date
)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Gold999
Starting Member

6 Posts

Posted - 2010-02-12 : 08:20:42

quote:
Originally posted by visakh16

select
Name,
Amount * RateInUSD,
c.EffectiveDate,
Date
from
Investments i,
Currencies c
where
i.Currency = c.Currency
and
c.EffectiveDate = (select
max(EffectiveDate)
from
Currencies
where
Currency = c.Currency
and EffectiveDate < i.Date
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Exactly what I've searched for!!!
Many Thanks!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 08:23:29

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources