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

adman3613 gravatar 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

Squirrel gravatar image

2.5k 1 2 4

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

adman3613 gravatar 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 1105 times

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