accumulated values

Question

accumulated values

Anamika

SSCertifiable

Points: 6202
More actions
January 9, 2012 at 5:04 am

#254700

hi,
i have a table with every minute data, if i want to select data for every 15 minute how can i do it.
for example
01/05/2012 5:40:00 PM 1
01/05/2012 5:39:00 PM 5
01/05/2012 5:38:00 PM 6
01/05/2012 5:37:00 PM 7
01/05/2012 5:36:00 PM 10
01/05/2012 5:35:00 PM 15
01/05/2012 5:34:00 PM 19
01/05/2012 5:33:00 PM 457
01/05/2012 5:32:00 PM 78
01/05/2012 5:31:00 PM 89
01/05/2012 5:30:00 PM 78
in the above data i want 01/05/2012 5:40:00 PM , 01/05/2012 5:35:00 PM and 01/05/2012 5:30:00 PM like this it goes.
i want to select the other columns also.
thanks,
regards,
ami

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

Anamika (1/9/2012)
hi,
i have a table with every minute data, if i want to select data for every 15 minute how can i do it.
for example
01/05/2012 5:40:00 PM 1
01/05/2012 5:39:00 PM 5
01/05/2012 5:38:00 PM 6
01/05/2012 5:37:00 PM 7
01/05/2012 5:36:00 PM 10
01/05/2012 5:35:00 PM 15
01/05/2012 5:34:00 PM 19
01/05/2012 5:33:00 PM 457
01/05/2012 5:32:00 PM 78
01/05/2012 5:31:00 PM 89
01/05/2012 5:30:00 PM 78
in the above data i want 01/05/2012 5:40:00 PM , 01/05/2012 5:35:00 PM and 01/05/2012 5:30:00 PM like this it goes.
i want to select the other columns also.
thanks,
regards,
ami

You mention every 15 minutes, but then your sample output is every 5 - or am I missing something?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

azdzn SSCarpal Tunnel Points: 4128 More actions · Answer 2

Use something like this :

WHERE DatePart(minute, Your_Date_Field) % 15 = 0

Anamika SSCertifiable Points: 6202 More actions · Answer 3

sorry about the interval mistake. lets take 5 minutes only.

create table #t (d1 int primary key identity(1,1), v1 varchar(50), v2 int, v3 int)

insert into #t values('01/05/2012 5:59:00 PM',322667648,330025988)

insert into #t values('01/05/2012 5:58:00 PM',322668648,330025888)

insert into #t values('01/05/2012 5:57:00 PM',322668648,330025888)

insert into #t values('01/05/2012 5:56:00 PM',322667648,330025888)

insert into #t values('01/05/2012 5:55:00 PM',322667648,330025788)

insert into #t values('01/05/2012 5:54:00 PM',322668648,330025888)

insert into #t values('01/05/2012 5:53:00 PM',322667648,330025888)

insert into #t values('01/05/2012 5:52:00 PM',322667648,330025888)

insert into #t values('01/05/2012 5:51:00 PM',322667648,330025888)

insert into #t values('01/05/2012 5:50:00 PM',322667648,330025688)

insert into #t values('01/05/2012 5:49:00 PM',322667948,330025888)

insert into #t values('01/05/2012 5:48:00 PM',322667648,330025888)

insert into #t values('01/05/2012 5:47:00 PM',322667648,330025888)

insert into #t values('01/05/2012 5:46:00 PM',322667648,330025888)

insert into #t values('01/05/2012 5:45:00 PM',322667628,330025588)

select * from #t

from the above table i want to take every 5th record and subtract it from the 1st record. it is for every 5 minutes gap. as i find it difficult to process with dates, i've added a identity column with that we can take every 5th record. the v1 of 5th record need to get subtract from 1st record. and 10th record subtract from 5th record. and it goes like this.

how can i achieve this?

thanks,

regards,

ami

tfifield SSCrazy Eights Points: 9655 More actions · Answer 4

Ami,

First of all, you should never use a VARCHAR column for dates - especially if you want to sort them. I've changed your create table statement to:

create table #t (d1 int primary key identity(1,1), v1 DATETIME, v2 int, v3 int)

Secondly. If you are using real tables in the database rather than a temp table you can't guarantee that the IDENTITY column will actually be consecutive. Any attempted insert into the table that fails will increment the identity and you will have a gap. The only way to guarantee consecutive row numbering in a table is to row number it yourself.

Third, you have to have some sort of range to deal with. You have to know where to start and where to end.. Here's an example that should give you what you want. This example works in ascending order and compares the a row to the one 5 rows back in time. If you wanted to start with the latest row and compare it to the row 5 rows earlier in time, then you just need to change the ASC to DESC in CTEOrdered.

DECLARE

@StartDateDATETIME

, @EndDateDATETIME

SET @StartDate = '1/5/2012'

SET @EndDate = '1/6/2012'

; WITH CTEOrdered AS

( SELECT v1, v2, v3

, ROW_NUMBER() OVER (ORDER BY v1 ASC) AS RowNum

FROM #t

WHERE v1 >+ @StartDate

AND v1 < @EndDate

), CTE5thRow AS

( SELECT v1, v2, v3, RowNum

FROM CTEOrdered

WHERE RowNum % 5 = 1

)

SELECT CTE1.v1, CTE1.v2, CTE1.v3, CTE1.RowNum

, C2.v1, C2.v2, C2.v3, C2.RowNum AS PriorRowNum

, CTE1.v2 - C2.v2 AS V2Diff

, CTE1.v3 - C2.v3 AS V3Diff

FROM CTE5thRow AS CTE1

OUTER APPLY

(SELECT TOP 1 CTE2.v1, CTE2.v2, CTE2.v3, CTE2.RowNum

FROM CTE5thRow AS CTE2

WHERE CTE2.v1 < CTE1.V1

ORDER BY CTE2.RowNum DESC

) AS C2

Todd Fifield

adrian.facio SSCrazy Points: 2406 More actions · Answer 5

Hi,

I came up with the following, maybe you like it and is what you need.

Thanks

-- Test table definition

DECLARE @t TABLE(DateStamp DATETIME PRIMARY KEY CLUSTERED, V1 INT, V2 INT)

-- Test data population

insert into @t values('01/05/2012 5:59:00 PM',322667648,330025988)

insert into @t values('01/05/2012 5:58:00 PM',322668648,330025888)

insert into @t values('01/05/2012 5:57:00 PM',322668648,330025888)

insert into @t values('01/05/2012 5:56:00 PM',322667648,330025888)

insert into @t values('01/05/2012 5:55:00 PM',322667648,330025788)

insert into @t values('01/05/2012 5:54:00 PM',322668648,330025888)

insert into @t values('01/05/2012 5:53:00 PM',322667648,330025888)

insert into @t values('01/05/2012 5:52:00 PM',322667648,330025888)

insert into @t values('01/05/2012 5:51:00 PM',322667648,330025888)

insert into @t values('01/05/2012 5:50:00 PM',322667648,330025688)

insert into @t values('01/05/2012 5:49:00 PM',322667948,330025888)

insert into @t values('01/05/2012 5:48:00 PM',322667648,330025888)

insert into @t values('01/05/2012 5:47:00 PM',322667648,330025888)

insert into @t values('01/05/2012 5:46:00 PM',322667648,330025888)

insert into @t values('01/05/2012 5:45:00 PM',322667628,330025588)

-- Get the first value recorded value

;WITH FirstReading

AS

(

SELECT TOP 1 * FROM @t

ORDER BY DateStamp

)

-- Get the comparison of the first record whith the records that have a differense of 5 minutes increments.

SELECT FirstDate = fr.DateStamp,

FirtsValue1 = fr.V1,

FirstValue2 = fr.V2,

LaterDate = lr.DateStamp,

LaterValue1 = lr.V1,

LaterValue2 = lr.V2,

MinuteDifference = datediff(minute ,fr.DateStamp, lr.DateStamp),

Value1Difference = lr.V1 - fr.V1,

Value2Difference = lr.V2 - fr.V2

FROM FirstReading fr

JOIN @t lr

ON lr.DateStamp > fr.DateStamp AND

datediff(minute ,fr.DateStamp, lr.DateStamp) % 5 = 0

thava SSCrazy Points: 2275 More actions · Answer 6

Hi anamika

you are there

you already insert a identity col

instead of starting from 1 just start from 0

and then select the records using the remainder operator

select * from tbl where Id%5=0

from the result

now you can process easily what you want

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 7

Anamika (1/9/2012)
sorry about the interval mistake. lets take 5 minutes only.
create table #t (d1 int primary key identity(1,1), v1 varchar(50), v2 int, v3 int)
insert into #t values('01/05/2012 5:59:00 PM',322667648,330025988)
insert into #t values('01/05/2012 5:58:00 PM',322668648,330025888)
insert into #t values('01/05/2012 5:57:00 PM',322668648,330025888)
insert into #t values('01/05/2012 5:56:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:55:00 PM',322667648,330025788)
insert into #t values('01/05/2012 5:54:00 PM',322668648,330025888)
insert into #t values('01/05/2012 5:53:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:52:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:51:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:50:00 PM',322667648,330025688)
insert into #t values('01/05/2012 5:49:00 PM',322667948,330025888)
insert into #t values('01/05/2012 5:48:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:47:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:46:00 PM',322667648,330025888)
insert into #t values('01/05/2012 5:45:00 PM',322667628,330025588)
select * from #t
from the above table i want to take every 5th record and subtract it from the 1st record. it is for every 5 minutes gap. as i find it difficult to process with dates, i've added a identity column with that we can take every 5th record. the v1 of 5th record need to get subtract from 1st record. and 10th record subtract from 5th record. and it goes like this.
how can i achieve this?
thanks,
regards,
ami

You haven't specified what you want to subtract from what. Have a look at the result of the following query, it returns the current row on the left and the row from 5 rows back on the right. Remove the columns you don't need and add your calculation to the output.

drop table #t