I have a SQL Server table with two datetime fields (CnxStartdatetime
, CnxEnddatetime
). Each row represents a transmission of information. I am trying to find the maximum number of concurrent transmissions based on these two timestamps. I have a working query but it is both slow and extremely cumbersome. I know there must be a better way to go about this but can't come up with any.
For the current version, if I run it with 5 "levels" and get results I have to go back and add a ton of SQL to test if there are instances of 6 concurrent transmissions, etc. Once the query gets 7-8 "levels" deep it becomes very slow.
Snippet of current version:
select
t1.id, t2.id, t3.id, t4.id, t5.id, t6.id, t7.id, t8.id, t9.id, t10.id
FROM
dbo.MyTable t1, dbo.MyTable t2, dbo.MyTable t3, dbo.MyTable t4, dbo.MyTable t5,
dbo.MyTable t6, dbo.MyTable t7, dbo.MyTable t8, dbo.MyTable t9, dbo.MyTable t10
WHERE
(((t2.cnxstartdatetime >= t1.cnxstartdatetime) and (t2.cnxstartdatetime <= t1.cnxenddatetime))
or ((t2.cnxenddatetime >= t1.cnxstartdatetime) and (t2.cnxenddatetime <= t1.cnxenddatetime)))
AND
t2.id != t1.id
AND
(((t3.cnxstartdatetime >= t2.cnxstartdatetime) and (t3.cnxstartdatetime >= t1.cnxstartdatetime)and (t3.cnxstartdatetime <= t1.cnxenddatetime) and (t3.cnxstartdatetime <= t2.cnxenddatetime))
or ((t3.cnxenddatetime >= t2.cnxstartdatetime) and (t3.cnxenddatetime >= t1.cnxstartdatetime)and (t3.cnxenddatetime <= t1.cnxenddatetime) and (t3.cnxenddatetime <= t2.cnxenddatetime)))
AND
t3.id != t2.id AND t3.id != t1.id
AND
(((t4.cnxstartdatetime >= t3.cnxstartdatetime) and (t4.cnxstartdatetime >= t1.cnxstartdatetime)and (t4.cnxstartdatetime >= t2.cnxstartdatetime) and (t4.cnxstartdatetime <= t1.cnxenddatetime) and (t4.cnxstartdatetime <= t3.cnxenddatetime)and (t4.cnxstartdatetime <= t2.cnxenddatetime))
or ((t4.cnxenddatetime >= t3.cnxstartdatetime) and (t4.cnxenddatetime >= t1.cnxstartdatetime)and (t4.cnxenddatetime >= t2.cnxstartdatetime) and (t4.cnxenddatetime <= t1.cnxenddatetime)and (t4.cnxenddatetime <= t3.cnxenddatetime)and (t4.cnxenddatetime <= t2.cnxenddatetime)))
AND
t4.id != t3.id AND t4.id != t2.id AND t4.id != t1.id
... *snip*
Edit Many of the responses are suggesting I use a cross join
. This does not achieve the results I am looking for. Here's an example of the results of the cross join
for one record's "overlaps." This is the list that it gives me for ID 11787
As you can see, 11781
does not overlap 11774
This is simply a list of any record whose time span intersects 11787
11774 2011-04-29 01:02:56.780 2011-04-29 01:02:58.793
11777 2011-04-29 01:02:56.780 2011-04-29 01:02:58.843
11778 2011-04-29 01:02:56.780 2011-04-29 01:02:58.950
11775 2011-04-29 01:02:56.793 2011-04-29 01:02:58.843
11776 2011-04-29 01:02:56.793 2011-04-29 01:02:58.890
11780 2011-04-29 01:02:58.310 2011-04-29 01:03:02.687
11779 2011-04-29 01:02:58.327 2011-04-29 01:03:02.543
11787 2011-04-29 01:02:58.530 2011-04-29 01:03:08.827 **
11781 2011-04-29 01:02:59.030 2011-04-29 01:03:05.187
11782 2011-04-29 01:02:59.247 2011-04-29 01:03:05.467
11784 2011-04-29 01:02:59.293 2011-04-29 01:03:05.810
11791 2011-04-29 01:03:00.107 2011-04-29 01:03:13.623
11786 2011-04-29 01:03:00.843 2011-04-29 01:03:08.983
11783 2011-04-29 01:03:02.560 2011-04-29 01:03:05.793
11785 2011-04-29 01:03:02.717 2011-04-29 01:03:07.357
11790 2011-04-29 01:03:05.200 2011-04-29 01:03:14.153
11804 2011-04-29 01:03:05.687 2011-04-29 01:03:25.577
11811 2011-04-29 01:03:07.093 2011-04-29 01:03:35.153
11799 2011-04-29 01:03:07.123 2011-04-29 01:03:24.437
11789 2011-04-29 01:03:08.793 2011-04-29 01:03:13.577
I've also attempted writing a CTE with recursion but I can't figure out how to insure the current ID
doesn't match any previous ID
in the current stack of concurrency. The below just recurses upon itself until it hits the limit.
WITH TransmissionConcurrency (StartTime, EndTime, ConcurrencyLevel) AS
(
SELECT
CnxStartDatetime AS StartTime,
CnxEndDatetime AS EndTime,
1 AS ConcurrencyLevel
FROM dbo.MyTable
UNION ALL
SELECT
CASE WHEN d.CnxStartDatetime > tc.StartTime THEN d.CnxStartDatetime ELSE tc.StartTime END AS StartTime,
CASE WHEN d.CnxEndDatetime < tc.EndTime THEN d.CnxEndDatetime ELSE tc.EndTime END AS EndDate,
tc.ConcurrencyLevel + 1 as ConcurrencyLevel
FROM dbo.MyTable d
INNER JOIN TransmissionConcurrency tc ON
((d.CnxStartDatetime between tc.StartTime and tc.EndTime)
or
(d.CnxEndDatetime between tc.StartTime and tc.EndTime)
or
(d.CnxStartDatetime <= tc.StartTime and d.CnxEndDatetime >= tc.EndTime))
)
SELECT *
FROM TransmissionConcurrency
ORDER BY ConcurrencyLevel, StartTime, EndTime
I've come up with the below diagram to try to better explain what I'm looking for.
A [--------]
B [-----]
C [------]
D [---]
E [---]
F [-]
In this instance, the cross join
methods would tell me that the maximum concurrency with A
is 6 (A
with B, C, D, E and F
) What I'm looking for would be a max concurrency of 3 (A
with B,F
or A
with C,E
)
Jeff. I've written a similar query once - but in Oracle - not sure whether this will work in SQL-Server, but it's worth trying: maybe it'll give you some idea:
select
t.time as b,
lead(t.time) over (order by t.time, t.weight desc) as e,
sum(t.weight) over (order by t.time, t.weight desc) as cnt
from
( select trunc(:aStartWith) as time, 0 as weight from dual
union all
select req_recieved as time, +1 as weight
from log_tbl
where trunc(req_recieved, 'mi') between :aStartWith - interval '10' minute and :aEndWith + interval '10' minute
union all
select response_sent as time, -1 as weight
from log_tbl
where trunc(req_recieved, 'mi') between :aStartWith - interval '10' minute and :aEndWith + interval '10' minute
union all
select trunc(:aEndWith) as time, 0 as weight from dual
) t
The general idea is that I go through all requests between :aStartWith
date and :aEndWith
date assigning +1 weight portion to every request that starts in the given period and -1 to every request that end in the same period.
Here I assume that requests are no longer that 10 minutes (where trunc(req_recieved, 'mi') between :aStartWith - interval '10' minute and :aEndWith + interval '10' minute
); and select ... from dual
are boundary conditions.
Then with analytic functions I find the end time of the request (lead(t.time) over (order by t.time, t.weight desc) as e
) and sum the weights for the current request - this will give a number of requests starting at time b
and ending at time e
(sum(t.weight) over (order by t.time, t.weight desc) as cnt
).
To find maximum number of requests you can just wrap this query with desired evaluations.
Could you please try if this scenario works for you? Hope it does :)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With