January 13, 2008 at 10:23 am
I have a database tracking industrial process data. There are roughly 200 tags (equipment or machines) being tracked. When a problem happens associated with one of these tags, the alarm field changes from a 0 to a 1. When the problem is corrected, the machine operator clicks a few buttons on his screen and the alarm value returns to 0. The amount of time the tag is in alarm mode ("1") can be from a few seconds to hours. Chronologically, the data being stored in the principle table is by date_time; thus the specific tag alarm records can be hundreds of rows apart in the table. Each machine can have multiple alarms during a production day. Below is a crude and simplified example of data:
Date_Time Equipment# Alarm
01/11/08 10:18:45 Machine#01 0
01/11/08 10:18:48 Machine#12 0
01/11/08 10:25:45 Machine#33 1
01/11/08 10:38:54 Machine#75 0
01/11/08 10:43:14 Machine#28 0
01/11/08 11:07:05 Machine#58 0
01/11/08 12:19:04 Machine#01 1
01/11/08 13:06:33 Machine#08 0
01/11/08 13:07:05 Machine#58 1
01/11/08 14:31:53 Machine#33 0
...
01/11/08 17:33:19 Machine#01 0
...
From the above data I need to build a new table that will look like this:
Equipment# Start Date_Time End Date_Time Alarm duration
Machine#31 01/11/08 12:19:04 01/11/08 17:33:19 05:14:15
...
Machine#33 01/11/08 10:25:45 01/11/08 14:31:53 04:06:08
...
I hope the columns are displayed correctly when I submit this. If not, I'll try again.
Thanks for any suggestions you can provide.
:unsure:
January 13, 2008 at 11:55 am
I hope the set based purists don't yell at me... 😀
I'd create a temp table populated with the machines and date ranges I want to process. I'd also create a "result set" temp table for me to insert records into for the final select at the end. Loop through (this can be done "cursorless" There are plenty of articles out there on how to do this) keeping track of current and previous machine and alarm state. When alarm state changes (When you have a 1, keep track of the startdate. When state changes back to 0, write the record with the startdate you held on to with the date of the current record), write a record to your recordset temp table.
At the end of your procedure, your temp table is populated with the info you need, and the last select of the stored procedure is the one returned to your client application. I've done something similar when I have no front end application, but my consumer is just a Crystal Report, which is limiting.
January 13, 2008 at 8:38 pm
I'm with Jeremy. I'm sure there's a good set based way to do this, but to just get it done, I'd rip through the table based on time and process the data by time.
January 14, 2008 at 4:56 am
Hi,
You might give this a try.
I had the idea that if you created a table with an incremental rowid and a clustered index on the machine and date you could then insert the rows from the existing table and store them in order thanks to the clustered index. Then a simple select query can make use of the incremental rowid and the status field to retrieve the results you need.
I'm not suggesting this is the best/fastest way of doing this but it worked for the data that you gave.
Thanks
K.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TBL_STATE_TEMP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TBL_STATE_TEMP]
GO
CREATE TABLE [dbo].[TBL_STATE_TEMP] (
[RowID] [bigint] IDENTITY (1, 1) NOT NULL ,
[Machine] [char] (3) NOT NULL , -- size depends on how many machines you have
[EventDateTime] [smalldatetime] NOT NULL ,
[State] [tinyint] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CI_TBL_STATE_TEMP] ON [dbo].[TBL_STATE_TEMP]([Machine], [EventDateTime]) ON [PRIMARY]
GO
Insert into [dbo].[TBL_STATE_TEMP]
Select [dbo].[TBL_STATE].[Machine],[dbo].[TBL_STATE].[EventDateTime],[dbo].[TBL_STATE].[State]
From TBL_STATE
Go
SELECT dbo.TBL_STATE_TEMP.Machine, dbo.TBL_STATE_TEMP.EventDateTime AS StartEvent, TBL_STATE_TEMP_1.EventDateTime AS EndEvent,
CASE WHEN DATEDIFF(s, dbo.TBL_STATE_TEMP.EventDateTime, TBL_STATE_TEMP_1.EventDateTime)/3600<10 THEN '0' ELSE '' END
+ RTRIM(DATEDIFF(s, dbo.TBL_STATE_TEMP.EventDateTime, TBL_STATE_TEMP_1.EventDateTime)/3600)
+ ':' + RIGHT('0'+RTRIM((DATEDIFF(s, dbo.TBL_STATE_TEMP.EventDateTime, TBL_STATE_TEMP_1.EventDateTime) % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((DATEDIFF(s, dbo.TBL_STATE_TEMP.EventDateTime, TBL_STATE_TEMP_1.EventDateTime) % 3600) % 60),2) as AlarmDuration
FROM dbo.TBL_STATE_TEMP INNER JOIN
dbo.TBL_STATE_TEMP TBL_STATE_TEMP_1 ON dbo.TBL_STATE_TEMP.RowID + 1 = TBL_STATE_TEMP_1.RowID AND
dbo.TBL_STATE_TEMP.Machine = TBL_STATE_TEMP_1.Machine AND dbo.TBL_STATE_TEMP.State > TBL_STATE_TEMP_1.State
January 14, 2008 at 5:26 am
Does this solution take into account that multiple data points could be collected in an alarm state? Does it also take into account that a machine can fall into and out of an alarm state potentially hundreds of times per day?
For example
MachineID Date Status
1 1/1/08 10:01:01 0
1 1/1/08 10:01:20 1
1 1/1/08 10:01:30 1
1 1/1/08 10:01:40 1
1 1/1/08 10:01:50 0
1 1/1/08 10:02:01 1
1 1/1/08 10:02:10 0
In this case, the machine had two alarms, one lasting 30 seconds, and the other lasting 9 seconds. The poster can let us know if data like this is possible, but I have a feeling it is. I'm not sure if there is a way to do this using a simple select.
January 14, 2008 at 7:43 am
What would be the reason(s) that an Update trigger would not be able to handle this by comparing the before and after version of the records and writing a summary record?
Pardon me if this is a dumb question.
Toni
January 14, 2008 at 7:53 am
The Alarms themselves should be their own entity (a.k.a table) with their own unique identifier. Would make this issue MUCH simpler.... That way you would have no issues with how far apart they are.
The situation gets much clearer if you have:
date MachineID AlarmID Status
This would dovetail nicely into the UPDATE trigger Toni was referring to (I'd think it would be an INSTEAD of trigger, since you wish to catch the update and turn it into an insert...)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 14, 2008 at 8:21 am
Jeremy,
You are correct, if that is the case then the solution i provided would not work. Saying that as far as i can see from the data provided and the description of the issue this isn't the case. Guess we'll have to wait and see if i wasted my time 🙂
K.
January 14, 2008 at 9:49 am
The OP states "When a problem happens associated with one of these tags, the alarm field changes from a 0 to a 1. When the problem is corrected, the machine operator clicks a few buttons on his screen and the alarm value returns to 0."
If this is indeed the case and no other readings are pulled, then the following set-based statement appears to work a treat:
[font="Courier New"]--DROP TABLE #OldTable
CREATE TABLE #OldTable (Date_Time DATETIME, Equipment VARCHAR(10), Alarm tinyint)
INSERT INTO #OldTable (Date_Time, Equipment, Alarm)
SELECT '01/11/08 10:18:45', 'Machine#01', 0 UNION ALL
SELECT '01/11/08 10:18:48', 'Machine#12', 0 UNION ALL
SELECT '01/11/08 10:25:45', 'Machine#33', 1 UNION ALL
SELECT '01/11/08 10:38:54', 'Machine#75', 0 UNION ALL
SELECT '01/11/08 10:43:14', 'Machine#28', 0 UNION ALL
SELECT '01/11/08 11:07:05', 'Machine#58', 0 UNION ALL
SELECT '01/11/08 12:19:04', 'Machine#01', 1 UNION ALL
SELECT '01/11/08 13:06:33', 'Machine#08', 0 UNION ALL
SELECT '01/11/08 13:07:05', 'Machine#58', 1 UNION ALL
SELECT '01/11/08 14:31:53', 'Machine#33', 0 UNION ALL
SELECT '01/11/08 16:07:05', 'Machine#58', 0 UNION ALL
SELECT '01/11/08 17:07:05', 'Machine#58', 1
SELECT Equipment, [Start Date_Time], [End Date_Time], ([End Date_Time] - [Start Date_Time]) AS [Alarm duration] -- extract/format time component as required
FROM (
SELECT
a.Equipment,
a.Date_Time AS [Start Date_Time],
(SELECT MIN(Date_Time)
FROM #OldTable b
WHERE b.Equipment = a.Equipment
AND b.Date_Time > a.Date_Time
AND b.Alarm = 1) AS [End Date_Time]
FROM #OldTable a
) t[/font]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 14, 2008 at 10:01 am
Having posted the above, I'd like to point out that I really really really hate correlated subqueries because they are more often than not an indication that the author of the statement couldn't be bothered to work out how to do the job using a derived table, and if they couldn't be bothered to do something this trivial, then I've got loads of work to do rewriting loads of some idle bu88ers cr@p code which runs like a lame dog, hogs resources and looks horrible.
But today it's my turn to write cr@p code because I simply couldn't figure out how to do this with a derived table :sick: !!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 14, 2008 at 10:24 am
It still doesn't get around issues with the data like Jeremy was getting at.
Is there a guarantee that issues are addressed in the order they occurred in? With a GUI popup, the later issue tends to pop up ON TOP of the older one, so I'd think that the answer to the above would be a no. If you make the box modal - then you're going to get an error, so you can get double the fun there....
Under that circumstance - there's no way for sure to know which issue was "resolved".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 14, 2008 at 6:18 pm
Note to all: Thanks! I'm on one of my all-too-frequent business trips. I think between now and June, I'm home maybe 3 - 4 weeks. I may not be able to respond very quickly to ideas, suggestions or questions. Pretty astute group. 🙂
Jeremy - good observation on data possibilities. Don't know how much you and/or others in the group are familiar with equipment PLC's (Programable Logic Controllers) from Allen-Bradley, etc. The alarm data is handled as discrete. It can only be a 1 or 0 and can only be different from any previous value. Once the value has changed, it remain that value until something happens (PLC triggers an alarm changing value to 1 or operator acknowledges the alarm, triggering the change back to 0). Until that is done, the value is stored in the data table with the date and time stamp that is assigned when the alarm status changes.
I will be running tests on the data in a few weeks using the information provided by those in the group. Please don't hesitate to provide ideas or ask questions. Be somewhat patient if I don't get to respond very quickly. Again, many thanks!!
Dean
January 14, 2008 at 8:03 pm
Yes, I'm familiar with PLCs. I used to work for a company that wrote custom data collection and reporting systems for manufacturers.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply