Home » SQL & PL/SQL » SQL & PL/SQL » filling in missing data (10g R2)
filling in missing data [message #345691] Thu, 04 September 2008 08:47 Go to next message
hungman
Messages: 16
Registered: September 2006
Junior Member
Hey all,

just wondering if you can guys can give me a hand on working this out? Here's my scenario, I'd like to return a result set with something like this:

Month       Year         Amt
------      -----        ----
3           2008         200
4           2008         200
5           2008         200
6           2008         300
7           2008         300
8           2008         300
9           2008         300


What I have is two tables: year_month (which stores all the months for a given years) and a transaction table (which stores the amounts, transaction_date, etc...)

What I'm to trying to achieve is to fill in the "missing" records. For example, if there transactions in the month of March 2008 for $200 and June 2008 for $300 in the transaction table, I'd like to return the missing months (up to the current date) with the amt carried forward.

I've managed to do this in PL/SQL, but am currently trying to do it with straight SQL using the MODEL clause, but I am unsuccessful so far, I don't know if I'm chasing an impossible here or if someone can point me in the right direction.

Thanks,
John.
Re: filling in missing data [message #345695 is a reply to message #345691] Thu, 04 September 2008 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: filling in missing data [message #345697 is a reply to message #345691] Thu, 04 September 2008 09:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't know how to do it with MODEL, but I can do it in SQL anyway:
SQL> with src as (select 3 month ,2008 year,200 amt from dual union all
  2               select 4,2008,200 from dual union all
  3               select 5,2008,200 from dual union all
  4               select 6,2008,300 from dual union all
  5               select 7,2008,300 from dual union all
  6               select 8,2008,300 from dual union all
  7               select 9,2008,300 from dual)
  8  select distinct 
  9          month
 10         ,year
 11         ,max(amt) over (partition by year,month) amt
 12         ,sum(amt) over (order by year,month) rolling_sum
 13  from (select * from src
 14       union all
 15        select level,2008,0 from dual connect by level <= 12)
 16  order by year,month;
     MONTH      YEAR        AMT ROLLING_SUM
---------- ---------- ---------- -----------
         1       2008          0           0
         2       2008          0           0
         3       2008        200         200
         4       2008        200         400
         5       2008        200         600
         6       2008        300         900
         7       2008        300        1200
         8       2008        300        1500
         9       2008        300        1800
        10       2008          0        1800
        11       2008          0        1800
        12       2008          0        1800

12 rows selected.
Re: filling in missing data [message #345699 is a reply to message #345691] Thu, 04 September 2008 09:27 Go to previous messageGo to next message
hungman
Messages: 16
Registered: September 2006
Junior Member
create table year_month
(
   cal_year     number,
   cal_month    number
);

begin
   for i in 1..12 loop
      insert into year_month(cal_year, cal_month) values (2008, i);
   end loop;
end;

create table transactions
(
   transaction_id       number,
   amt                  number,
   transaction_year     number,
   transaction_month    number
);

insert into transactions (transaction_id, amt, transaction_year, transaction_month) values (1, 200, 2008, 3);
insert into transactions (transaction_id, amt, transaction_year, transaction_month) values (2, 300, 2008, 6);


Year_Month table
Month       Year
------      -----
2008        1
2008        2
2008        3
2008        4
2008        5
2008        6
2008        7
2008        8
2008        9
2008        10
2008        11
2008        12


Transcations table
Transaction_id    Amt    Transcation_Year   Transaction_Month
---------------   ---    ----------------   -----------------
1                 200    2008               3
2                 300    2008               6


what I'm trying to achieve is:
Month       Year         Amt
------      -----        ----
3           2008         200
4           2008         200
5           2008         200
6           2008         300
7           2008         300
8           2008         300
9           2008         300

Re: filling in missing data [message #345700 is a reply to message #345691] Thu, 04 September 2008 09:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Reading the question more carefully, you say you have a table with the dates in it already, so you can just use an outer join and an analytic sum:
SQL> with year_month as (select level month,2008 year from dual connect by level <= 12)
  2      ,trans      as (select 3 month ,2008 year,200 amt from dual union all
  3                      select 4,2008,200 from dual union all
  4                      select 5,2008,200 from dual union all
  5                      select 6,2008,300 from dual union all
  6                      select 7,2008,300 from dual union all
  7                      select 8,2008,300 from dual union all
  8                      select 9,2008,300 from dual)
  9  select month
 10        ,year
 11        ,amt,sum(amt) over (order by month,year) rolling_amt
 12  from  (select year_month.month
 13               ,year_month.year
 14               ,nvl(trans.amt,0) amt
 15         from   year_month
 16               ,trans
 17         where  year_month.month = trans.month(+)
 18         and    year_month.year = trans.year(+));
     MONTH      YEAR        AMT ROLLING_AMT
---------- ---------- ---------- -----------
         1       2008          0           0
         2       2008          0           0
         3       2008        200         200
         4       2008        200         400
         5       2008        200         600
         6       2008        300         900
         7       2008        300        1200
         8       2008        300        1500
         9       2008        300        1800
        10       2008          0        1800
        11       2008          0        1800
        12       2008          0        1800

12 rows selected.
Re: filling in missing data [message #345739 is a reply to message #345691] Thu, 04 September 2008 12:29 Go to previous message
hungman
Messages: 16
Registered: September 2006
Junior Member
well, after some plugging away I managed to get this:

select     b.cal_year,
           b.cal_month,
           nvl(b.amt, t2.amt)
from       (
            select     y.cal_year,
                       y.cal_month,
                       nvl(t.transaction_id, (select   max(t2.transaction_id)
                                              from     transactions t2
                                              where    t2.transaction_month <= y.cal_month
                                             )) transaction_id,
                       t.amt
            from       (select    *
                        from      year_month y
                        where     y.cal_month between (select   min(t.transaction_month)
                                                       from     transactions t
                                                      ) and extract(month from sysdate)
                       ) y
                       left outer join transactions t on t.transaction_year = y.cal_year and t.transaction_month = y.cal_month 
            ) b 
            left outer join transactions t2 on t2.transaction_id = b.transaction_id
order by   1, 2


thanks for the help JRowbottom
Previous Topic: Update in two table by one update statement
Next Topic: outer join (merged, was Please help with a (+) question (newbe) and how do you constrain a result se
Goto Forum:
  


Current Time: Sun Dec 04 18:49:50 CST 2016

Total time taken to generate the page: 0.08721 seconds