TSQL - industrial process timestamp aggregiation

I have a table that i log timestamped datachange events from a industrial process to. I need to be able to aggregiate all the individual records into a summary table. For example:

table contents

2010-07-18 03:05:27.593 Formation_Infeed.PE_1_Eye.Value 1
2010-07-18 03:05:31.423 Formation_Infeed.PE_1_Eye.Value 0
2010-07-18 03:05:34.121 Formation_Infeed.PE_1_Eye.Value 1
2010-07-18 03:06:23.765 Formation_Infeed.PE_1_Eye.Value 0

etc ....

  1. I need to know how much time the value was a "1" or a "0" until the next timestamp.

  2. I Need to know the total time the value was a "1" or a "0" over a specified time span ... ie... 8hrs

I think i need the output put into a view or a table so i can do aggregiate query work on it like .... the count of the changes to "1", the average length of a "0" value or a "1" event,and the max "0" ot "1" event.

more ▼

asked Jul 18, 2010 at 12:25 AM in Default

avatar image

1 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
 -- Sample Table
     declare @sample table
         event_date    datetime,
         event_data    varchar(50),
         value        int
 -- Sample Data
     insert into @sample
     select    '2010-07-18 03:05:27.593', 'Formation_Infeed.PE_1_Eye.Value', 1 union all
     select    '2010-07-18 03:05:31.423', 'Formation_Infeed.PE_1_Eye.Value', 0 union all
     select    '2010-07-18 03:05:34.121', 'Formation_Infeed.PE_1_Eye.Value', 1 union all
     select    '2010-07-18 03:06:23.765', 'Formation_Infeed.PE_1_Eye.Value', 0
 -- The Query
     ; with 
         select    *, row_no = row_number() over (order by event_date)
         from    @sample
     select    e1.*, time_spent = datediff(second, e1.event_date, e2.event_date)
     from    events e1
         left join events e2    on    e1.row_no    = e2.row_no - 1
more ▼

answered Jul 18, 2010 at 07:48 PM

avatar image

2.7k 1 4 7

squirrel, nice answer +1

Jul 20, 2010 at 12:35 AM WilliamD

I am a novice ... trying to make things happen while i learn so please excuse my failing ....

1st ... thank you very much for your answer ....

i'm trying to understand .....

ok ... (1) the union all on the end of the lines that are populating the sample table .... with my data file full of data ... i'm guessig i won't have to use these lines???

(2) row_number() is a sql function that returns the current row number ... correct???

(3) over (order by date) .... lost me ...

(4) looks like the data is in a table named @sample now .... but .... then we are looking at a table called events to do the next select .... how does it make it there and what are we doing ... if you could spare a moment more than you already have ....

thank you adam

Jul 22, 2010 at 05:56 AM adman3613


The table variable @sample is just to demonstrate - you don't need this.

ROW_NUMBER() is a sql server window function: http://msdn.microsoft.com/en-us/library/ms186734.aspx

It calculates a row number for the data supplied in a select statement and allows you to partition and order the data before applying the numbering. Squirrel has given each entry in @sample a row number (without partitioning it) starting with the earliest event_date.

events is a common table expression (CTE): http://msdn.microsoft.com/en-us/library/ms190766.aspx and is used to create a pseudo table at runtime.

The pseudo table has all columns from @sample plus the row number that has been generated. This is then joined to itself using the row_no to join finding the previous entry with row_no - 1. Then the time_spent is calculated using datediff on event_date for the current entry and the previous entry.

In effect, you need to replace @sample with your real table and try it out.


Jul 22, 2010 at 06:25 AM WilliamD

@WiliamD - good comment. Only to mention, that CTE is only available on SQL2005 and above, as there is not specified on which version of SQL Server the user is working. +1 also to @Squirrel

Jul 22, 2010 at 06:38 AM Pavel Pawlowski

@WilliamD, thanks for the comments.

@adman, you only required section from "The Query" onwards. Replace @sample with your actual table name and column name

Jul 22, 2010 at 06:33 PM Squirrel
(comments are locked)
10|1200 characters needed characters left

@WilliamD - Great ... thanks for taking the time .... you have helped a lot .... I'm running 2005 btw and will probably be upgrading to 2008 in the next month or 2.
Another long weekend .... upgrading our data server .... moving database .... iis .... opcsystems.net ..... kepware .... you get the picture ... lol ...
thanks again

more ▼

answered Aug 02, 2010 at 09:32 PM

avatar image

1 1 1 1

@adman3613 - Please mark squirrels answer as correct if that has helped you, then everyone can see you have been helped out. Thanks.

Aug 02, 2010 at 11:57 PM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 18, 2010 at 12:25 AM

Seen: 1232 times

Last Updated: Jul 18, 2010 at 12:25 AM

Copyright 2016 Redgate Software. Privacy Policy