Home » SQL & PL/SQL » SQL & PL/SQL » Query help (Oracle 12.1.0.2.0, Linux)
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
|
|
|
|
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
|
|
|
Re: Query help [message #654658 is a reply to message #654652] |
Mon, 08 August 2016 18:24 |
|
Barbara Boehmer
Messages: 9097 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.
|
|
|
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
|
|
|
Re: Query help [message #654681 is a reply to message #654680] |
Tue, 09 August 2016 14:57 |
|
Barbara Boehmer
Messages: 9097 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.
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:31:50 CDT 2024
|