Thursday, March 5, 2015

Best SQL Challenege: TSQL Query Maximum Concurrent Connections (Participants)

This is a good starting point

create table ##PhoneCalls (
PhoneID int not null
,PhoneNumber varchar(12) not null
,Callstarttime datetime2(3) not null
,CallEndtime datetime2(3) not null
)
go

truncate table ##PhoneCalls;

insert into ##PhoneCalls
values
(1,'111-111-1111',cast('2013-04-01 05:13:03.000' as datetime2(3)),cast('2013-04-01 05:13:03.000' as datetime2(3)))
,(1,'222-222-2222',cast('2013-04-01 05:15:12.000' as datetime2(3)),cast('2013-04-01 05:16:52.000' as datetime2(3)))
,(2,'333-333-3333',cast('2013-04-01 05:17:29.000' as datetime2(3)),cast('2013-04-01 05:24:08.000' as datetime2(3)))
,(2,'444-444-4444',cast('2013-04-01 05:21:50.000' as datetime2(3)),cast('2013-04-01 05:22:31.000' as datetime2(3)))
,(2,'555-555-5555',cast('2013-04-01 05:22:41.000' as datetime2(3)),cast('2013-04-01 05:23:11.000' as datetime2(3)))
,(2,'666-666-6666',cast('2013-04-01 05:23:20.000' as datetime2(3)),cast('2013-04-01 05:23:46.000' as datetime2(3)))
,(3,'555-555-5555',cast('2013-04-01 08:22:41.000' as datetime2(3)),cast('2013-04-01 10:23:11.000' as datetime2(3)))
,(3,'666-666-6666',cast('2013-04-01 08:23:20.000' as datetime2(3)),cast('2013-04-01 10:23:46.000' as datetime2(3)))

select * from ##PhoneCalls;

with cte1
AS
(
select PhoneID
,convert(date,min(Callstarttime)) [TheDate]
,min(Callstarttime) as [StartTime]
,max(CallEndtime) [EndTime]
,count(distinct(PhoneNumber))[ConcurrentConnections]
from ##PhoneCalls
group by PhoneID
)
select TheDate,datepart(hour,StartTime) [HrStart], datepart(hour,EndTime) [HrEnd],[ConcurrentConnections] from cte1;


No comments:

Post a Comment