February 5, 2008 at 12:54 pm
I have a table of events that has a start time and a duration. I need to calculate the maximum number of concurrent events.
eventid uniqueidentifier
calldate numeric YYYYMMDD
calltime numeric HHMM
duration varchar hh:mm:ss
I can calculate the start and end times in a date format and I am going down the path of a tally table with an entry for each minute of each day and joining where the entries in the tally table are between the start and end times and then summing up the counts by the tally table entries.
I am hoping for an easier less machine intensive method if anyone has one.
February 5, 2008 at 1:30 pm
Think of using the tally table has merit, but I'm thinking your using that hammer on the wrong kind of nail.
How about populating a temp table with a minute by minute increment for each phone call. You could then do a grouped count by the minute increment.
something like:
--create table events(evtid int identity(1,1) primary key clustered,
--startd datetime,
--endd datetime,
--duration AS datediff(minute,startd,endd) persisted
--)
insert events (startd,endd)
select '1/12/2006 14:01:32','1/15/2006 06:42:15' UNION ALL
select '1/12/2006 14:01:32','1/13/2006 06:42:15' UNION ALL
select '1/12/2006 15:01:32','1/12/2006 16:42:15'
go
drop table #temp;
select
evtID,
startd,
dateadd(second,
-datepart(second,startd),
dateadd(ms,
-datepart(ms,startd),
dateadd(minute,N,startd)
)
) evttimemarker
into #temp
from events cross join tally
where tally.n<events.duration
create index ix_tmp on #temp(evttimemarker,evtid)
select evttimemarker,count(evtID)
from #temp
group by evttimemarker
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 5, 2008 at 2:38 pm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply