SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Displaying Maximum in every 12 and 6 second window
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rgrover
Starting Member

India
4 Posts

Posted - 08/09/2013 :  06:17:11  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 08/09/2013 :  07:02:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Which version of SQL Server are you using?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

rgrover
Starting Member

India
4 Posts

Posted - 08/09/2013 :  07:15:52  Show Profile  Reply with Quote
Hi I am Using Sql Server 2008 R2

Ritesh Grover
Senior Database Developer
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/09/2013 :  07:50:25  Show Profile  Reply with Quote
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
Go to Top of Page

rgrover
Starting Member

India
4 Posts

Posted - 08/09/2013 :  09:03:00  Show Profile  Reply with Quote
Hi Visakh, thanks for providing input but this is not as as per our requirement.

Thanks & Regards
Ritesh Grover
Go to Top of Page

rgrover
Starting Member

India
4 Posts

Posted - 08/12/2013 :  05:15:45  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000