Home » SQL & PL/SQL » SQL & PL/SQL » Need Query
Need Query [message #200998] Thu, 02 November 2006 03:44 Go to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Hi all,
My table contain these below data
A	B	C	Datamonth
1	1	1	200405
1	3	1	200407
1	1	1	200411
1	7	2	200609
1	1	2	200611

but i want to generate the o/p like given bleow
A	B	C	Datamonth
1	1	1	200405
1	1	1	200406
1	3	1	200407
1	3	1	200408
1	3	1	200409
1	3	1	200410
1	1	1	200411
1	1	1	200412
1	7	2	200609
1	7	2	200610
1	1	2	200611

That is, if the Datamonth is missing i have to make up the data with older month. For instance between 200405 and 200407, 200406 is missing. So i have ot make up the older month(200405) data for this month.
hope it is clear now

Thanks,
Thangam
Re: Need Query [message #201114 is a reply to message #200998] Thu, 02 November 2006 11:08 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You need a "generator" to make up the missing months - that is the query from dual. Then you need to outer join to your source table and look up the preceding existing value. To understand this, look at the output from the component pieces of the query.

sql>select * from t;

        A         B         C DATAMONTH
--------- --------- --------- ----------
        1         1         1 05/01/2004
        1         3         1 07/01/2004
        1         1         1 11/01/2004
        1         7         2 09/01/2006
        1         1         2 11/01/2006

5 rows selected.

sql>with list_of_months as
  2    (select add_months(to_date('200405', 'yyyymm'), rownum - 1) month
  3       from dual, 
  4            (select months_between(max(datamonth), min(datamonth)) + 1 mb 
  5               from t) nm
  6      connect by level <= nm.mb)
  7  select nvl(t.a, (select t2.a
  8                     from t t2
  9                    where t2.datamonth = (select max(datamonth)
 10                                            from t t3
 11                                           where t3.datamonth < lm.month))) a,
 12         nvl(t.b, (select t2.b
 13                     from t t2
 14                    where t2.datamonth = (select max(datamonth)
 15                                            from t t3
 16                                           where t3.datamonth < lm.month))) b, 
 17         nvl(t.c, (select t2.c
 18                     from t t2
 19                    where t2.datamonth = (select max(datamonth)
 20                                            from t t3
 21                                           where t3.datamonth < lm.month))) c, 
 22         nvl(t.datamonth, lm.month) datamonth
 23    from t, list_of_months lm
 24   where t.datamonth (+)= lm.month;      

        A         B         C DATAMONTH
--------- --------- --------- ----------
        1         1         1 05/01/2004
        1         1         1 06/01/2004
        1         3         1 07/01/2004
        1         3         1 08/01/2004
        1         3         1 09/01/2004
        1         3         1 10/01/2004
        1         1         1 11/01/2004
        1         1         1 12/01/2004
        1         1         1 01/01/2005
        1         1         1 02/01/2005
        1         1         1 03/01/2005
        1         1         1 04/01/2005
        1         1         1 05/01/2005
        1         1         1 06/01/2005
        1         1         1 07/01/2005
        1         1         1 08/01/2005
        1         1         1 09/01/2005
        1         1         1 10/01/2005
        1         1         1 11/01/2005
        1         1         1 12/01/2005
        1         1         1 01/01/2006
        1         1         1 02/01/2006
        1         1         1 03/01/2006
        1         1         1 04/01/2006
        1         1         1 05/01/2006
        1         1         1 06/01/2006
        1         1         1 07/01/2006
        1         1         1 08/01/2006
        1         7         2 09/01/2006
        1         7         2 10/01/2006
        1         1         2 11/01/2006

31 rows selected.
Re: Need Query [message #201593 is a reply to message #201114] Mon, 06 November 2006 00:23 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Hi Todd Barry,
Thanks for your response. Hope i had given bit different requirement. My actual requirement is given below.
Let us assume we have two table table1 and table2. Table2 will have only one column datamonth which will contain the data in YYYYMM format.
Table1 constist of columns A, B, C, D, datamonth. The mock data in both table is given below.
Table1
A	B	C	D	datamonth
1	1	1	333	200507
1	1	1	434	200509
1	1	1	647	200512
2	2	2 	873	200504
2	2	2	324	200505
2	2	2	234	200507

Table2:
datamonth
200504
200505
200506
200507
200508
200509
200510
200511
200512

In this scenario, for each A,B,C combination, i have to generate the records for each datamonth in Table2.
A	B	C	D	datamonth
1	1	1	333	200507
1	1	1	333	200508
1	1	1	434	200509
1	1	1	434	200510
1	1	1	434	200511
1	1	1	647	200512
2	2	2 	873	200504
2	2	2	324	200505
2	2	2	324	200506
2	2	2	234	200507

if the datamonth is not present in Table1(Ex 200508), then we have to generate the records for this by copying rest of the column value from previous records.
Here i have written Procedure to do this. But it is taking quit long time. In the real environment, Table1 will have 17 million records and Table2 will have required datamonth range. I’m looking for either SQL query or PL/SQL

Sorry for inconvenience. Hope now the requirement is clear.
Re: Need Query [message #201622 is a reply to message #201593] Mon, 06 November 2006 02:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Given that you never mentioned this second table until now, I think Todd can be forgiven for not intuiting its existance.

Try this as a solution.
create table table_a (a number, b number, c number, d number, datemonth date);

create table table_b (datemonth date);

insert into table_a values (1,	1,	1,	333,	to_Date('200507','yyyymm'));
insert into table_a values (1,	1,	1,	434,	to_Date('200509','yyyymm'));
insert into table_a values (1,	1,	1,	647,	to_Date('200512','yyyymm'));
insert into table_a values (2,	2,	2 ,	873,	to_Date('200504','yyyymm'));
insert into table_a values (2,	2,	2,	324,	to_Date('200505','yyyymm'));
insert into table_a values (2,	2,	2,	234,	to_Date('200507','yyyymm'));

insert into table_b values (to_date('200504','yyyymm'));
insert into table_b values (to_date('200505','yyyymm'));
insert into table_b values (to_date('200506','yyyymm'));
insert into table_b values (to_date('200507','yyyymm'));
insert into table_b values (to_date('200508','yyyymm'));
insert into table_b values (to_date('200509','yyyymm'));
insert into table_b values (to_date('200510','yyyymm'));
insert into table_b values (to_date('200511','yyyymm'));
insert into table_b values (to_date('200512','yyyymm'));

select a.a
      ,a.b
      ,a.c
      ,a.d
      ,b.datemonth
from  (select a,b,c,d,datemonth,lead(datemonth) over (partition by a,b,c order by datemonth) next_month
       from   table_a) a
      ,table_b b
where b.datemonth >= a.datemonth
and   b.datemonth < nvl(a.next_month,a.datemonth+1)
order by a,b,c,datemonth;

         A          B          C          D DATEMONTH
---------- ---------- ---------- ---------- ---------
         1          1          1        333 01-JUL-05
         1          1          1        333 01-AUG-05
         1          1          1        434 01-SEP-05
         1          1          1        434 01-OCT-05
         1          1          1        434 01-NOV-05
         1          1          1        647 01-DEC-05
         2          2          2        873 01-APR-05
         2          2          2        324 01-MAY-05
         2          2          2        324 01-JUN-05
         2          2          2        234 01-JUL-05
Re: Need Query [message #201657 is a reply to message #201622] Mon, 06 November 2006 03:56 Go to previous message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Thanks so much JRowbottom
Previous Topic: Can procedures in a package step on each other?
Next Topic: Diagonal Subtraction of Column Values
Goto Forum:
  


Current Time: Wed Dec 07 22:13:09 CST 2016

Total time taken to generate the page: 0.11717 seconds