Quarterly Data Select using SQL
MLBrownJan 27 2012 — edited Jan 30 2012All -
The database version I am using is 10.2.0.4.0
This is probably simple, but for the life of me, I cannot figure it out. I have a table of about 200 Codes and a table that contains millions of records that are date stamped (data is from 1995 to sysdate). I need a query for a view to be used in a report that will show the different codes used by employee's for specific quarters based on a date parameter. If they do not have data for that quarter I still want to show the quarter, but show 0 for the number.
I created this simple example. If I can see how someone does this, I can incorporate that logic into what I am doing.
SQL for test data:
create table bunch_of_codes
(id_code number(12) primary key,
description varchar2(10));
insert into bunch_of_codes
values (1, 'APPLE');
insert into bunch_of_codes
values (2, 'ORANGE');
insert into bunch_of_codes
values (3, 'GRAPE');
create table bunch_of_data
(id_code number(12),
activity_date date,
CONSTRAINT data_id_code
FOREIGN KEY (id_code) REFERENCES bunch_of_codes(id_code));
INSERT INTO bunch_of_data
VALUES (1, to_date('4/12/2011','MM/DD/YYYY'));
INSERT INTO bunch_of_data
VALUES (1, to_date('6/1/2011','MM/DD/YYYY'));
INSERT INTO bunch_of_data
VALUES (3, to_date('1/15/2011','MM/DD/YYYY'));
INSERT INTO bunch_of_data
VALUES (3, to_date('4/17/2011','MM/DD/YYYY'));
INSERT INTO bunch_of_data
VALUES (3, to_date('11/11/2011','MM/DD/YYYY'));
COMMIT;
What I want to see when I execute the SQL Statement is this:
DESCRIPTION QUARTER COUNT
----------- ------- -----
APPLE 1 0
APPLE 2 2
APPLE 3 0
APPLE 4 0
GRAPE 1 1
GRAPE 2 1
GRAPE 3 0
GRAPE 4 1
ORANGE 1 0
ORANGE 2 0
ORANGE 3 0
ORANGE 4 0
I need every code printed out regardless if it was used of not. If it wasn't used, I still want to see each quarter but show 0 for the count.
For this example, I can run this SQL to join the data:
select c.description,
to_char(d.activity_date,'Q') quarter,
count(d.id_code) count
from bunch_of_codes c,
bunch_of_data d
where c.id_code = d.id_code
group by c.description, to_char(d.activity_date,'Q')
order by description, quarter
But if there isn't any detail for the quarter then I do not get a row of data. I have tried all sorts of things, but nothing seems to work.
Please help if you have any ideas.