SQL SERVER - PLC Production Cycles Processing




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”.

Programming Requirements:

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).


Skills: .NET, Data Mining, SQL

See more: plc sql, record cycle time plc, plc data cycle, plc processing cycle, speed programming problems, programming sql, programming in sql, programming best practices, production spreadsheet, production post production, problems to solve with programming, plc programming work, plc programming requirements, in post production, how to write a requirements document example, how to solve programming problems, expert in sql programming, element 14, data processing requirements, best programming practices, basic programming problems, basic plc programming, advanced c programming by example, sql server 2008 plc data collection, sql server or

Project ID: #1435159

Awarded to:


Hi, I have worked before with PLC and SCADA software. Recently I wrote some set-based logic to get three different running speeds of data logged from a conveyor belt for a MS SQL Report which correlated to the type More

$300 USD in 10 days
(1 Review)

4 freelancers are bidding on average $230 for this job


Dear Sir, I'm an IT professional with more tha 20 years of working experience, including 10 years int the lagest Coca-Cola Canning plant in LatinAmerica. I'm new at Freelancers.com so I don't have reviews yet. I More

$200 USD in 5 days
(0 Reviews)


$220 USD in 5 days
(0 Reviews)

Dear Sir/Madam, I am writing to apply for this position. The opportunity presented in this listing is very interesting, and I believe that my team strong technical experience and education will make me a very comp More

$200 USD in 15 days
(0 Reviews)