Home » SQL & PL/SQL » SQL & PL/SQL » Query help (Oracle 10.2.0.5, AIX 5.3)
Query help [message #556324] Fri, 01 June 2012 14:45 Go to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
Hi,

I've below tables.

Ref_Weeks table is kind of metadata table which contains weeks information (actually date)

sample_data table has the information for each customer when they changed their status like 'I' or 'O' for each channel like DM, EM and PH with date. Now i need to write query to get the status of customer for each date (i.e. for each date from ref_weeks table). If i don't have customer information on date present in ref_weeks table then i need to select previous date data. Can someone please help me on writing query



Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 
Connected as sminnakanti
 
SQL> create table ref_weeks (week_of date);
 
Table created
 
SQL> insert into ref_weeks values (to_date('20110601','YYYYMMDD'));
 
1 row inserted
SQL> insert into ref_weeks values (to_date('20110608','YYYYMMDD'));
 
1 row inserted
SQL> insert into ref_weeks values (to_date('20110615','YYYYMMDD'));
 
1 row inserted
SQL> insert into ref_weeks values (to_date('20110622','YYYYMMDD'));
 
1 row inserted
 
SQL> commit;
 
Commit complete

SQL> create table sample_data (cust_id number(5),
  2                            prod_id varchar2(5),
  3                            chnl_cd varchar2(2),
  4                            opt_dt date,
  5                            opt_cd varchar2(1)
  6                           );
 
Table created

SQL> Insert into sample_data values (123,'ABCD','DM',to_date('20110601','YYYYMMDD'),'I');
 
1 row inserted
SQL> Insert into sample_data values (123,'ABCD','EM',to_date('20110601','YYYYMMDD'),'O');
 
1 row inserted
SQL> Insert into sample_data values (123,'ABCD','PH',to_date('20110601','YYYYMMDD'),'O');
 
1 row inserted
SQL> Insert into sample_data values (123,'ABCD','DM',to_date('20110615','YYYYMMDD'),'O');
 
1 row inserted
SQL> Insert into sample_data values (123,'ABCD','EM',to_date('20110615','YYYYMMDD'),'I');
 
1 row inserted
SQL> Insert into sample_data values (123,'ABCD','PH',to_date('20110622','YYYYMMDD'),'I');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from sample_data;
 
CUST_ID PROD_ID CHNL_CD OPT_DT      OPT_CD
------- ------- ------- ----------- ------
    123 ABCD    DM      6/1/2011    I
    123 ABCD    EM      6/1/2011    O
    123 ABCD    PH      6/1/2011    O
    123 ABCD    DM      6/15/2011   O
    123 ABCD    EM      6/15/2011   I
    123 ABCD    PH      6/22/2011   I
 
6 rows selected



My output is to be like as below


CUST_ID PROD_ID CHNL_CD OPT_DT OPT_CD
------- ------- ------- ----------- ------
123 ABCD DM 6/1/2011 I
123 ABCD EM 6/1/2011 O
123 ABCD PH 6/1/2011 O
123 ABCD DM 6/8/2011 I
123 ABCD EM 6/8/2011 O
123 ABCD PH 6/8/2011 O

123 ABCD DM 6/15/2011 O
123 ABCD EM 6/15/2011 I
123 ABCD PH 6/15/2011 O
123 ABCD DM 6/22/2011 O
123 ABCD EM 6/22/2011 I

123 ABCD PH 6/22/2011 I

the ones highlighted in Blue i need to get it from previous date, Is there anyway i can use LAST_VALUE analytical function?

Appreicate your help.

Thanks
Sri
Re: Query help [message #556329 is a reply to message #556324] Fri, 01 June 2012 17:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
I wouldn't use last_value. I would use max (...) keep (dense_rank last order by ...), as shown below.

-- test data that you provided:
SCOTT@orcl_11gR2> select * from ref_weeks
  2  /

WEEK_OF
---------
01-JUN-11
08-JUN-11
15-JUN-11
22-JUN-11

4 rows selected.

SCOTT@orcl_11gR2> select * from sample_data
  2  /

   CUST_ID PROD_ CH OPT_DT    O
---------- ----- -- --------- -
       123 ABCD  DM 01-JUN-11 I
       123 ABCD  EM 01-JUN-11 O
       123 ABCD  PH 01-JUN-11 O
       123 ABCD  DM 15-JUN-11 O
       123 ABCD  EM 15-JUN-11 I
       123 ABCD  PH 22-JUN-11 I

6 rows selected.


-- all possible combinations:
SCOTT@orcl_11gR2> select distinct sd.cust_id, prod_id, chnl_cd, rw.week_of
  2  from   sample_data sd, ref_weeks rw
  3  order  by week_of, chnl_cd
  4  /

   CUST_ID PROD_ CH WEEK_OF
---------- ----- -- ---------
       123 ABCD  DM 01-JUN-11
       123 ABCD  EM 01-JUN-11
       123 ABCD  PH 01-JUN-11
       123 ABCD  DM 08-JUN-11
       123 ABCD  EM 08-JUN-11
       123 ABCD  PH 08-JUN-11
       123 ABCD  DM 15-JUN-11
       123 ABCD  EM 15-JUN-11
       123 ABCD  PH 15-JUN-11
       123 ABCD  DM 22-JUN-11
       123 ABCD  EM 22-JUN-11
       123 ABCD  PH 22-JUN-11

12 rows selected.


-- join of above query to sample_data
SCOTT@orcl_11gR2> with
  2    all_possible_combinations as
  3  	 (select distinct sd.cust_id, prod_id, chnl_cd, rw.week_of
  4  	  from	 sample_data sd, ref_weeks rw)
  5  select sd.cust_id, sd.prod_id, sd.chnl_cd, apc.week_of opt_dt,
  6  	    max (opt_cd) keep (dense_rank last order by sd.opt_dt) opt_cd
  7  from   all_possible_combinations apc, sample_data sd
  8  where  apc.cust_id = sd.cust_id (+)
  9  and    apc.prod_id = sd.prod_id (+)
 10  and    apc.chnl_cd = sd.chnl_cd (+)
 11  and    apc.week_of >= sd.opt_dt (+)
 12  group  by sd.cust_id, sd.prod_id, sd.chnl_cd, apc.week_of
 13  order  by opt_dt, chnl_cd
 14  /

   CUST_ID PROD_ CH OPT_DT    O
---------- ----- -- --------- -
       123 ABCD  DM 01-JUN-11 I
       123 ABCD  EM 01-JUN-11 O
       123 ABCD  PH 01-JUN-11 O
       123 ABCD  DM 08-JUN-11 I
       123 ABCD  EM 08-JUN-11 O
       123 ABCD  PH 08-JUN-11 O
       123 ABCD  DM 15-JUN-11 O
       123 ABCD  EM 15-JUN-11 I
       123 ABCD  PH 15-JUN-11 O
       123 ABCD  DM 22-JUN-11 O
       123 ABCD  EM 22-JUN-11 I
       123 ABCD  PH 22-JUN-11 I

12 rows selected.

Re: Query help [message #570870 is a reply to message #556329] Fri, 16 November 2012 13:49 Go to previous message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
Thank you all for your help.

Regards
Sri
Previous Topic: Query Help with table function
Next Topic: UTL_MAIL package
Goto Forum:
  


Current Time: Sun Aug 31 05:40:22 CDT 2014

Total time taken to generate the page: 0.11121 seconds