×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SQL: Select Case when ...
2

SQL: Select Case when ...

SQL: Select Case when ...

(OP)
Goal is to select data from a table ec_rates, but some conversion is needed to have the right data/column header in a csv file

Please see 2 lines of data from the table and what I would like

rate_code        market_date bid   ask   mid
FR.EUR.GBY.0.0.1 2012-03-14  0.32  0.32     0.32
FR.EUR.GBY.0.0.2 2012-03-14  0.681 0.681 0.681

Goal:

'market_date' needs to be converted into 'DATE'
mid rate for 'FR.EUR.GBY.0.0.1' needs to be taken into the column 'EURFRGBOND1Y'
mid rate for 'FR.EUR.GBY.0.0.2' needs to be taken into the column 'EURFRGBOND2Y'

Report (where data for column 2+3 is the mid rate):
DATE       EURFRGBOND1Y EURFRGBOND2Y
2012-03-14 0.32         0.681


SELECT market_date as DATE,
...
from ec_rates

RE: SQL: Select Case when ...

(OP)
I've tried this:

CASE WHEN (rate_code='FR.EUR.GBY.0.0.1') THEN mid else 0 end as EURFRGBOND1Y

But I don't want all the 0 values, I only want to report the value per market_date and per rate_code

RE: SQL: Select Case when ...

Use CAST to convert values to a specific data type, e.g. cast(market_date as date). Note that the ANSI date is in format YYYY-MM-DD!

It seems you want data per day? GROUP BY is used for

SELECT market_date,
       setfn(CASE WHEN (rate_code='FR.EUR.GBY.0.0.1') THEN mid else 0 end) as EURFRGBOND1Y,
       setfn(CASE WHEN (rate_code='FR.EUR.GBY.0.0.2') THEN mid else 0 end) as EURFRGBOND2Y
FROM yourtable
GROUP BY market_date

Where you replace setfn with SUM or MAX or AVG or another set function.

RE: SQL: Select Case when ...

(OP)
@JarlH: Thanks for the prompt reply. However, this is not what I mean.

Problem with your solution is that I get this as report:

market_date    EURFRGBOND1Y    EURFRGBOND2Y
2012-03-14     0.32            0
2012-03-14     0            0
2012-03-14     0            0
2012-03-14     0            0
2012-03-14     0            0
2012-03-14     0            0
...

Next to that: a function for the case when, e.g. SUM, AVG, ... is not needed.

RE: SQL: Select Case when ...

How many rows per day does your table have? How many rows per day do you want to be returned?

How many different "EURFRGBONDnY" columns are you actually having, is it just EURFRGBOND1Y and EURFRGBOND2Y, or do you have a whole bunch of them?



The better you describe your problem, the better answer you'll get!

RE: SQL: Select Case when ...

(OP)
You are right, I should have better described my problem.

There are hundreds of row per market date in the table. I would like only 1 row per market date and several columns for government bond rates, filled with the mid rate.

RE: SQL: Select Case when ...

The GROUP BY construction is typically used to "merge" several rows together into one row. Since you want one row per day, do group by the date column:

SELECT market_date
FROM yourtable
GROUP BY market_date


But for each date you do also want mid-values for 'FR.EUR.GBY.0.0.1' rows returned in a EURFRGBOND1Y-column. Since neither I nor the database handler knows if there are zero, one or several 'FR.EUR.GBY.0.0.1' for a day, you'll have to decide how to treat the case of several rows. Either you return the largest mid value (i.e. MAX), or the average mid value (AVG), or perhaps the sum of all mid values that day. (Speaking 'FR.EUR.GBY.0.0.1' specific mid values here.)

(The general GROUP BY rule says:
If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function.
)

Say you chose to go with each days average mid value for every specific EURFRGBONDnY column:

SELECT market_date,
       AVG(CASE WHEN (rate_code='FR.EUR.GBY.0.0.1') THEN mid else 0 end) as EURFRGBOND1Y,
       AVG(CASE WHEN (rate_code='FR.EUR.GBY.0.0.2') THEN mid else 0 end) as EURFRGBOND2Y,
       ... etc
FROM yourtable
GROUP BY market_date

 

RE: SQL: Select Case when ...

(OP)
Many thanks! I'm getting there, slowly smile

Now, for the date format. In the table the format of column market_date is yyyy-mm-dd. In the report I need a different format= dd-mmm-yy, e.g. 1-Jan-2012.

I've tried using DATEPART, e.g. SELECT DATEPART(d, market_date) + '-' + DATEPART(mm, market_date) + '-'+DATEPART(yy, market_date) as DATE,

However, the result is simply: 2012

Can you please help me out?

RE: SQL: Select Case when ...

ANSI SQL has no built-in functionality to convert dates to the format you want. Check your specific dbms manual, it might have something you can use.

In ANSI SQL you can use EXTRACT to get each specific part of a date, i.e. EXTRACT(day from market_date). But then you have to use CAST to convert to character, something like
cast(EXTRACT(day from market_date) as varchar(2)).

RE: SQL: Select Case when ...

(OP)
I found it:

SELECT CONVERT(varchar, datepart(day,axiom.market_date)) + '-' + CONVERT(varchar,datename(month,axiom.market_date)) + '-' +  CONVERT(varchar,datepart(year,axiom.market_date)) as DATE,

The only problem I still have is that the month is the full name, not only 3 characters, e.g. 'January' io 'Jan' ...

RE: SQL: Select Case when ...

Use CAST or SUBSTRING to get first 3 characters.

RE: SQL: Select Case when ...

(OP)
Great! I got what I need now.

SELECT CONVERT(varchar, datepart(day,axiom.market_date)) + '-' + SUBSTRING(CONVERT(varchar,datename(month,axiom.market_date)),1,3) + '-' +  CONVERT(varchar,datepart(year,axiom.market_date)) as DATE,

Thanks a lot for your help!

RE: SQL: Select Case when ...

alternatively...

CODE

SELECT CONVERT(VARCHAR,axiom.market_date,106)
which gives you dd mon yyyy

or

CODE

SELECT REPLACE(CONVERT(VARCHAR,axiom.market_date,106),' ','-')
if you really need the dashes

this is not even close to ANSI SQL, but i'm guessing you didn't care about ANSI from the get go smile

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close