January 21, 2009 at 8:13 am
I've got a query that will work, but it's taking way too long. Currently, I'm creating a temporary table with a record for every second in the past 2 months. I then go back over that table and run a query assigning the number of calls with a CallStart BEFORE that second and a CalllEnd AFTER that second.
As you can imagine, this is taking some time.
Am I missing something simple? I searched scripts and forums for "Spanning", to no avail.
Diluted example (at the MINUTE level)...
Calls table:
-------------
callid, callstart, callend
-------------------------
aaa, 8:15, 8:18
bbb, 8:14, 8:20
ccc, 8:16, 8:25
I'd want to see
moment, #concurrent calls
-----------------------------
8:17, 3
8:16, 2
8:18, 2
8:19, 2
8:15, 1
8:20, 1
... or something quite like it.
Anyone? Anyone?
January 21, 2009 at 8:57 am
When you say the last 2 months, do you mean the previous 2 months (i.e. in January 2009, you are talking about November and December 2008) or the previous month and current month?
January 21, 2009 at 9:01 am
Speed on something like that will depend a lot on how you query it and how much data you are querying. Can you post the code you have so far?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 21, 2009 at 9:46 am
-- the grossly inefficient "minutely" code. Figured I'd try to get it to run fast at the minute level, then try it at seconds
declare @looper smalldatetime
declare @loopstop smalldatetime
select @looper = min(callstartdate)
from dictcall_flat
select @loopstop = max(callstartdate)
from dictcall_flat
create table #minbin (
zyear int,
zmonth smallint,
zday smallint,
zhour smallint,
zminute smallint,
numconcurrent int
)
while @looper < @loopstop
begin
insert into #minbin (zyear, zmonth, zday, zhour, zminute, numconcurrent)
select
datepart(yy,@looper) as zyear,
datepart(mm,@looper) as zmonth,
datepart(dd,@looper) as zdate,
datepart(hh,@looper) as zhour,
datepart(mi,@looper) as zmin,
count(*)
from
dictcall_flat
where
callstartdate <= @looper
and
callenddate > @looper
set @looper = dateadd(mi,1,@looper)
end
January 21, 2009 at 9:48 am
Sorry Lynn... to address your q... I'm looking in a table that already holds the earliest and latest dates. Please see the first few lines of code I just posted. It will never be more than 62 days.
Thanks for the help everyone.
January 21, 2009 at 10:45 am
First, you do not need a record for every second, you only need a record for every distinct callstart.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 21, 2009 at 10:52 am
Here's one way to do it without the temp table or any loops:
Select callstart
, (Select count(*)
From YourTable t2
Where t2.callstart <= t1.callstart
And t2.callend >= t1.callstart) as OpenCalls
From (Select distinct callstart
From YourTable) t1
probably not nearly as fast as it could be, but we really need your table, key and index definitions for that. Some sample data would help too.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 21, 2009 at 12:22 pm
... but we really need your table, key and index definitions for that. Some sample data would help too.
the table structures for this data nugget are as of yet, undefined. Point taken on "not every second" needed, but we also wanted to do metrics along the line of "% of time with no active calls", so the 0's would have to be considered somehow.
The table containing the start and end dates of each call looks like this:
CREATE TABLE [dbo].[dictcall_flat](
[call_id] [int] NOT NULL,
[CallSecs] [int] NULL,
[CallStartDate] [datetime] NULL,
[CallEndDate] [datetime] NULL,
[CallMDName] [varchar](128) NULL,
...
[CobrandID] [int] NULL,
[BillNow] [bit] NULL CONSTRAINT [DF_dictcall_flat_BillNow] DEFAULT ((0))
) ON [PRIMARY]
January 21, 2009 at 2:39 pm
Greg J (1/21/2009)
Am I missing something simple? I searched scripts and forums for "Spanning", to no avail.
Yes, I have noticed that there is something wrong with the Google search on this site. It cannot seem to find many things that I know are here.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 21, 2009 at 2:43 pm
Greg J (1/21/2009)
the table structures for this data nugget are as of yet, undefined. Point taken on "not every second" needed, but we also wanted to do metrics along the line of "% of time with no active calls", so the 0's would have to be considered somehow.
You need the callend times for the 0 points. If you want both 0's and maxs and/or you want the spans at certain values, then you will need both the callstart times and the callend times. You do not need to enumerate seconds (which depending on the data could be much slower). I cannot work on the query right now so I will have to get back to it tonight.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 21, 2009 at 11:29 pm
Here you go. this is certainly not the fastest, but my brain is not working right now.
;With calltimes as (
Select callstart as calltime From calltable
UNION
Select callend From calltable)
SELECT calltime
, (Select count(*)
From calltable t2
Where t2.callstart <= t1.calltime
And t2.callend > t1.calltime) as OpenCalls
From calltimes t1
Order by calltime
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 27, 2009 at 4:04 pm
Thanks
Here's a sample of what your query produced:
calltime Open Calls
-------------------------------------
2008-12-16 06:40:59.85724
2008-12-16 06:41:20.06025
2008-12-16 06:41:46.06026
2008-12-16 06:42:36.95727
2008-12-16 06:43:36.02329
2008-12-16 06:45:38.59330
2008-12-16 06:46:17.36328
2008-12-16 06:46:45.53729
2008-12-16 06:47:52.57328
... but what am I looking at? I don't often use the "inline Select" construct. Are the individual times when the # of OpenCalls changed?
If that's the case, this is AWESOME.
January 27, 2009 at 4:19 pm
... I'm understanding the query more fully now. After checking the numbers, I realized I needed a distinct clause.
Here's my final result. Thanks again a TON for that query RB.
SELECT
CallTime,
(SELECT COUNT(DISTINCT dc2.call_id)
FROM dictcall_flat dc2
WHERE dc2.CallStartDate < = dc1.CallTime
AND dc2.callenddate > dc1.CallTime) AS OpenCalls
--INTO
--zzzCC_testing
FROM
(SELECT callstartdate AS calltime
FROM dictcall_flat
UNION
SELECT callenddate FROM dictcall_flat) dc1
January 29, 2009 at 7:40 pm
Greg J (1/27/2009)
... I'm understanding the query more fully now. After checking the numbers, I realized I needed a distinct clause.Here's my final result. Thanks again a TON for that query RB.
SELECT
CallTime,
(SELECT COUNT(DISTINCT dc2.call_id)
FROM dictcall_flat dc2
WHERE dc2.CallStartDate < = dc1.CallTime
AND dc2.callenddate > dc1.CallTime) AS OpenCalls
--INTO
--zzzCC_testing
FROM
(SELECT callstartdate AS calltime
FROM dictcall_flat
UNION
SELECT callenddate FROM dictcall_flat) dc1
Obi-Wan has taught you well.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply