Toad World® Forums

SQL Query Help

Hi, I have a really simple question, the problem is I don’t know what to search for to ask it as I’m struggling to describe it! All I need is a table output that has the Gross Media Cost (GMC) across different date ranges in their own columns for different advertisers, specifically the table would look like this…

Advertiser | GMC 3 Days Ago | GMC 2 Days Ago | GMC Yesterday |

Advertiser 1 | £ x | £x | £x
Advertiser 2 | £ x | £x | £x
Advertiser 3 | £ x | £x | £x

Hope that displays correctly… thanks in advance!

I have used case/if statements in the past to do this:

Sum(If(Range = ’ GMC 3 Days Ago’, £Field, 0)
Sum(CASE range when ’ GMC 3 Days Ago’ then £Field else 0 END)

Just remember to group by Advertiser

Message was edited by: Dave - Stt

Exactly what Dave - STT said.
Here is a real life example:

SQL> select advertiser, cost_date, gross_media_cost
2 from sample
3 order by advertiser, cost_date ;

ADVERTISER COST_DATE GROSS_MEDIA_COST


Advertiser 1 2012/03/10 20
Advertiser 1 2012/03/10 30
Advertiser 1 2012/03/11 15
Advertiser 1 2012/03/11 70
Advertiser 1 2012/03/12 46
Advertiser 2 2012/03/10 93
Advertiser 2 2012/03/11 14
Advertiser 2 2012/03/11 76
Advertiser 2 2012/03/11 40
Advertiser 2 2012/03/12 65
Advertiser 2 2012/03/12 89

11 ligne(s) sélectionnée(s).

SQL> select
2 advertiser,
3 sum (case cost_date
4 when trunc (sysdate, ‘DD’) - 2 then gross_media_cost
5 else 0
6 end
7 ) as gmc_2days_ago,
8 sum (case cost_date
9 when trunc (sysdate, ‘DD’) - 1 then gross_media_cost
10 else 0
11 end
12 ) as gmc_1day_ago,
13 sum (case cost_date
14 when trunc (sysdate, ‘DD’) then gross_media_cost
15 else 0
16 end
17 ) as gmc_today
18 from sample
19 group by advertiser
20 order by advertiser ;

ADVERTISER GMC_2DAYS_AGO GMC_1DAY_AGO GMC_TODAY


Advertiser 1 50 85 46
Advertiser 2 93 130 154

SQL>

I forgot to say: this method only works if you have a discrete number of date ranges you have to look at. If the number of date ranges is variable, then you will need to write a procedure and/or some dynamic SQL.

e.g. In the example in my previous post I knew that the only dates I had to look for were “2 days ago”, “yesterday”, “today”. If I didn’t know in advance how far back I had to go, a simple SELECT with CASE statements would miss some values. In my example, if my table had values that were older than “2 days ago”, they would not show up in the query output for the crosstabulation query.