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 |
|
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;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Mar 29 04:08:15 CDT 2024
|