OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Query help (Oracle 12.1.0.2.0, Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query help [message #654652]

Mon, 08 August 2016 13:48

sspn2010
Messages: 167
Registered: October 2008

Senior Member

Hi All,

I need help from experts on the below query.

I've below 2 tables, one table contains when the customer visited the website and the 2nd table contains when the customer made the transaction. Now i need to link the transaction to the website visit record.


VISIT Table
=============

with visit_tbl as
(select 1234 as cust_id, to_date('8/14/2015 4:49:32 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('8/27/2015 7:38:39 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('10/16/2015 6:59:09 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('11/24/2015 12:44:18 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('3/24/2016  9:59:06 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('7/16/2016  2:03:31 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
)
select * from visit_tbl;

CUST_ID     VISIT_DATE
----------  -----------------
1234        8/14/2015 4:49:32 PM
1234        8/27/2015 7:38:39 PM
1234        10/16/2015 6:59:09 PM
1234        11/24/2015 12:44:18 PM
1234        3/24/2016 9:59:06 PM
1234        7/16/2016 2:03:31 PM


TRANSACTION Table
==================

with txn_data as
(select 1234 as cust_id, to_date('10/25/2015  3:41:52 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 union
 select 1234 as cust_id, to_date('12/3/2015  3:30:04 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 union
 select 1234 as cust_id, to_date('7/9/2016  3:29:13 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
)
select * from txn_data;

CUST_ID     TXN_DATE
----------  ----------------
1234        10/25/2015 3:41:52 PM
1234        12/3/2015 3:30:04 PM
1234        7/9/2016 3:29:13 PM

Cust ID 1234 has visited website on 8/14/2015 and 8/27/2015 and had transaction on 10/25/2015, so need to attribute this to the earliest website visit record which is 8/14/2015, on 11/24/2015 again customer visited website and had transaction on 12/3/2015 and need to attribute this transaction to 11/24/2015 visit and attribute transaction happened on 7/9/2016 3:29:13 PM to the website visit record 3/24/2016 9:59:06 PM.


Output:

CUST_ID     VISIT_DATE              TXN_DATE
----------  ----------------        -----------------
1234        8/14/2015 4:49:32 PM    10/25/2015 3:41:52 PM
1234        11/24/2015 12:44:18 PM  12/3/2015 3:30:04 PM
1234        3/24/2016 9:59:06 PM     7/9/2016 3:29:13 PM

I've tried below one but it's not giving the required output. Can someone please help me out.


with visit_tbl as
(select 1234 as cust_id, to_date('8/14/2015 4:49:32 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('8/27/2015 7:38:39 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('10/16/2015 6:59:09 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('11/24/2015 12:44:18 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('3/24/2016  9:59:06 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('7/16/2016  2:03:31 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
),
txn_data as
(select 1234 as cust_id, to_date('10/25/2015  3:41:52 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 union
 select 1234 as cust_id, to_date('12/3/2015  3:30:04 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 union
 select 1234 as cust_id, to_date('7/9/2016  3:29:13 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
)
select a.cust_id, a.visit_date, b.txn_date from visit_tbl a, txn_data b where a.cust_id = b.cust_id and a.visit_date <= b.txn_date
 order by 2,3;

Appreciate your help!

Thanks
SS

Report message to a moderator

Re: Query help [message #654656 is a reply to message #654652]

Mon, 08 August 2016 15:29

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Is this not the same question than in your previous Query help?

Report message to a moderator

Re: Query help [message #654657 is a reply to message #654656]

Mon, 08 August 2016 15:32

sspn2010
Messages: 167
Registered: October 2008

Senior Member

Hi Michael,

Yes, this is similar to that question, but in that question need to attribute the transaction to the latest web site visit date but here it's to the earliest date.

Thanks
SS

Report message to a moderator

Re: Query help [message #654658 is a reply to message #654652]

Mon, 08 August 2016 18:24

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

The following is based more on your desired results.

SCOTT@orcl_12.1.0.2.0> with visit_tbl as
  2  (select 1234 as cust_id, to_date('8/14/2015 4:49:32 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
  3   union
  4   select 1234 as cust_id, to_date('8/27/2015 7:38:39 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
  5   union
  6   select 1234 as cust_id, to_date('10/16/2015 6:59:09 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
  7   union
  8   select 1234 as cust_id, to_date('11/24/2015 12:44:18 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
  9   union
 10   select 1234 as cust_id, to_date('3/24/2016  9:59:06 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 11   union
 12   select 1234 as cust_id, to_date('7/16/2016  2:03:31 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 13  ),
 14  txn_data as
 15  (select 1234 as cust_id, to_date('10/25/2015  3:41:52 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 16   union
 17   select 1234 as cust_id, to_date('12/3/2015  3:30:04 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 18   union
 19   select 1234 as cust_id, to_date('7/9/2016  3:29:13 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 20  )
 21  select a.cust_id, min (a.visit_date) visit_date, b.txn_date
 22  from   visit_tbl a, txn_data b
 23  where  a.cust_id = b.cust_id
 24  and    a.visit_date >
 25  	    (select nvl (max (b2.txn_date), a.visit_date - 1)
 26  	     from   txn_data b2
 27  	     where  b2.txn_date < b.txn_date)
 28  group  by a.cust_id, b.txn_date
 29  order  by 2, 3
 30  /

   CUST_ID VISIT_DATE             TXN_DATE
---------- ---------------------- ----------------------
      1234 08/14/2015 04:49:32 PM 10/25/2015 03:41:52 PM
      1234 11/24/2015 12:44:18 PM 12/03/2015 03:30:04 PM
      1234 03/24/2016 09:59:06 PM 07/09/2016 03:29:13 PM

3 rows selected.

Report message to a moderator

Re: Query help [message #654680 is a reply to message #654658]

Tue, 09 August 2016 10:57

sspn2010
Messages: 167
Registered: October 2008

Senior Member

Thank you Barbara!

If there are 2 transactions after one visit date only the earliest need to be attributed to the visit date. Let's say if there is one more transaction on 12/15/2015 which after the visit 11/24/2015 then there will be 2 transactions one on 12/3/2015 and other on 12/15/2015, only 12/3/2015 transaction need to be attributed to 11/24/2015 visit date, but the above query is returning one record for each transaction date. I know it's my bad, i haven't given the test data properly.


with visit_tbl as
(select 1234 as cust_id, to_date('8/14/2015 4:49:32 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('8/27/2015 7:38:39 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('10/16/2015 6:59:09 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('11/24/2015 12:44:18 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('3/24/2016  9:59:06 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('7/16/2016  2:03:31 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
),
txn_data as
(select 1234 as cust_id, to_date('10/25/2015  3:41:52 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 union
 select 1234 as cust_id, to_date('12/3/2015  3:30:04 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 union
 select 1234 as cust_id, to_date('12/15/2015  3:30:04 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 union
 select 1234 as cust_id, to_date('7/9/2016  3:29:13 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 union
 select 1234 as cust_id, to_date('7/15/2016  2:49:53 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
)
select a.cust_id, min (a.visit_date) visit_date, b.txn_date
   from   visit_tbl a, txn_data b
   where  a.cust_id = b.cust_id
   and    a.visit_date >
           (select nvl (max (b2.txn_date), a.visit_date - 1)
            from   txn_data b2
            where  b2.txn_date < b.txn_date)
   group  by a.cust_id, b.txn_date
   order  by 2, 3;


CUST_ID       VISIT_DATE                 TXN_DATE
-----------  ------------------------    ---------------------------
1234          8/14/2015 4:49:32 PM       10/25/2015 3:41:52 PM
1234         11/24/2015 12:44:18 PM      12/3/2015 3:30:04 PM
1234          3/24/2016 9:59:06 PM       12/15/2015 3:30:04 PM
1234          3/24/2016 9:59:06 PM        7/9/2016 3:29:13 PM
1234          7/16/2016 2:03:31 PM        7/15/2016 2:49:53 PM

I've modified query slightly, please let me know if it makes sense.


with visit_tbl as
(select 1234 as cust_id, to_date('8/14/2015 4:49:32 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('8/27/2015 7:38:39 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('10/16/2015 6:59:09 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('11/24/2015 12:44:18 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('3/24/2016  9:59:06 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 union
 select 1234 as cust_id, to_date('7/16/2016  2:03:31 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
),
txn_data as
(select 1234 as cust_id, to_date('10/25/2015  3:41:52 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 union
 select 1234 as cust_id, to_date('12/3/2015  3:30:04 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 union
 select 1234 as cust_id, to_date('12/15/2015  3:30:04 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 union
 select 1234 as cust_id, to_date('7/9/2016  3:29:13 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 union
 select 1234 as cust_id, to_date('7/15/2016  2:49:53 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
)
select cust_id, visit_date, max(txn_date) as txn_date from (
select a.cust_id, min (a.visit_date) visit_date, b.txn_date
   from   visit_tbl a, txn_data b
   where  a.cust_id = b.cust_id
   and    a.visit_date >
           (select nvl (max (b2.txn_date), a.visit_date - 1)
            from   txn_data b2
            where  b2.txn_date < b.txn_date)
   group  by a.cust_id, b.txn_date
   ) where visit_date < txn_date 
   group by cust_id, visit_date
   order  by 2, 3;

CUST_ID       VISIT_DATE                 TXN_DATE
-----------  ------------------------    ---------------------------
1234         8/14/2015 4:49:32 PM        10/25/2015 3:41:52 PM
1234         11/24/2015 12:44:18 PM      12/3/2015 3:30:04 PM
1234         3/24/2016 9:59:06 PM        7/9/2016 3:29:13 PM

Thanks
SS

Report message to a moderator

Re: Query help [message #654681 is a reply to message #654680]

Tue, 09 August 2016 14:57

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

That's one way. I would be inclined instead to include an additional condition, like the one in upper case on line 28 below, that probably should have been in the original query and would have avoided the issue. I got so focused on making sure that the visit date was the first one after the previous transaction date that I forgot about making sure the visit date was on or before the current transaction and not after.

SCOTT@orcl_12.1.0.2.0> with visit_tbl as
  2  (select 1234 as cust_id, to_date('8/14/2015 4:49:32 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
  3   union
  4   select 1234 as cust_id, to_date('8/27/2015 7:38:39 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
  5   union
  6   select 1234 as cust_id, to_date('10/16/2015 6:59:09 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
  7   union
  8   select 1234 as cust_id, to_date('11/24/2015 12:44:18 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
  9   union
 10   select 1234 as cust_id, to_date('3/24/2016  9:59:06 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 11   union
 12   select 1234 as cust_id, to_date('7/16/2016  2:03:31 PM','MM/DD/YYYY HH:MI:SS AM') as visit_date from dual
 13  ),
 14  txn_data as
 15  (select 1234 as cust_id, to_date('10/25/2015  3:41:52 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 16   union
 17   select 1234 as cust_id, to_date('12/3/2015  3:30:04 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 18   union
 19   select 1234 as cust_id, to_date('12/15/2015  3:30:04 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 20   union
 21   select 1234 as cust_id, to_date('7/9/2016  3:29:13 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 22   union
 23   select 1234 as cust_id, to_date('7/15/2016  2:49:53 PM','MM/DD/YYYY HH:MI:SS AM') as txn_date from dual
 24  )
 25  select a.cust_id, min (a.visit_date) visit_date, b.txn_date
 26  from   visit_tbl a, txn_data b
 27  where  a.cust_id = b.cust_id
 28  AND    A.VISIT_DATE <= B.TXN_DATE
 29  and    a.visit_date >
 30  	    (select nvl (max (b2.txn_date), a.visit_date - 1)
 31  	     from   txn_data b2
 32  	     where  b2.txn_date < b.txn_date)
 33  group  by a.cust_id, b.txn_date
 34  order  by 2, 3
 35  /

   CUST_ID VISIT_DATE             TXN_DATE
---------- ---------------------- ----------------------
      1234 08/14/2015 04:49:32 PM 10/25/2015 03:41:52 PM
      1234 11/24/2015 12:44:18 PM 12/03/2015 03:30:04 PM
      1234 03/24/2016 09:59:06 PM 07/09/2016 03:29:13 PM

3 rows selected.

Report message to a moderator

Re: Query help [message #654682 is a reply to message #654681]

Tue, 09 August 2016 16:21

sspn2010
Messages: 167
Registered: October 2008

Senior Member

Great! Thank you Barbara!

SS

Report message to a moderator

Previous Topic:	Check if value EXISTS in plsql table of record type
Next Topic:	Dynamic Update statement Error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 09:55:48 CDT 2024