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  |
sminnakanti
Messages: 73
Registered: October 2008
|
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  |
 |
Barbara Boehmer
Messages: 7668
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Wed May 22 02:01:25 CDT 2013
Total time taken to generate the page: 0.19734 seconds
|
] 
Members
Search
Help
Register
Login
Home
