Issue in using Date part in SQL query

Asked By 10 points N/A Posted on -
qa-featured

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.

SHARE
Best Answer by jackrhonny
Best Answer
Best Answer
Answered By 0 points N/A #130807

Issue in using Date part in SQL query

qa-featured

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
Answered By 10 points N/A #130808

Issue in using Date part in SQL query

qa-featured

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

Login/Register to Answer

Related Questions