asked
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.
-- 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 events as ( 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
Adam, 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. HTH
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
@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
@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 adam
