Find Latest Value of Exchange_rate

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

SQL Server Administration (2005)

Find Latest Value of Exchange_rate

Author

Topic

Freaking_cute
Starting Member

4 Posts

Posted - 2012-05-29 : 04:01:49

Hello All...
please guide me how i get latest value of exchange rate.in details : i have a table named "Temp" who contain columns of exchange_rate,Start_date,end_date.and i want output like
record_date :21-Feb-2012
exchange_rate : 0.3366

but i want latest value of exchange_rate of February what should i do now???

please need your reply as soon as possible

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-29 : 05:11:28

SELECT TOP(1) * FROM dbo.Temp WHERE StartDate <= '20120229' ORDER BY StartDate DESC

N 56°04'39.26"
E 12°55'05.63"

Freaking_cute
Starting Member

4 Posts

Posted - 2012-05-29 : 06:17:12

Thanks SwePeso.

But i want latest record.i have just given you that date as a sample,it is not the actual one.like i have data like this
Start_date :14-Feb-2007 end_date : 14-Mar-2007 Exchange_rate:0.336
Start_date :15-Feb-2007 end_date : 14-Apr-2007 Exchange_rate:0.663
these are my table data.

Now the output which i got is

Month exchange_rate
Feb 0.336
Mar 0.336
Feb 0.663
Mar 0.663
April 0.663

Now you can see that here is Feb Twice time so i want latest exchange_rate value of Feb which is 0.663..so how i remove or delete the previous exchange_rate value or how i just display the latest value of Feb and also these type of month which comes multiple times

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-29 : 11:43:34

You want all months in one query? Or only one month?
Your description is at minimum, unclear.

DECLARE	@Sample TABLE
	(
		StartDate DATE NOT NULL,
		EndDate DATE NOT NULL,
		ExchangeRate SMALLMONEY NOT NULL
	)

INSERT	@Sample
VALUES	('14-Feb-2007', '14-Mar-2007', 0.336),
	('15-Feb-2007', '14-Apr-2007', 0.663)

-- SwePeso
SELECT		DATENAME(MONTH, DATEADD(DAY, v.Number, s.StartDate)) AS theMonth,
		s.ExchangeRate
FROM		(
			SELECT	StartDate,
				DATEDIFF(DAY, StartDate, EndDate) AS Interval,
				ROW_NUMBER() OVER (ORDER BY StartDate) AS GrpID,
				ExchangeRate
			FROM	@Sample
		) AS s
INNER JOIN	master.dbo.spt_values AS v ON v.Type = 'P'
			AND v.Number BETWEEN 0 AND s.Interval
GROUP BY	DATENAME(MONTH, DATEADD(DAY, v.Number, s.StartDate)),
		s.ExchangeRate,
		s.GrpID
ORDER BY	s.GrpID,
		MIN(v.Number)

N 56°04'39.26"
E 12°55'05.63"

Freaking_cute
Starting Member

4 Posts

Posted - 2012-05-29 : 14:25:22

Oh thanks a lot Swepose really!! :O you are Champ for me :)
I will run this query tomorrow for sure and will let you know.
Can you give me one solution more please??
Actually i declared cursor and within cursor i declared a FOR loop but now within this session/cursor, i want to create a temporary(Temp) table that stores the output of my query so the question is how i declare Temp table by using oracle.

i wrote within cursor like :

Declare Global Temporary Table Temp
(
id int;
Name varchar(5);
)on commit preserve rows;

the problem with above declaration is it is not creating temp table.Have you any idea about this??please kindly let me know as i am new also i have not enough knowlegde about SQL :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 14:33:38

you might be better off redirecting oracle questions to relevant forums like www.orafaq.com

this is ms sql server forum

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

Freaking_cute
Starting Member

4 Posts

Posted - 2012-06-01 : 03:30:24

hey i am now again stuck in one problem please someone let me out from this..I have table named 'Temp' having columns
EXCH_GRP_CODE,
EXCH_RATES_CODE,
CURRENCY_CODE,
MONTH_NAME,
YEAR_CODE,
EXCH_RT
and i got data/output after running one of my query is:
output

EXCH_GRP_CODE,EXCH_RT_CODE,CURRENCY_CODE,MONTH_NAME,YEAR_CODE,EXCH_RT

ACT,ME,FI,DEC,2003,2.072
ACT,CE,FI,DEC,2003,1.94
ACT,AVG,FI,JAN,2004,0.95
ACT,PE,FI,JAN,2004,0.91
ACT,AVG,FI,FEB,2004,0.98
ACT,PE,FI,FEB,2004,5.93
ACT,AVG,FI,MAR,2004,6.48
ACT,PE,FI,MAR,2004,5.99
Now,how i get the updated records between same name of months like i need this record of dec:ACT,CE,FI,DEC,2003,1.94.mean latest record of dec 2003..please tell me the solutions??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources