| Date closest to event date [message #661072] |
Mon, 06 March 2017 17:34  |
 |
ertweety
Messages: 7
Registered: June 2012
|
Junior Member |
|
|
Customer is unique.
Customer can only receive one text per day but customer can call us anytime. Every text dt is matching to a call for customer if call dt is greater than text dt.
Customer. Text_dt. Call_dt
Bob. 01/31/2017 8:27am. 02/01/2017. 10:00am
Bob. 01/31/2017 8:27am. 02/05/2017. 8:00pm
Bob 02/01/2017 8:54am 02/01/2017. 10:00am
Bob. 02/01/2017 8:54am. 02/05/2017. 8:00pm
Bob. 02/03/2017. 9:00am. 02/05/2017 8:00pm
Assumption is that the first call from the customer after text is related to that text.
Expecting data to look like the following
Customer. Text_dt. Call_dt
Bob. 01/31/2017 8:27am.
Bob. 02/01/2017 8:54am. 02/01/2017 10:00am
Bob. 02/03/2017 9:00am. 02/05/2017. 8:00pm
01/31/2017 is blank because the 02/01/2017 call date is closer to the text that was sent on the same day.
[mod-edit: code tags added by bb]
[Updated on: Mon, 06 March 2017 19:16] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Date closest to event date [message #661074 is a reply to message #661073] |
Mon, 06 March 2017 18:11  |
|
ertweety
Messages: 7
Registered: June 2012
|
Junior Member |
|
|
I've googled everywhere to try and find this answer 
I am using oracle 11.2.0.3.0
The code I am trying is
SELECT *
FROM (
SELECT customer,
call_dt,
Rank() over (PARTITION BY call_dt ORDER BY call_dt)
FROM TABLE
where rank=1;
I tried following the guidelines you sent. I clicked on 'Formatted' under #8 to see how to post the code but the link is broken. Just an FYI.
*BlackSwan corrected {code} tags & provided correctly indented code
[Updated on: Mon, 06 March 2017 19:18] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Date closest to event date [message #661076 is a reply to message #661072] |
Mon, 06 March 2017 19:40 |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- The following are create table and insert statements for sample data, like what we expect you to provide in the future:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE your_table
2 ( customer VARCHAR2(8)
3 , text_dt DATE
4 , call_dt DATE)
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT ALL
2 INTO your_table VALUES ('Bob', TO_DATE ('01/31/2017 8:27am', 'MM/DD/YYYY HH:MIAM'),
3 TO_DATE ('02/01/2017 10:00am', 'MM/DD/YYYY HH:MIAM'))
4 INTO your_table VALUES ('Bob', TO_DATE ('01/31/2017 8:27am', 'MM/DD/YYYY HH:MIAM'),
5 TO_DATE ('02/05/2017 8:00pm', 'MM/DD/YYYY HH:MIAM'))
6 INTO your_table VALUES ('Bob', TO_DATE ('02/01/2017 8:54am', 'MM/DD/YYYY HH:MIAM'),
7 TO_DATE ('02/01/2017 10:00am', 'MM/DD/YYYY HH:MIAM'))
8 INTO your_table VALUES ('Bob', TO_DATE ('02/01/2017 8:54am', 'MM/DD/YYYY HH:MIAM'),
9 TO_DATE ('02/05/2017 8:00pm', 'MM/DD/YYYY HH:MIAM'))
10 INTO your_table VALUES ('Bob', TO_DATE ('02/03/2017 9:00am', 'MM/DD/YYYY HH:MIAM'),
11 TO_DATE ('02/05/2017 8:00pm', 'MM/DD/YYYY HH:MIAM'))
12 SELECT * FROM DUAL
13 /
5 rows created.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM your_table ORDER BY customer, text_dt, call_dt
2 /
CUSTOMER TEXT_DT CALL_DT
-------- ------------------ ------------------
Bob 01/31/2017 08:27am 02/01/2017 10:00am
Bob 01/31/2017 08:27am 02/05/2017 08:00pm
Bob 02/01/2017 08:54am 02/01/2017 10:00am
Bob 02/01/2017 08:54am 02/05/2017 08:00pm
Bob 02/03/2017 09:00am 02/05/2017 08:00pm
5 rows selected.
-- The following is one query that produces the desired results. There are always various methods to choose from.
-- This assumes that your text_dt and call_dt columns are of date data type as they should be.
SCOTT@orcl_12.1.0.2.0> SELECT customer, text_dt,
2 CASE WHEN MIN(call_dt) < LEAD(text_dt) OVER (PARTITION BY customer ORDER BY text_dt)
3 OR LEAD(text_dt) OVER (PARTITION BY customer ORDER BY text_dt) IS NULL
4 THEN MIN(call_dt)
5 ELSE NULL
6 END AS call_dt
7 FROM your_table
8 WHERE call_dt > text_dt
9 GROUP BY customer, text_dt
10 ORDER BY customer, text_dt
11 /
CUSTOMER TEXT_DT CALL_DT
-------- ------------------ ------------------
Bob 01/31/2017 08:27am
Bob 02/01/2017 08:54am 02/01/2017 10:00am
Bob 02/03/2017 09:00am 02/05/2017 08:00pm
3 rows selected.
[Updated on: Mon, 06 March 2017 19:42] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
