March 4, 2015 at 1:15 am
Hi All,
I have a table called dbo.PhoneCalls with below columns
PhoneID |PhoneNumber| Callstarttime| CallEndtime|
1 |111-111-1111|2013-04-01 05:13:03.000|2013-04-01 05:13:03.000
1 |222-222-2222|2013-04-01 05:15:12.000|2013-04-01 05:16:52.000
2 |333-333-3333|2013-04-01 05:17:29.000|2013-04-01 05:24:08.000
2 |444-444-4444|2013-04-01 05:21:50.000|2013-04-01 05:22:31.000
2 |555-555-5555|2013-04-01 05:22:41.000|2013-04-01 05:23:11.000
2 |666-666-6666|2013-04-01 05:23:20.000|2013-04-01 05:23:46.000
..........
1. PhoneID is nothing but the participant in the call. PhoneID = 1 is twice from above. Which means 2 particpants (Same call )with 2 numbers with their callstarttime and callendtime. Similarly for PhoneID =2, there are 4 participants. And the list goes on for a day and then for a month.
2. For example a phone call P1 with 2 participants is going on for a particular day. We should not consider the same phone call having 2 participants involved. So that the concurrency would be 2. We have to ignore that here.
3. Only to be considered is other Phone calls for that day. Lets say P1 having call with 2 participants, P2 having some 4 participants which fall in the time period of P1. Then we should consider P1 and P2 the common period
4. In order to find number of concurrent calls happened for a day basing on callstarttime and callendtime. What would be the query?
5. Should consider the Timeperiod or the bucket with 1 hour as the period.
6. A Phone Call P1, Phone Call P2, should have matching (common) time ( keeping all the scenarios) is required for this query.
Result for Concurrent calls for a day should be like below. Should get all the concurrent connections happened for a particular day.
Date|TimePeriod/Bucket(hr part)|Concurrentconnections|
Jan-01-2015|01 to 02|3
Jan-01-2015|11 to 12|2
Jan-02-2015|04 to 05|5
Jan-02-2015|12 to 13|13
........
ii) So once the above is achieved.
Have to find the Maximum concurrent connections for day from the above.
For below Maximum Concurrent connections are '3'
Date|TimePeriod/Bucket(hr part)|Concurrentconnections|
Jan-01-2015|01 to 02|3
Jan-01-2015|11 to 12|2
Hence the Result for Maximum Concurrent Connections would be
Date|TimePeriod/Bucket(hr part)|MaxConcurrentconnections|
Jan-01-2015|01 to 02|3
Jan-02-2015|12 to 13|13
.............
March 4, 2015 at 7:11 am
Not sure I got it right, but lets start with enumerating buckets of interest. If the query brings the proper list of buckets with concurrent calls then we can proceed to calculating day statistics
with sampledata as(
Select * from
(values
(1,'111-111-1111',cast('2013-04-01 05:13:03.000' as datetime),cast('2013-04-01 06:22:03.000'as datetime))
,(1,'222-222-2222','2013-04-01 05:15:12.000','2013-04-01 05:16:52.000')
,(2,'333-333-3333','2013-04-01 05:17:29.000','2013-04-01 06:24:08.000')
,(2,'444-444-4444','2013-04-01 05:21:50.000','2013-04-01 05:22:31.000')
,(2,'555-555-5555','2013-04-01 05:22:41.000','2013-04-01 05:23:11.000')
,(2,'666-666-6666','2013-04-01 05:23:20.000','2013-04-01 05:23:46.000')
) T(PhoneID ,PhoneNumber, Callstarttime, CallEndtime)
), conc as (
select s1.PhoneID as pId1, s1.PhoneNumber as pN1, s2.PhoneID as pId2, s2.PhoneNumber as pN2
,case when s1.Callstarttime > s2.Callstarttime then s1.Callstarttime else s2.Callstarttime end as st
,case when s1.CallEndtime < s2.CallEndtime then s1.CallEndtime else s2.CallEndtime end as et
from sampledata s1
join sampledata s2 on s1.PhoneID < s2.PhoneID
and s1.Callstarttime < s2.CallEndtime and s2.Callstarttime < s1.CallEndtime
), concBuckets as (
select conc.*, dateadd(hour,datediff(hour,0,st)+dn,0) as bucketStart
from conc
cross apply ( -- replace it with subquery using tally table or TVF at hand
select top (datediff(hour, st, et) + 1)
dn = row_number() over (order by (select null)) - 1
from sys.all_objects) as buckets
)
select distinct pid1, pid2, bucketStart
from concBuckets
March 4, 2015 at 8:00 am
Alternatively we can count concurrent calls based on second-granularity tally and then compute max concurrency within bucket.
declare @repStart datetime = '2013-04-01';
declare @repEnd datetime = '2013-04-02 23:59:59.9';
with sampledata as(
Select * from
(values
(1,'111-111-1111',cast('2013-04-01 05:13:03.000' as datetime),cast('2013-04-01 06:22:03.000'as datetime))
,(1,'222-222-2222','2013-04-01 05:15:12.000','2013-04-01 05:16:52.000')
,(2,'333-333-3333','2013-04-01 05:17:29.000','2013-04-01 06:24:08.000')
,(2,'444-444-4444','2013-04-01 05:21:50.000','2013-04-01 05:22:31.000')
,(2,'555-555-5555','2013-04-01 05:22:41.000','2013-04-01 05:23:11.000')
,(2,'666-666-6666','2013-04-01 05:23:20.000','2013-04-01 05:23:46.000')
) T(PhoneID ,PhoneNumber, Callstarttime, CallEndtime)
), secTally as(
select top(datediff(second
, @repStart
, @repEnd ))
dateadd(second
, row_number() over (order by (select null)) - 1
, @repStart ) as secStart
from sys.all_objects s1 ,sys.all_objects s2
), concSeconds as (
select secStart, dateadd(hour,datediff(hour,0, secStart),0) as bucketStart
, count(distinct PhoneID) as cnt
from sampledata s1
join secTally t on s1.Callstarttime <= t.secStart and s1.CallEndtime >t.secStart
group by secStart, dateadd(hour,datediff(hour,0, secStart),0)
)
select bucketStart, max(cnt) as cnt
from concSeconds
group by bucketStart;
March 5, 2015 at 2:36 pm
Itzek Ben-Gan has some clever SQL Server 2012 and non-2012 solutions for this type of problem in his book, Microsoft® SQL Server® 2012 High-Performance T-SQL Using Window Functions
I have not read it entirely but he also covers this problem in his four part series:
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply