*** SEE WORD DOCUMENT ATTACHED FOR MORE DETAILS (Machine-Cycles-VCast-01)
Understanding of Programmable Control Logic (PLC) data processing. Particularly calculating machine cycle times (post data-collection), examining datasets in excess of 100,000 records at a time.
SQL SERVER 2008 (Advanced). You must be able to solve and process complex SQL problems using “set-based logic” and not rely on cursors. We prefer for the processing to be done is TSQL. We need to process the entire days records (85,000+) in less than a minute
Perhaps C#: Alternatively, if you are an SQL and C# expert and can process the logic of the records fast enough using C#, that will work. Again, speed is an issue and requirement for a successful project.
We are working with production machine data and need to calculate and organize cycle times through complex logic.
We are collecting daily data from a machine that have several basic functioning components. (called a tagClass). TagClasses we need to process are tagClasss are Left_Cutting_Head, Right_Cutting_Head, Left_ Gathering_Arm, Right_Gathering_Arm, Left_Conveyor, and Right_Conveyor.
Each tagClass is measured by the current it draws. If the current is above a certain threshold the tagClass is said to be “Active”, if it is not drawing current above a given threshold it is said to be “Idle”.
1. Create Cycle Times from the data in table (dbo.trans_WE_CM_FindCycles).
2. Group the individual cycles and write the result to a table (dbo.trans_WE_CM_DW_Data_Cycles).
3. Process a day’s worth of records in less than a minute.
4. Write the code to meet scalability and other best practices requirements listed below.
Basic Logic to Determine and Process Cycle Times
First, Cycle times for individual tagClasses will be determined in one table (trans_WE_CM_FindCycles ).
Second, the Cycle times will be grouped and written to another table (trans_WE_CM_Data_Cycles) .
Individual tagClass Cycle Time Identification
A Cycle for a given tagClass will be determined by the series of records of idle time surrounding an active tagClass series of records. We call this the “Cycle Idle Border”. If the idle time surrounding (before and after) that tagClass is greater than 1.5 minutes (This value will be a variable called @cycle_idle_border_time), the set of record that are “Active” between the Cycle Idle Border is given a unique Cycle_ID, Cycle_StartTime, and Cycle_EndTime.
For example, let’s examine just one of the tag_Classes, the Left_Cutting_Head. These are fields to be considered:
LeftCutterCurrent – This is how much current the tagClass is drawing at that point in time. The value of this field will not be changed, as it was set when the table was created.
LeftCutterCurrent_ValidReading – This is a 1 or NULL value field. If it is 1, then the tagClass is drawing enough current to be considered “Active”. If the value is NULL, the tagClass is idle at that point in time. The value of this field will not be changed, as it was set when the table was created.
Cycle_ID_LeftCutter – When the cycle is identified. This will be set to a value based on [Machine_ID] + “-“ [Mine_ID] + “-“ [Time_Stamp]
Cycle_StartTime_LeftCutter – This will be set to the value of the [Time_Stamp] in the first “Active” record of that Cycle.
[Cycle_EndTime_LeftCutter] – This will be set to the value of the [Time_Stamp] in the last “Active” record of that Cycle.
Grouped tagClass Cycle Time Identification
After individual tagClass cycles are identified, they are grouped in various ways to create records such as those in the [url removed, login to view] spreadsheet (see below). Note that each individual tag_Class cycle is written as a grouped record (one element in the group).
*** SEE WORD DOCUMENT ATTACHED FOR FULL DETAILS (Machine-Cycles-VCast-01)