Issue in using Date part in SQL query

Asked By roslin.miye 10 points N/A Posted on - 06/25/2011

Hi,

I have a SQL table “Currency_exchange_rates” that has fields Currency_code, Currency_date and Currency_rate. The table consists of values in random order. I want it the Date field to be ordered according to the currency code.

Example in the table is:

Currency_Code       Currency_Date        Currency_Rate        
           USD                   11-Jan-11                       2.00
           USD                   27-Feb-11                       2.17
           INR                    10-May-11                     45.43
           INR                    18-Jun-11                      42.54

I want it to become:

USD    11-Jan-11      2.00
USD    12-Jan-11      2.00
USD    13-Jan-11      2.00
INR     10-May-11    45.43

I tried using the avg functions on date range. But no luck. Please suggest some codes to get this output.

Thanks.

• Status: Open
• Question Views: 1067
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS SQL Server

Best Answer by jackrhonny

Go To Solution

Best Answer

Best Answer

Answered By jackrhonny 0 points N/A #130807

Issue in using Date part in SQL query

This is a good base, use the basics of SQL. First, create the Currency_exchange_rates table.

Create table Currency_exchange_rates
(Currency_Code varchar (20) not null,
Currency_date datetime not null,
Currency_rate decimal (40, 23) not null)

• The purpose of declaring the fields as not null is to avoid the insertion of null values in it. If the null values are being inserted, then issue will be created while using the minimum function. After creating the table, insert all the values that you are trying to manipulate in the SQL table. Then try to run the following script:
• Select Currency_code, Currency_date, min (Currency_rate) as Currency_rate,
• Datediff (day, 0, Currency_date) as Day_Num,
• rownumber () over (partition by Currency_code order by Currency_date) as Row_Num from Currency_exchange_rates group by Currency_code, Currency_date
• The datediff function is used to get the difference between the set of date durations. Now the output will be as expected. The date part is important in this query execution which may lead to the expected results

About jackrhonny

Questions
1

Answers
2

Best Answers
2

• Vote Up 0 Vote Down

• Posted on - 06/25/2011
• Question Category: MS SQL Server

Answered By hasan4368 10 points N/A #130808

Issue in using Date part in SQL query

You can use Transact-SQL (T-SQL) Order by clause to order the query. We can use Order by in the query, view, inline function, sub query and derived table.

Order by clause has four arguments:
1. Order by expression – the column we used for sort.
2. Collate (Collection Name) – inherit the collation of the current database.
3. ASC – it is a default sort type. Start from lowest value to highest value.
4. DESC – Start from highest value to lowest value.

Remember null values are treated as lowest possible values. Here is your solution:
Select Currency_Code, Currency_Date, Currency_Rate from Currency_exchange_rates
Order by currency_date,currency_code

About hasan4368

Questions
3

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 07/09/2011
• Question Category: MS SQL Server