Home » SQL & PL/SQL » SQL & PL/SQL » Query help (Oracle 11.2.0.4, Linux)
Query help [message #635210] Tue, 24 March 2015 13:14 Go to next message
sspn2010
Messages: 167
Registered: October 2008
Senior Member
Hi,

Can someone please help me out on the below query.

I've a table that contains the web site browse information as given below


with browse_tbl as
(select 1 as cust_id, date '2015-01-01' as browse_dt from dual
 union
 select 1 as cust_id, date '2015-02-01' as browse_dt from dual
 union
 select 2 as cust_id, date '2015-03-01' as browse_dt from dual
 )
select * from browse_tbl;


Second table contains purchase information as given below


with 
sales_tbl as
 (select 1 as cust_id, date '2015-01-10' as txn_dt, 'S' as txn_source from dual
  union
  select 1 as cust_id, date '2015-02-01' as txn_dt, 'S' as txn_source from dual
  union
  select 1 as cust_id, date '2015-02-10' as txn_dt, 'S' as txn_source from dual
  union
  select 1 as cust_id, date '2015-03-01' as txn_dt, 'O' as txn_source from dual
 )
select * from sales_tbl order by 2;


I need to assign the purchases to the browse date.

1) cust_id 1 has browsed website first time on 1/1/2015 and made purchase on 1/20/2015, this purchase need to attributed to the browse date 1/1/2015.

2) cust_id 1 browsed again website on 2/1/2015 and 3/1/2015 and made purchases on 2/1/2015 (i.e. same day), 2/10/2015 (which are before the next browse date 3/1/2015) and these purchases need to be attributed to browse date 2/1/2015

3) Made another purchase on 3/1/2015 and this need to be attributed to the browse date 3/1/2015.

I've written below query and it's not giving the correct output. I'm not sure if this is the right approach, can some one please help me out?


with browse_tbl as
(select 1 as cust_id, date '2015-01-01' as browse_dt from dual
 union
 select 1 as cust_id, date '2015-02-01' as browse_dt from dual
 union
 select 2 as cust_id, date '2015-03-01' as browse_dt from dual
 ), 
sales_tbl as
 (select 1 as cust_id, date '2015-01-10' as txn_dt, 'S' as txn_source from dual
  union
  select 1 as cust_id, date '2015-02-01' as txn_dt, 'S' as txn_source from dual
  union
  select 1 as cust_id, date '2015-02-10' as txn_dt, 'S' as txn_source from dual
  union
  select 1 as cust_id, date '2015-03-01' as txn_dt, 'O' as txn_source from dual
 )
select a.cust_id, browse_dt, b.txn_dt, a.next_browse_dt, b.txn_source from 
     (select cust_id, browse_dt, lead(browse_dt,1) over(partition by cust_id order by browse_dt) next_browse_dt from browse_tbl) a, sales_tbl b
  where a.cust_id = b.cust_id
     and (a.browse_dt <= b.txn_dt and nvl(a.next_browse_dt,b.txn_dt) >= b.txn_dt) 
  order by 2,1,3;

   CUST_ID BROWSE_DT TXN_DT    NEXT_BROWSE_DT TXN_SOURCE
---------- --------- --------- -------------- ----------
         1 01-JAN-15 10-JAN-15 01-FEB-15      S         
         1 01-JAN-15 01-FEB-15 01-FEB-15      S         
         1 01-FEB-15 01-FEB-15                S         
         1 01-FEB-15 10-FEB-15                S         
         1 01-FEB-15 01-MAR-15                O         

5 rows selected.

Need ouput as given below

   CUST_ID BROWSE_DT TXN_DT    NEXT_BROWSE_DT TXN_SOURCE
---------- --------- --------- -------------- ----------
         1 01-JAN-15 10-JAN-15 01-FEB-15      S         
         1 01-FEB-15 01-FEB-15                S         
         1 01-FEB-15 10-FEB-15                S         
         1 01-MAR-15 01-MAR-15                O


Appreciate your help!

Thanks
SS

Report message to a moderator

Re: Query help [message #635216 is a reply to message #635210] Tue, 24 March 2015 14:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
1) cust_id 1 has browsed website first time on 1/1/2015 and made purchase on 1/20/2015


There is no purchase with this date in your table.

Quote:
2/1/201


Is this 2nd January or 1st February?
Use month names in your explanation we don't all use the same default format.

[Updated on: Tue, 24 March 2015 14:17]

Report message to a moderator

Re: Query help [message #635217 is a reply to message #635216] Tue, 24 March 2015 14:26 Go to previous messageGo to next message
sspn2010
Messages: 167
Registered: October 2008
Senior Member
Sorry it was typo.

1) cust_id 1 has browsed website first time on 1-Jan-2015 and made purchase on 10-Jan-2015, this purchase need to attributed to the browse date 1-Jan-2015.

2) cust_id 1 browsed again website on 1-Feb-2015 and 1-Mar-2015 and made purchases on 1-Feb-2015 (i.e. same day), 10-Feb-2015 (which are before the next browse date 1-Mar-2015) and these purchases need to be attributed to browse date 1-Feb-2015

3) Made another purchase on 1-Mar-2015 and this need to be attributed to the browse date 1-Mar-2015.


Need ouput as given below

   CUST_ID BROWSE_DT TXN_DT    NEXT_BROWSE_DT TXN_SOURCE
---------- --------- --------- -------------- ----------
         1 01-JAN-15 10-JAN-15 01-FEB-15      S         
         1 01-FEB-15 01-FEB-15                S         
         1 01-FEB-15 10-FEB-15                S         
         1 01-FEB-15 01-MAR-15                O


Thanks
SS

Report message to a moderator

Re: Query help [message #635218 is a reply to message #635217] Tue, 24 March 2015 14:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 
SQL> with browse_tbl as
  2  (select 1 as cust_id, date '2015-01-01' as browse_dt from dual
  3   union
  4   select 1 as cust_id, date '2015-02-01' as browse_dt from dual
  5   union
  6   select 2 as cust_id, date '2015-03-01' as browse_dt from dual
  7   ),
  8  sales_tbl as
  9   (select 1 as cust_id, date '2015-01-10' as txn_dt, 'S' as txn_source from dual
 10    union
 11    select 1 as cust_id, date '2015-02-01' as txn_dt, 'S' as txn_source from dual
 12    union
 13    select 1 as cust_id, date '2015-02-10' as txn_dt, 'S' as txn_source from dual
 14    union
 15    select 1 as cust_id, date '2015-03-01' as txn_dt, 'O' as txn_source from dual
 16   ),
 17  browse_tbl2 as
 18  (select cust_id, browse_dt,
 19          lead(browse_dt,1,to_date('31/12/9999','DD/MM/YYYY'))
 20            over (partition by cust_id order by browse_dt) next_browse
 21   from browse_tbl
 22  )
 23  select s.cust_id, b.browse_dt, s.txn_dt, s.txn_source
 24  from sales_tbl s, browse_tbl2 b
 25  where b.cust_id = s.cust_id
 26    and s.txn_dt >= b.browse_dt
 27    and s.txn_dt < b.next_browse
 28  order by s.cust_id, s.txn_dt
 29  /
   CUST_ID BROWSE_DT   TXN_DT      T
---------- ----------- ----------- -
         1 01-JAN-2015 10-JAN-2015 S
         1 01-FEB-2015 01-FEB-2015 S
         1 01-FEB-2015 10-FEB-2015 S
         1 01-FEB-2015 01-MAR-2015 O

4 rows selected.

Report message to a moderator

Re: Query help [message #635223 is a reply to message #635218] Tue, 24 March 2015 15:27 Go to previous message
sspn2010
Messages: 167
Registered: October 2008
Senior Member
Thank you for your help Michael!

Regards
SS

Report message to a moderator

Previous Topic: merging two queries to create a table
Next Topic: need conditional quantity insert into table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 05:45:32 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.