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.
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
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
