SQL: Select Case when ... 2

[dEX2340]

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

 

[dEX2340]

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

 

[JarlH]

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

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.

 

[dEX2340]

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

 

[JarlH]

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!

 

[dEX2340]

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.

 

[JarlH]

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:
[tt]
SELECT market_date
FROM yourtable
GROUP BY market_date[/tt]

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:
[tt]
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[/tt]

 

[dEX2340]

Many thanks! I'm getting there, slowly

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?

 

[JarlH]

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. [tt]EXTRACT(day from market_date)[/tt]. But then you have to use CAST to convert to character, something like
[tt]cast(EXTRACT(day from market_date) as varchar(2))[/tt].

 

[dEX2340]

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

 

[JarlH]

Use CAST or SUBSTRING to get first 3 characters.

 

[dEX2340]

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!

 

[r937]

alternatively...

SELECT CONVERT(VARCHAR,axiom.market_date,106)

which gives you dd mon yyyy

or

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

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