asked on

Oracle query

select to_char(drive_date,'MON') date_label,
to_date(to_char(drive_date,'MON'),'MON') date_order,
sum(nvl(actual_draw,projection)) as Actual
from blood_drives
where drive_date between '01-jan-2011' and '31-dec-2011'
and drive_cancelled is null
and area_rep_no = 11
group by area_rep_no, to_date(to_char(drive_date,'MON'),'MON'),
to_char(drive_date,'MON')
-------------------------------
select to_char(drive_date,'MON') date_label,
to_date(to_char(drive_date,'MON'),'MON') date_order,
sum(nvl(actual_draw,projection)) as Actual
from blood_drives
where drive_date between '01-jan-2012' and '31-dec-2012'
and drive_cancelled is null
and area_rep_no = 11
group by area_rep_no, to_date(to_char(drive_date,'MON'),'MON'),
to_char(drive_date,'MON')

Result of 1st query

I have enclosed the results and what is expected in the excel spread sheet that is attached
result-1st-query.xls

Mark Geerlings

The results you posted from the queries appear to be from 2014, but your queries are for dates in 2011 and 2012. So that is one puzzle.

Then, it looks like the output you want is what is called a "matrix" or "cross-tab" report. Some reporting tools (Crystal Reports, Oracle Reports, etc.) can produce this kind of output, but simple SQL queries cannot. There may be an advanced SQL keyword that can be used these days to do that, but I haven't used it.

In the "old days", if you wanted to produce this kind of output from a single query without a reporting tool, you had to use "case" or "decode" and hard-code separate sections in your query for each additional column that you wanted in the output.

anumoses

ASKER