Displaying Maximum in every 12 and 6 second window

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Displaying Maximum in every 12 and 6 second window

Author

Topic

rgrover
Starting Member

4 Posts

Posted - 2013-08-09 : 06:17:11

HI I have one requirement in which i want maximum value of every six (1-6) seconds. If difference of time between the time value of maximum value of subsequent window of six (i.e window 1-6 and window 7-12) is <=4 then we should ignore the value of window 1 and check for next to maximum in window 1 and so on respectively. Suppose we get maximum value in window 1 at 4th second and for window window 2 at 10th second for first window of 12. Then while trying for window 3(13-18) if maximum value is at 13 then we we calculate 13 minus 10(13-10=3)which is less than 4 then we should ignore value at 10th second and should look for value next to maximum in window 2. and same process goes on till end. Sample Data is shown below and it is sorted with time. I need to check only for those values which have accevalue between .01 and .74 so we can apply a filter on that we will have to preserver every not value for every six second window for back tracking. I am working on this issue for many days but could not find exact solution to this problem. It would be highly beneficials to have inputs from experts

JourneyID TimeSpend AcceValue
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 16:58.0 0.06
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:02.0 0.01
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:04.0 -0.04
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:06.0 -0.02
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:09.0 0.02
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:11.0 0.02
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:13.0 0.01
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:15.0 -0.03
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:18.0 0.11
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:20.0 0.12
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:23.0 0.23
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:26.0 0.09
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:28.0 0.15
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:31.0 0.06
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:34.0 -0.02
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:36.0 -0.17
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:39.0 -0.27
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:42.0 -0.16
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:44.0 -0.13
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:47.0 -0.02
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:49.0 0.03
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:51.0 0.19
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:53.0 0.07
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:56.0 0.06
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:58.0 0.04
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 18:00.0 0.17
33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 18:03.0 0.3

Thanks & Regards
Ritesh Grover

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-09 : 07:02:34

Which version of SQL Server are you using?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

rgrover
Starting Member

4 Posts

Posted - 2013-08-09 : 07:15:52

Hi I am Using Sql Server 2008 R2

Ritesh Grover
Senior Database Developer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-09 : 07:50:25

see

http://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rgrover
Starting Member

4 Posts

Posted - 2013-08-09 : 09:03:00

Hi Visakh, thanks for providing input but this is not as as per our requirement.

Thanks & Regards
Ritesh Grover

rgrover
Starting Member

4 Posts

Posted - 2013-08-12 : 05:15:45

Hi I have done something like this but this is not the end solution

Declare @initialtime datetime
DECLARE @maximumTime datetime
Declare @Minaccvalue decimal (15,2)
Declare @Maxaccvalue decimal(15,2)
set @Minaccvalue=0.01
SET @Maxaccvalue=0.74
declare @initialmaxvalue decimal (15,2)
Declare @InitialSno int =0
DECLARE @cycle int
DECLARE @SNO Int
SET @cycle =1
SET @SNO=1
Declare @addonesecond int=1
declare @nextmax decimal(15,2)
SET @initialtime =(select top(1) TimeSpend from JourneyLocationsSummary
where JourneyID='33946F55-DACF-42D7-8E6E-5EE0D9E96F6E' and AcceValue between .01 and .74
order by TimeSpend asc)

SET @maximumTime = (select top(1) TimeSpend from JourneyLocationsSummary
where JourneyID='33946F55-DACF-42D7-8E6E-5EE0D9E96F6E' and AcceValue between .01 and .74
order by TimeSpend desc)

set @initialmaxvalue= @Minaccvalue

drop table #record
create table #record
(
sno INT, cycle INT, Time DATETIME,
Value decimal(6,2), MaxValue VARCHAR(20)
)

drop table #record1
create table #record1
(
sno INT, cycle INT, Time DATETIME,
Value decimal(6,2), MaxValue VARCHAR(20),Rank int
)

drop table #record2
create table #record2
(
Diffdate int ,sno INT, cycle INT, Time DATETIME,
Value decimal(6,2), MaxValue VARCHAR(20),Rank int
)

while @initialtime< @maximumTime
BEGIN
select @nextmax = (SELECT top(1)AcceValue FROM JourneyLocationsSummary where
JourneyID='33946F55-DACF-42D7-8E6E-5EE0D9E96F6E' and
TimeSpend=@initialtime
)

IF @initialmaxvalue = @nextmax
BEGIN
SELECT @initialmaxvalue=@initialmaxvalue
INSERT INTO #record
SELECT @sno AS SNO, @Cycle AS Cycle, @initialtime as time,
@NEXTMAX as value, 'Initial value' as MAXVALUE
END

If @nextmax is null
BEGIN
SELECT @NEXTMAX=@INITIALMAXVALUE
INSERT INTO #record
SELECT @sno AS SNO, @Cycle AS Cycle,@initialtime as time,null as value,
'Null Value' as MAXVALUE

END
ELSE
IF @initialmaxvalue < @nextmax
BEGIN
select @initialmaxvalue=@nextmax
INSERT INTO #record
SELECT @sno AS SNO, @Cycle AS Cycle, @initialtime as time,
@NEXTMAX as value,'Greater Value' as MAXVALUE
END
ELSE
IF @initialmaxvalue > @nextmax
BEGIN
SELECT @initialmaxvalue=@initialmaxvalue
INSERT INTO #record
SELECT @sno AS SNO, @Cycle AS Cycle, @initialtime as time,
@NEXTMAX as value, 'Lesser value' as MAXVALUE
END

SET @initialtime = (select DATEADD(ss,1,@initialtime))

whiLE @SNO >@InitialSno +5
BeGIN
SET @cycle =@CYCLE +1
SET @InitialSno =@InitialSno+6
END

SET @SNO = @sno+1

END
select * from #record
SELECT *,ROW_NUMBER() OVER ( PARTITION BY cycle ORDER BY time asc ) AS RANK FROM #RECORD where Value is not null and Value between .01 and .74
insert into #record1 select *,ROW_NUMBER() OVER ( PARTITION BY cycle ORDER BY time asc ) AS RANK FROM #RECORD where Value is not null and Value between .01 and .74

SELECT * FROM #RECORD1 Where rank=1

declare @cycles int=1
while @cycles<189
begin
insert into #record2 SELECT DATEDIFF(ss,(select time from #record1 where cycle=@cycles and RANK=1) ,(select time from #record1 where cycle=@cycles+1 and RANK=1))
AS DiffDate,* from #record1 where cycle=@cycles and rank=1
set @cycles=@cycles+1
end

Select * from #record2

Please suggest if we can proceed from here

Thanks & Regards
Ritesh Grover

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources