1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Complex date differences

Discussion in 'SQL PL/SQL' started by ertweety, Feb 27, 2017.

  1. ertweety

    ertweety Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    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 end up with 4 records.

    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:
    Code (SQL):
    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;
     
     

    Attached Files:

    Last edited: Feb 27, 2017
    ertweety, Feb 27, 2017
    #1