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.
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 expertsJourneyID TimeSpend AcceValue33946F55-DACF-42D7-8E6E-5EE0D9E96F6E 16:58.0 0.0633946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:02.0 0.0133946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:04.0 -0.0433946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:06.0 -0.0233946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:09.0 0.0233946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:11.0 0.0233946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:13.0 0.0133946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:15.0 -0.0333946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:18.0 0.1133946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:20.0 0.1233946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:23.0 0.2333946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:26.0 0.0933946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:28.0 0.1533946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:31.0 0.0633946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:34.0 -0.0233946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:36.0 -0.1733946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:39.0 -0.2733946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:42.0 -0.1633946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:44.0 -0.1333946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:47.0 -0.0233946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:49.0 0.0333946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:51.0 0.1933946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:53.0 0.0733946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:56.0 0.0633946F55-DACF-42D7-8E6E-5EE0D9E96F6E 17:58.0 0.0433946F55-DACF-42D7-8E6E-5EE0D9E96F6E 18:00.0 0.1733946F55-DACF-42D7-8E6E-5EE0D9E96F6E 18:03.0 0.3Thanks & RegardsRitesh 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 R2Ritesh GroverSenior Database Developer |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
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 & RegardsRitesh 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 solutionDeclare @initialtime datetimeDECLARE @maximumTime datetimeDeclare @Minaccvalue decimal (15,2)Declare @Maxaccvalue decimal(15,2)set @Minaccvalue=0.01SET @Maxaccvalue=0.74declare @initialmaxvalue decimal (15,2)Declare @InitialSno int =0DECLARE @cycle intDECLARE @SNO Int SET @cycle =1SET @SNO=1Declare @addonesecond int=1declare @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 .74order by TimeSpend desc)set @initialmaxvalue= @Minaccvaluedrop table #recordcreate table #record ( sno INT, cycle INT, Time DATETIME, Value decimal(6,2), MaxValue VARCHAR(20) ) drop table #record1create table #record1( sno INT, cycle INT, Time DATETIME, Value decimal(6,2), MaxValue VARCHAR(20),Rank int )drop table #record2create 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=1while @cycles<189begininsert 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=1set @cycles=@cycles+1endSelect * from #record2 Please suggest if we can proceed from hereThanks & RegardsRitesh Grover |
|
|
|
|
|
|
|