Home » SQL & PL/SQL » SQL & PL/SQL » Complex date differences - Bet you can't figure this one out.
Complex date differences - Bet you can't figure this one out. [message #660861] Mon, 27 February 2017 15:46 Go to next message
ertweety
Messages: 7
Registered: June 2012
Junior Member
I need help to identify the amount of time spent on a campaign using logon and logoff times. The issue I have is that I can have several logoff times that have the same logon time. I could use the maximum logoff time by campaign but sometimes the campaign will repeat itself in the day. Example of Data is attached.

I should have 4 data lines.
LOGON LOGOFF CAMPAIGN
02/24/2017 8:05:04 AM 02/24/2017 9:00:41 AM CO
02/24/2017 9:01:22 AM 02/24/2017 10:22:52 AM PR
02/24/2017 10:37:46 AM 02/24/2017 12:29:44 PM PR
02/24/2017 3:29:57 PM 02/24/2017 4:00:36 PM CO

Code to load data to a table:
CREATE TABLE hours(Logon TIMESTAMP, logoff TIMESTAMP, campaign VARCHAR(50));
INSERT INTO hours VALUES (to_date('2017-02-24 08:05:04','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 08:15:04','yyyy-mm-dd hh24:mi:ss'),'CO');
INSERT INTO hours VALUES (to_date('2017-02-24 08:05:04','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 08:25:04','yyyy-mm-dd hh24:mi:ss'),'CO');
INSERT INTO hours VALUES (to_date('2017-02-24 08:05:04','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 08:35:04','yyyy-mm-dd hh24:mi:ss'),'CO');
INSERT INTO hours VALUES (to_date('2017-02-24 08:05:04','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 08:45:04','yyyy-mm-dd hh24:mi:ss'),'CO');
INSERT INTO hours VALUES (to_date('2017-02-24 08:05:04','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 08:55:04','yyyy-mm-dd hh24:mi:ss'),'CO');
INSERT INTO hours VALUES (to_date('2017-02-24 08:05:04','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 09:00:41','yyyy-mm-dd hh24:mi:ss'),'CO');

INSERT INTO hours VALUES (to_date('2017-02-24 09:01:22','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 09:06:45','yyyy-mm-dd hh24:mi:ss'),'PR');
INSERT INTO hours VALUES (to_date('2017-02-24 09:01:22','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 09:26:24','yyyy-mm-dd hh24:mi:ss'),'PR');
INSERT INTO hours VALUES (to_date('2017-02-24 09:01:22','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 09:44:24','yyyy-mm-dd hh24:mi:ss'),'PR');
INSERT INTO hours VALUES (to_date('2017-02-24 09:01:22','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 09:48:52','yyyy-mm-dd hh24:mi:ss'),'PR');
INSERT INTO hours VALUES (to_date('2017-02-24 09:01:22','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 10:12:53','yyyy-mm-dd hh24:mi:ss'),'PR');
INSERT INTO hours VALUES (to_date('2017-02-24 09:01:22','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 10:22:52','yyyy-mm-dd hh24:mi:ss'),'PR');

INSERT INTO hours VALUES (to_date('2017-02-24 10:37:46','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 10:55:12','yyyy-mm-dd hh24:mi:ss'),'PR');
INSERT INTO hours VALUES (to_date('2017-02-24 10:37:46','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 11:00:10','yyyy-mm-dd hh24:mi:ss'),'PR');
INSERT INTO hours VALUES (to_date('2017-02-24 10:37:46','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 11:19:54','yyyy-mm-dd hh24:mi:ss'),'PR');
INSERT INTO hours VALUES (to_date('2017-02-24 10:37:46','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 11:46:49','yyyy-mm-dd hh24:mi:ss'),'PR');
INSERT INTO hours VALUES (to_date('2017-02-24 10:37:46','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 11:56:49','yyyy-mm-dd hh24:mi:ss'),'PR');
INSERT INTO hours VALUES (to_date('2017-02-24 10:37:46','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 12:06:49','yyyy-mm-dd hh24:mi:ss'),'PR');
INSERT INTO hours VALUES (to_date('2017-02-24 10:37:46','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 12:10:20','yyyy-mm-dd hh24:mi:ss'),'PR');
INSERT INTO hours VALUES (to_date('2017-02-24 10:37:46','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 12:29:44','yyyy-mm-dd hh24:mi:ss'),'PR');

INSERT INTO hours VALUES (to_date('2017-02-24 15:29:57','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 15:39:57','yyyy-mm-dd hh24:mi:ss'),'CO');
INSERT INTO hours VALUES (to_date('2017-02-24 15:29:57','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 15:49:57','yyyy-mm-dd hh24:mi:ss'),'CO');
INSERT INTO hours VALUES (to_date('2017-02-24 15:29:57','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 15:59:57','yyyy-mm-dd hh24:mi:ss'),'CO');
INSERT INTO hours VALUES (to_date('2017-02-24 15:29:57','yyyy-mm-dd hh24:mi:ss'),to_date('2017-02-24 16:00:36','yyyy-mm-dd hh24:mi:ss'),'CO');
commit;
Re: Complex date differences - Bet you can't figure this one out. [message #660864 is a reply to message #660861] Mon, 27 February 2017 16:10 Go to previous messageGo to next message
Stew Ashton
Messages: 4
Registered: February 2017
Junior Member
What's wrong with a simple GROUP BY?
select logon, max(logoff) logoff, campaign 
from hours
group by logon, campaign
order by logon, campaign;

LOGON               LOGOFF              CAMPAIGN
------------------- ------------------- --------
2017-02-24 08:05:04 2017-02-24 09:00:41 CO      
2017-02-24 09:01:22 2017-02-24 10:22:52 PR      
2017-02-24 10:37:46 2017-02-24 12:29:44 PR      
2017-02-24 15:29:57 2017-02-24 16:00:36 CO
Re: Complex date differences - Bet you can't figure this one out. [message #660872 is a reply to message #660861] Tue, 28 February 2017 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
but sometimes the campaign will repeat itself in the day.
What is the impact of this sentence on the result?
What is the rule(s) to compute the desired result?
What is the result for the data you gave?
What is your Oracle version?

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Re: Complex date differences - Bet you can't figure this one out. [message #660879 is a reply to message #660872] Tue, 28 February 2017 04:00 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
result is at the top Michel
Re: Complex date differences - Bet you can't figure this one out. [message #660880 is a reply to message #660879] Tue, 28 February 2017 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oops, didn't see it, rest the explanation of these lines and the above sentence.

Re: Complex date differences - Bet you can't figure this one out. [message #660881 is a reply to message #660861] Tue, 28 February 2017 04:31 Go to previous messageGo to next message
Stew Ashton
Messages: 4
Registered: February 2017
Junior Member
You haven't said what's wrong with a simple GROUP BY.

If my solution doesn't work, please provide test data that shows it not working.

Does the input data you are getting really include both a logon datetime and a logoff datetime? In that case, I don't see the problem with the campaign repeating itself in the same day. Could you explain in more detail, again with test data that shows the problem?

Best regards, Stew
Re: Complex date differences - Bet you can't figure this one out. [message #660888 is a reply to message #660861] Tue, 28 February 2017 08:18 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
How can you have several logoff times with only one login time? What kind of poorly designed system allows this? How can one logoff when not logged on?
Re: Complex date differences - Bet you can't figure this one out. [message #660896 is a reply to message #660888] Tue, 28 February 2017 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And to paraphrase his title: "Bet he can't figure our questions".

Re: Complex date differences - Bet you can't figure this one out. [message #660897 is a reply to message #660888] Tue, 28 February 2017 09:08 Go to previous messageGo to next message
ertweety
Messages: 7
Registered: June 2012
Junior Member
Agreed. But this is the data I have to work with.
Re: Complex date differences - Bet you can't figure this one out. [message #660898 is a reply to message #660897] Tue, 28 February 2017 09:09 Go to previous messageGo to next message
ertweety
Messages: 7
Registered: June 2012
Junior Member
I've figured it out. Thanks to all.
Re: Complex date differences - Bet you can't figure this one out. [message #660899 is a reply to message #660898] Tue, 28 February 2017 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ertweety wrote on Tue, 28 February 2017 07:09
I've figured it out. Thanks to all.
To benefit others, please post solution here.
Re: Complex date differences - Bet you can't figure this one out. [message #660900 is a reply to message #660899] Tue, 28 February 2017 09:27 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

...and give answers to our questions.

Previous Topic: online table structure changes without affecting the user
Next Topic: Today I came across different space issue in 12c DB (merged 2)
Goto Forum:
  


Current Time: Thu Mar 28 16:47:48 CDT 2024