Logon/Logoff Time Difference

Transact-SQL
1

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. See example of data below.

LOGON LOGOFF CAMPAIGN
02/24/2017 8:05:04 AM 02/24/2017 8:15:04 AM CO
02/24/2017 8:05:04 AM 02/24/2017 8:25:04 AM CO
02/24/2017 8:05:04 AM 02/24/2017 8:35:04 AM CO
02/24/2017 8:05:04 AM 02/24/2017 8:45:04 AM CO
02/24/2017 8:05:04 AM 02/24/2017 8:55:04 AM CO
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 9:06:45 AM PR
02/24/2017 9:01:22 AM 02/24/2017 9:26:24 AM PR
02/24/2017 9:01:22 AM 02/24/2017 9:44:24 AM PR
02/24/2017 9:01:22 AM 02/24/2017 9:48:52 AM PR
02/24/2017 9:01:22 AM 02/24/2017 10:12:53 AM PR
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 10:55:12 AM PR
02/24/2017 10:37:46 AM 02/24/2017 11:00:10 AM PR
02/24/2017 10:37:46 AM 02/24/2017 11:19:54 AM PR
02/24/2017 10:37:46 AM 02/24/2017 11:46:49 AM PR
02/24/2017 10:37:46 AM 02/24/2017 11:56:49 AM PR
02/24/2017 10:37:46 AM 02/24/2017 12:06:49 PM PR
02/24/2017 10:37:46 AM 02/24/2017 12:10:20 PM PR
02/24/2017 10:37:46 AM 02/24/2017 12:29:44 PM PR
02/24/2017 1:02:26 PM 02/24/2017 1:02:50 PM EV
02/24/2017 1:50:23 PM 02/24/2017 2:00:23 PM EV
02/24/2017 1:50:23 PM 02/24/2017 2:10:23 PM EV
02/24/2017 1:50:23 PM 02/24/2017 2:14:58 PM EV
02/24/2017 2:30:26 PM 02/24/2017 2:30:43 PM EV
02/24/2017 2:30:44 PM 02/24/2017 2:31:02 PM EV
02/24/2017 2:32:47 PM 02/24/2017 2:42:47 PM EV
02/24/2017 2:32:47 PM 02/24/2017 2:52:47 PM EV
02/24/2017 2:32:47 PM 02/24/2017 3:02:47 PM EV
02/24/2017 2:32:47 PM 02/24/2017 3:12:47 PM EV
02/24/2017 2:32:47 PM 02/24/2017 3:22:47 PM EV
02/24/2017 2:32:47 PM 02/24/2017 3:28:59 PM EV
02/24/2017 3:29:57 PM 02/24/2017 3:39:57 PM CO
02/24/2017 3:29:57 PM 02/24/2017 3:49:57 PM CO
02/24/2017 3:29:57 PM 02/24/2017 3:59:57 PM CO
02/24/2017 3:29:57 PM 02/24/2017 4:00:36 PM CO

For the CO campaign, I should have 2 data lines.
LOGON LOGOFF CAMPAIGN
02/24/2017 8:05:04 AM 02/24/2017 9:00:41 AM CO
02/24/2017 3:29:57 PM 02/24/2017 4:00:36 PM CO

2

please post your data as follows

create table #ertweety(Logon datetime, logoff datetime, campaign varchar(50))
insert into #ertweety
select '02/24/2017 8:05:04 AM',	'02/24/2017 8:15:04 AM', 	'CO'
3

drop table hours;
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;