SQL – Filling in Gaps in the Source Data 2

December 8, 2009

The following question recently appeared in the comp.databases.oracle.server group:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/d9c7420e0e36231d

Could anyone show me how to write a analytical query for my requirement.

Query:

SELECT distinct bug_when,
       login_name,
       SUM(incoming_count - outgoing_count)
        OVER (PARTITION BY login_name ORDER BY bug_when) AS OPEN
FROM RM_COMP_INCOM_OUTGO_BUGIDS_GT G,
     bz_components c,
     bz_profiles p
where  G.component_id= c.ID and
c.manager = p.userid
order by 2,1 desc;

Query returns data like this:

12/04/2009 ssmit 52
12/02/2009 ssmit 48
11/30/2009 ssmit 45
11/29/2009 ssmit 42

I want the data to be like this:

12/04/2009 ssmit 52
12/03/2009 ssmit 48 -- fill the gap with previous value
12/02/2009 ssmit 48
12/01/2009 ssmit 45
11/30/2009 ssmit 45
11/30/2009 ssmit 45
11/29/2009 ssmit 42

Table Desc:

CREATE GLOBAL TEMPORARY TABLE RM_COMP_INCOM_OUTGO_BUGIDS_GT
(
  BUG_WHEN        DATE,
  COMPONENT_ID    NUMBER,
  INCOMING_COUNT  NUMBER,
  OUTGOING_COUNT  NUMBER
);

Set up a test table for experimentation (note, I changed the value for test from 42 to 12 for extra clarity and added additional rows):

CREATE TABLE T1
(
  BUG_WHEN        DATE,
  COMPONENT_ID    VARCHAR2(10),
  INCOMING_COUNT  NUMBER,
  OUTGOING_COUNT  NUMBER
);

INSERT INTO T1 VALUES (TO_DATE('12/07/2009','MM/DD/YYYY'),'ssmit',60,60);
INSERT INTO T1 VALUES (TO_DATE('12/04/2009','MM/DD/YYYY'),'ssmit',52,52);
INSERT INTO T1 VALUES (TO_DATE('12/02/2009','MM/DD/YYYY'),'ssmit',48,48);
INSERT INTO T1 VALUES (TO_DATE('11/30/2009','MM/DD/YYYY'),'ssmit',45,45);
INSERT INTO T1 VALUES (TO_DATE('11/29/2009','MM/DD/YYYY'),'ssmit',42,42);
INSERT INTO T1 VALUES (TO_DATE('01/29/2009','MM/DD/YYYY'),'test',12,12);

COMMIT;

SELECT
  *
FROM
  T1;

BUG_WHEN  COMPONENT_ INCOMING_COUNT OUTGOING_COUNT
--------- ---------- -------------- --------------
07-DEC-09 ssmit                  60             60
04-DEC-09 ssmit                  52             52
02-DEC-09 ssmit                  48             48
30-NOV-09 ssmit                  45             45
29-NOV-09 ssmit                  42             42
29-JAN-09 test                   12             12

Let’s start with using the LEAD function to peek at the next row from the T1 table.

SELECT
  COMPONENT_ID,
  BUG_WHEN,
  LEAD(BUG_WHEN,1) OVER (PARTITION BY COMPONENT_ID ORDER BY BUG_WHEN) NEXT_BUG_WHEN,
  INCOMING_COUNT
FROM
  T1;

COMPONENT_ BUG_WHEN  NEXT_BUG_ INCOMING_COUNT
---------- --------- --------- --------------
ssmit      29-NOV-09 30-NOV-09             42
ssmit      30-NOV-09 02-DEC-09             45
ssmit      02-DEC-09 04-DEC-09             48
ssmit      04-DEC-09 07-DEC-09             52
ssmit      07-DEC-09                       60
test       29-JAN-09                       12

Above needs to be modified to permit ranges by subtracting 1 from the next date.  Also we need to fix the NULL values on the last row of each COMPONENT_ID grouping.

SELECT
  COMPONENT_ID,
  BUG_WHEN,
  NVL(LEAD(BUG_WHEN,1) OVER (PARTITION BY COMPONENT_ID ORDER BY BUG_WHEN)-1,BUG_WHEN) THROUGH_BUG_WHEN,
  NVL(LEAD(BUG_WHEN,1) OVER (PARTITION BY COMPONENT_ID ORDER BY BUG_WHEN)-1,BUG_WHEN)-BUG_WHEN RANGE_DAYS,
  INCOMING_COUNT
FROM
  T1;

COMPONENT_ BUG_WHEN  THROUGH_B RANGE_DAYS INCOMING_COUNT
---------- --------- --------- ---------- --------------
ssmit      29-NOV-09 29-NOV-09          0             42
ssmit      30-NOV-09 01-DEC-09          1             45
ssmit      02-DEC-09 03-DEC-09          1             48
ssmit      04-DEC-09 06-DEC-09          2             52
ssmit      07-DEC-09 07-DEC-09          0             60
test       29-JAN-09 29-JAN-09          0             12

Now, all we need to do is find a way to generate the missing dates between the BUG_WHEN date and the THROUGH_BUG_WHEN date.  If we slide the above into an inline view and join to an inline view with a counter generator, we end up with the following:

SELECT
  T1.COMPONENT_ID,
  T1.BUG_WHEN+V1.C BUG_WHEN,
  T1.INCOMING_COUNT
FROM
  (SELECT
    COMPONENT_ID,
    BUG_WHEN,
    NVL(LEAD(BUG_WHEN,1) OVER (PARTITION BY COMPONENT_ID ORDER BY BUG_WHEN)-1,BUG_WHEN) THROUGH_BUG_WHEN,
    NVL(LEAD(BUG_WHEN,1) OVER (PARTITION BY COMPONENT_ID ORDER BY BUG_WHEN)-1,BUG_WHEN)-BUG_WHEN RANGE_DAYS,
    INCOMING_COUNT
  FROM
    T1) T1,
  (SELECT
    ROWNUM-1 C
  FROM
    DUAL
  CONNECT BY
    LEVEL<=100) V1
WHERE
  V1.C<=T1.RANGE_DAYS
ORDER BY
  T1.COMPONENT_ID,
  T1.BUG_WHEN+V1.C DESC;

COMPONENT_ BUG_WHEN  INCOMING_COUNT
---------- --------- --------------
ssmit      07-DEC-09             60
ssmit      06-DEC-09             52
ssmit      05-DEC-09             52
ssmit      04-DEC-09             52
ssmit      03-DEC-09             48
ssmit      02-DEC-09             48
ssmit      01-DEC-09             45
ssmit      30-NOV-09             45
ssmit      29-NOV-09             42
test       29-JAN-09             12