Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
CREATE TABLE [dbo].[tbl_currencies](
	[currency_id] [int] IDENTITY(1,1) NOT NULL,
	[currency] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_tbl_currencies] PRIMARY KEY CLUSTERED 
(
	[currency_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


currency_id	currency
1	                 LBP
2	                 USD
3	                 EUR


CREATE TABLE [dbo].[tbl_rate](
	[rate_id] [int] IDENTITY(1,1) NOT NULL,
	[currency_id] [int] NULL,
	[currency] [nvarchar](50) NULL,
	[time_stamp] [date] NULL,
	[rate] [decimal](18, 4) NULL
) ON [PRIMARY]
GO



rate_id currency_id currency time_stamp rate
1 1 LBP 2018-01-01 1.0000
2 2 USD 2018-01-01 1500.0000
3 1 LBP 2019-01-01 1.0000
4 2 USD 2019-01-01 1057.5000

What I have tried:

Select c.currency_id,c.currency,r.rate,Max(r.time_stamp)  from tbl_currencies c 
inner join tbl_rate r on c.currency_id=r.currency_id 
group by c.currency_id,c.currency,r.rate


currency_id	currency	rate	(No column name)
1	                     LBP	1.0000	2019-01-01
2	                     USD	1057.5000	2019-01-01
2	                     USD	1500.0000	2018-01-01



Ther Correct Result that must i retrive is 
currency_id	            currency rate	       (No column name)
1	                     LBP	1.0000	        2019-01-01
2	                     USD	1057.5000	    2019-01-01
Posted
Updated 6-Jan-19 6:06am
v2
Comments
OriginalGriff 6-Jan-19 10:25am    
And?
Did you have a question?

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
Use the "Improve question" widget to edit your question and provide better information.
Rabee3-F1.787545 6-Jan-19 10:57am    
I'm sorry for that and thanks for your time
i made the changes i hope its clear now
Richard MacCutchan 6-Jan-19 10:53am    
You do realise that currency rates fluctuate all the time? You should get your rates from a reputable online currency exchange website in order to provide accurate calculations.
Rabee3-F1.787545 6-Jan-19 10:58am    
Yes Sure but this to record them and save the changes during the all year
Richard MacCutchan 6-Jan-19 11:12am    
What about the ones that change every second? Storing these values is a waste of time, you need to get the real-time values from one of the currency exchange services or stock exchanges that provide them.

As far as I can see this is the same question as Select the latest record in the list by date[^]

As said this feels like homework so you should try to utilize the clauses described also in previous answer.

So the idea is, in order to select the latest rate, you just need to sort the data based on the timestamp column in descending order using ORDER BY Clause (Transact-SQL) - SQL Server | Microsoft Docs[^] and then in order to fetch only one record you can use TOP (Transact-SQL) - SQL Server | Microsoft Docs[^] .

Since you only want the latest record, you don't need GROUP BY clause nor you need the MAX aggregate.

ADDITION

In case the goal is to fetch latest rates for all currencies, then one possibility is to use a correlated EXISTS BY structure. Consider the following pseudo example

SQL
SELECT ...
FROM tbl_currencies c
WHERE NOT EXISTS (SELECT 1
                  FROM tbl_currencies c2
                  WHERE c2.Currency = c.Currency
                  AND   c2.time_stamp > c.time_stamp)
 
Share this answer
 
v2
Comments
Wendelius 6-Jan-19 11:38am    
Answer updated.
alter procedure GetCurrency

as
begin 
delete from tbl_Temp_rate
Declare @MID nvarchar(50)
DECLARE MY_CURSOR CURSOR 
     LOCAL STATIC READ_ONLY FORWARD_ONLY
     FOR 
     select distinct(currency_id) from tbl_currencies

     OPEN MY_CURSOR
     FETCH NEXT FROM MY_CURSOR INTO @MID
     WHILE @@FETCH_STATUS = 0
       BEGIN 
      
	   --insert into  tbl_Temp_rate   Select top 1 currency,rate,time_stamp from tbl_rate  where currency_id=1 order by time_stamp desc 
      INSERT INTO [dbo].[tbl_Temp_rate] Select top 1 currency_id,currency,time_stamp,rate from tbl_rate  where currency_id=@MID order by time_stamp desc 

         FETCH NEXT FROM MY_CURSOR INTO @MID
        END
        CLOSE MY_CURSOR
        DEALLOCATE MY_CURSOR
Select currency_id,currency,rate,time_stamp from tbl_Temp_rate


End
 
Share this answer
 
Comments
CHill60 7-Jan-19 6:37am    
Is this an actual solution or part of your question?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900