Home » SQL & PL/SQL » SQL & PL/SQL » Date closest to event date
Date closest to event date [message #661072] Mon, 06 March 2017 17:34 Go to next message
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 #661073 is a reply to message #661072] Mon, 06 March 2017 17:43 Go to previous messageGo to next message
BlackSwan
Messages: 25796
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Date closest to event date [message #661074 is a reply to message #661073] Mon, 06 March 2017 18:11 Go to previous messageGo to next message
ertweety
Messages: 7
Registered: June 2012
Junior Member
I've googled everywhere to try and find this answer Sad

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 #661075 is a reply to message #661074] Mon, 06 March 2017 19:20 Go to previous messageGo to next message
BlackSwan
Messages: 25796
Registered: January 2009
Location: SoCal
Senior Member
#8 was supposed to point to the second URL in my first response & now has been correct.

Please provide CREATE TABLE statement & INSERT statements so we can have your tables & data to work against.
Re: Date closest to event date [message #661076 is a reply to message #661072] Mon, 06 March 2017 19:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8790
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

Re: Date closest to event date [message #661077 is a reply to message #661076] Mon, 06 March 2017 20:44 Go to previous message
ertweety
Messages: 7
Registered: June 2012
Junior Member
Thanks so much for the help.
Previous Topic: Analytical query
Next Topic: How to split col into two columns according to data types
Goto Forum:
  


Current Time: Fri Jan 19 16:37:41 CST 2018

Total time taken to generate the page: 0.01327 seconds