CODEWITHSUNDEEP
sqloracle-databaseselect
Kairan
Kairan

Reputation: 5542

Select query to return values for all records

I am trying to retrieve some report data

This is my query:

SELECT sourceCode as "Source", to_char(myTimestamp, 'YYYY-MM-DD') as "Date", statusCode as "Status", count(*) as "Count"
FROM archive_table
WHERE myTimestamp BETWEEN TO_TIMESTAMP('2013-09-30','yyyy-mm-dd') AND TO_TIMESTAMP('2013-10-05','yyyy-mm-dd')
GROUP BY sourceCode, to_char(myTimestamp, 'YYYY-MM-DD'), statusCode
ORDER BY 1, 2, 3

This is the output:

Source  Date    Status  Count
Source1 9/30/2013   C   10
Source1 10/1/2013   C   8
Source1 10/2/2013   C   24
Source1 10/2/2013   O   4
Source2 10/4/2013   C   22
Source2 10/4/2013   O   7
Source3 10/1/2013   C   2
Source4 9/30/2013   C   15
Source4 9/30/2013   O   15
Source4 10/1/2013   C   24
Source4 10/1/2013   O   12

What I would like to see is all the Counts of 0, for example Source1 with Date of 9/30/2013 has no Status 0 in the database so I would like for it to display:

Source1  9/30/2013  O  0

Another example, there is now Source2 at all for 9/30/2013 so it should display:

Source2  9/30/2013  O  0
Source2  9/30/2013  C  0

Im hoping there is a simple way to do this. For additional information, there are only 4 "sources" and status is either "O" or "C", obviously the date range is provided in the where clause.

Upvotes: 1

Views: 84

Answers (2)

Shawn Mire
Shawn Mire

Reputation: 306

Rather than specifying each date within a date range, use the following and you'll only need to include the start and end dates:

SELECT
    TO_DATE('09/30/2013','mm/dd/yyyy') - 1 + LEVEL dt
FROM dual
    CONNECT BY
LEVEL <= ( TO_DATE('10/05/2013','mm/dd/yyyy')
         - TO_DATE('09/30/2013','mm/dd/yyyy')) + 1

Upvotes: 1

Derek
Derek

Reputation: 23228

SQL Fiddle for easy reference

The easiest way to do this is if you have a table of values containing the possible Sources, Statuses, and Dates. Even if you don't, you can still generate derived tables on the fly that can support your query.

This generates every combination of Source, Status, and Date you're trying to query.

select
  *
from
  (select 'Source1' Source from Dual
   union select 'Source2' from Dual
   union select 'Source3' from Dual
   union select 'Source4' from Dual
   ) s
  cross join (
    select 'O' Status from Dual
    union select 'C' from Dual
   ) c
  cross join (
    select '2013-09-30' Dt from Dual
    union select '2013-10-01' from Dual
    union select '2013-10-02' from Dual
    union select '2013-10-03' from Dual
    union select '2013-10-04' from Dual
    union select '2013-10-05' from Dual
   ) d;

Then you can take all of this and left join to your archive_table and do your grouping on the fabricated data rather than your base table and do a count on one of your archive_table columns.

select
  s.Source,
  c.Status,
  d.Dt,
  count(t.Source)
from
  (select 'Source1' Source from Dual
   union select 'Source2' from Dual
   union select 'Source3' from Dual
   union select 'Source4' from Dual
   ) s
  cross join (
    select 'O' Status from Dual
    union select 'C' from Dual
   ) c
  cross join (
    select '2013-09-30' Dt from Dual
    union select '2013-10-01' from Dual
    union select '2013-10-02' from Dual
    union select '2013-10-03' from Dual
    union select '2013-10-04' from Dual
    union select '2013-10-05' from Dual
   ) d
  left join test t
    on s.Source = t.Source
       and c.Status = t.Status
       and d.Dt = t.Dt
group by
  s.Source,
  c.Status,
  d.Dt

Upvotes: 2

Related Questions