accumulated values

  • 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

  • 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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Use something like this :

    WHERE DatePart(minute, Your_Date_Field) % 15 = 0

  • 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

  • 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

  • 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

  • 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]

  • 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

    create table #t (d1 int primary key identity(1,1), v1 DATETIME, 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)

    ;WITH SequencedData AS (

    SELECT d1, v1, v2, v3, seq = ROW_NUMBER() OVER (ORDER BY v1 DESC)

    from #t newest

    )

    SELECT Newest.*, Oldest.*

    FROM SequencedData Newest

    LEFT JOIN SequencedData Oldest ON Oldest.seq = Newest.seq+5


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

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

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