Nujen Sovet kak bistro bi4islit MAX kol-vo odnovremenno zvonjashiz
v telefonnoy seti.
Vi4islenija s resolution 1 sec. Max calls output dlya 1 hour

Input table Original_tbl mojet bit o4en bolshaja ( 1 000 000 rows and more)
Ja napisal script pabotaet pravilno no ne goditsja dlya Huge data

Zaranee spasibo

Fields from Original_tbl table :

NW_ID : Telephone Network
Col011: Start Call Time
Col012: Start Call Date
Col013: End Call Time
Col014: End Call Date

NW_ID Col011 Col012 Col013 Col014
----------- --------- ----------- --------- -----------
88 10:47:02 15/11/2001 10:47:07 15/11/2001
88 10:46:21 15/11/2001 10:46:46 15/11/2001
88 10:48:43 15/11/2001 10:48:43 15/11/2001
88 10:53:06 15/11/2001 10:54:11 15/11/2001
88 10:47:56 15/11/2001 10:49:10 15/11/2001
88 10:28:33 15/11/2001 10:44:14 15/11/2001
88 10:55:44 15/11/2001 10:57:56 15/11/2001




--------------------------------------------------------
CREATE FUNCTION fn_GetAllDateTime( @NW_ID INT,
@StartCallDateTime DateTime,
@EndCallDateTime DateTime)
RETURNS @AllDateTimeTable TABLE (
NW_ID int NOT NULL,
Dt_Hr_Min_Sec DateTime NOT NULL )
AS
BEGIN
DECLARE @RunCallDateTime DateTime
SET @RunCallDateTime = @StartCallDateTime
WHILE @RunCallDateTime BETWEEN @StartCallDateTime AND @EndCallDateTime
BEGIN
INSERT INTO @AllDateTimeTable
SELECT @NW_ID,@RunCallDateTime
SET @RunCallDateTime = DATEADD(ss,1,@RunCallDateTime)
END
RETURN
END

----------------------------------------------------------

DECLARE @MinStartCallDateTime DATETIME
DECLARE @MaxEndCallDateTime DATETIME

-- ja chotel zasunut kod v UDF poetomu
-- ne polzovalsja SET DATETIMEFORMATE

SELECT @MinStartCallDateTime = MIN(CAST(SubString(Col012,7,4)+ SubString(Col012,4,2) + SubString(Col012,1,2)+' '+ LTRIM(RTRIM(Col011)) as DateTime)) ,
@MaxEndCallDateTime = MAX(CAST(SubString(Col014,7,4)+ SubString(Col014,4,2) + SubString(Col014,1,2)+' '+ LTRIM(RTRIM(Col013)) as DateTime))
FROM Original_tbl
WHERE NW_ID = 88



SELECT NW_ID,
CAST (CONVERT(VARCHAR(12),Dt_Hr_Min_Sec,103) AS SMALLDATETIME) Dt,
DATEPART(hh, Dt_Hr_Min_Sec) as Hr,
MAX(Max_Sim_Calls) AS Max_Active_Calls

FROM
(SELECT fn_G.NW_ID,fn_G.Dt_Hr_Min_Sec,Count(Org.NW_ID) AS Max_Sim_Calls
FROM [dbo].[fn_GetAllDateTime](88,@MinStartCallDateTime,@MaxEndCallDateTime) fn_G,
Original_tbl Org
WHERE Org.NW_ID = 88
AND Dt_Hr_Min_Sec BETWEEN CAST(SubString(Col012,7,4)+ SubString(Col012,4,2) + SubString(Col012,1,2)+' '+ LTRIM(RTRIM(Col011)) as DateTime)
AND CAST(SubString(Col014,7,4)+ SubString(Col014,4,2) + SubString(Col014,1,2)+' '+ LTRIM(RTRIM(Col013)) as DateTime)
GROUP BY fn_G.NW_ID,fn_G.Dt_Hr_Min_Sec) aaa
GROUP BY NW_ID, CAST (CONVERT(VARCHAR(12),Dt_Hr_Min_Sec,103) AS SMALLDATETIME),DATEPART(hh,Dt_Hr_Min_Sec)


Output :
( OUTPUT DATA NE SVJAZANA S INPUT DATA ETO TOLKO PRIMER!!!)

NW_ID Dt Hr Max_Active_Calls
---------- - -------------------- ------- -----------------------
88 2002-02-01 00:00:00 9 20
88 2002-02-01 00:00:00 10 6

Spasibo za projavlenniy interes
Poprobuyu objasnit. V 1 hour est 3600 secunds mne nujno dlja kajdoy secund nayti kol-vo
aktivnix abonentov a potom dat dlja 1 hour MAX resultat naprimer:
First 1:10:15 to 1:11:30
Second 1:10:20 to 1:12:45
Eto zna4it s 1:10:20 to 1:11:30 bilo dlja kajdoy sec 2 calls
i dlja 1:00:00 sootvetstvenno MAX 2 calls
S uvajeniem Oleg

V sutkax est 24 intervala , Naprimer
from 1:00 to 2:00 i MAX odnovremenno zvonjashix (MAX simultaneous calls)
bilo i 1:31:45 - 50 Calls. Autput budet

NW_ID Dt Hr Max_Active_Calls
----- --- --- ------------------
88 2002-02-01 1 50

Ja vprinzipe napisal script kotoriy rabotaet pravilno
problema ,4to INPUT DATA o4en bolshaa (1 000 000 rows and more)

Esli u kogo nibud est idey budu o4en rad uslishat
S uvajeniem oleg

Sorry,
V primere iz4ezli probeli mejdu polami
Itak output dlja moego primera :

Date : 2002-02-01
Hr : 1
Max_Active_Calls : 50

Spasibo

Blagodaru vas, Лука Кладбищев
Vash algorithm rabotaet obsalutno pravilno
Sey4as pishu UDF xo4u proverit na bolshom kol-ve Data
Potom soobshu resultati
Spasibo
Oleg

Dlja Лука Кладбищев
Sdelal UDF iz vashego algorithma rabotaet 4ut medlennee 4em u
menja bilo ranshe NO GORAZDO PROZRA4NEE
Esho ras spasibo
S Uvajeniem
Oleg


CREATE FUNCTION fn_calc_MAX_simultaneous_calls (@NW_ID INT,
@dt0 DateTime,
@dt1 DateTime)
RETURNS INT
AS
BEGIN

-- Example for input data
--SET @NW_ID = 88
--SET @dt0 = '20020201 10:00:00'
--SET @dt1 = '20020201 11:00:00'

declare @t table (id int identity, dt datetime, n int)

insert @t (dt, n)
select CAST(SubString(Col012,7,4)+ SubString(Col012,4,2) + SubString(Col012,1,2)+' '+ LTRIM(RTRIM(Col011)) as DateTime), 1
from Original_tbl
where (CAST(SubString(Col012,7,4)+ SubString(Col012,4,2) + SubString(Col012,1,2)+' '+ LTRIM(RTRIM(Col011)) as DateTime) between @dt0 and @dt1
or CAST(SubString(Col014,7,4)+ SubString(Col014,4,2) + SubString(Col014,1,2)+' '+ LTRIM(RTRIM(Col013)) as DateTime) between @dt0 and @dt1)
AND NW_ID = @NW_ID

insert @t (dt, n)
select CAST(SubString(Col014,7,4)+ SubString(Col014,4,2) + SubString(Col014,1,2)+' '+ LTRIM(RTRIM(Col013)) as DateTime), -1
from Original_tbl
where (CAST(SubString(Col012,7,4)+ SubString(Col012,4,2) + SubString(Col012,1,2)+' '+ LTRIM(RTRIM(Col011)) as DateTime) between @dt0 and @dt1
or CAST(SubString(Col014,7,4)+ SubString(Col014,4,2) + SubString(Col014,1,2)+' '+ LTRIM(RTRIM(Col013)) as DateTime) between @dt0 and @dt1)
AND NW_ID = @NW_ID




declare @result int
select @result = max(s) from (select (select sum(t2.n)
from @t t2
where t2.dt <= t1.dt) as s
from @t t1) as t

RETURN @result
END

2Serhiy+Snisarenko
Spasibo za link ja daje li4no znau avtora statii
on vedet u nas "Israeli SQL and OLAP user group"
ja govoru o Itzik Ben-Gan
S uvajeniem
Oleg

2tmpnick
Spasobo za interes proyavlenniy k moey teme
ja u sebja v programme poka realizoval to algoritmu deda mazaja ( ili luka kladbishev ja ponal eto odin i tot je 4elovek) vremja ot 30 sec do 1 min dlja dannix za 1 hour poetomu prixoditcja zapuskat job
Vi pravi zada4a nayti MAX nagruzku v kajdiy 4ac i ne objazatelno s4itat dlja kajdoy sec.
Ja poprobuyu ispolzovat vash algoritm i coobshu potom resultati
S uvajeniem
Oleg