1

Get the total machine running hours between two dates but split into 3 time ranges: standard time, peak time and off-peak time.

Context
Programming environment: Wonderware ArchestrA
Programming language: ArchestrA Quick Script .Net
Database: Historian - SQL Server (In-SQL)
External: A couple of pumps in the mining industry, need to know the pump usage during 3 different power tariff times (peak, standard, off-peak times).

Weekdays:
Standard Time: 09:00 to 17:00 AND 19:00 to 22:00
Peak Time: 06:00 to 09:00 AND 17:00 to 19:00
Off-Peak Time: 22:00 to 06:00

Saturdays:
Standard Time: 07:00 to 12:00 AND 18:00 to 20:00
Off-Peak Time: 20:00 to 07:00 AND 12:00 to 18:00

Sundays:
Off-Peak Time: The whole Sunday is off-peak

I need
Between two dates:

  • Total Hours pump ran during Peak Time.
  • Total Hours pump ran during Off-Peak Time.
  • Total Hours pump ran during Standard Time.

What I've tried: (Total off-peak time in hours that the machine ran).
It works, but most of the times I get less hours than what I should get. 

-- This script only gets the total off-peak time hours
SET NOCOUNT ON  
        DECLARE @StartDate DateTime  
        DECLARE @EndDate DateTime  
        DECLARE @var1 REAL;  
        DECLARE @var2 REAL; 
        DECLARE @var3 REAL; 

        SET @StartDate = '2015/08/01 05:00:00.000'  
        SET @EndDate = GetDate()  
        SET NOCOUNT OFF  

        SET @var1 = 
           (
           SELECT   
               'Count' = Count(DiscreteHistory.Value)/60.0  
           FROM   
               DiscreteHistory  
           WHERE  
               DiscreteHistory.TagName  
               IN ('KDCE_S04_22PMP01_Machine.FA_RF') 
               AND DiscreteHistory.Value = 1  
               AND wwRetrievalMode = 'Cyclic' 
               AND wwResolution = 60000  
               AND DateTime >= @StartDate  
               AND DateTime <= @EndDate  
               AND DATEPART(dw, DateTime) NOT IN (2, 3, 4, 5, 6, 7)
           )

        SET @var2 = 
           ( 
           SELECT   
               'Count' = Count(DiscreteHistory.Value)/60.0  
           FROM  
               DiscreteHistory 
           WHERE  
               DiscreteHistory.TagName 
               IN ('KDCE_S04_22PMP01_Machine.FA_RF')  
               AND DiscreteHistory.Value = 1  
               AND wwRetrievalMode = 'Cyclic'  
               AND wwResolution = 60000  
               AND DateTime >= @StartDate  
               AND DateTime <= @EndDate  
               AND DATEPART(dw, DateTime) NOT IN (1, 2, 3, 4, 5, 6)
               AND (CAST(DateTime as time) >= '20:00:00' AND CAST(DateTime as time) < '07:00:00') 
           )

        SET @var3 = 
           ( 
           SELECT   
               'Count' = Count(DiscreteHistory.Value)/60.0  
           FROM   
               DiscreteHistory  
           WHERE 
               DiscreteHistory.TagName 
               IN ('KDCE_S04_22PMP01_Machine.FA_RF') 
               AND DiscreteHistory.Value = 1  
               AND wwRetrievalMode = 'Cyclic'  
               AND wwResolution = 60000  
               AND DateTime >= @StartDate  
               AND DateTime <= @EndDate  
               AND DATEPART(dw, DateTime) NOT IN (1, 2, 3, 4, 5, 6) 
               AND (CAST(DateTime as time) >= '12:00:00' AND CAST(DateTime as time) < '18:00:00') 
           )

        IF @var1 IS NULL SET @var1 = 0
        IF @var2 IS NULL SET @var2 = 0 
        IF @var3 IS NULL SET @var3 = 0 

        SELECT  
           'Count' = (Count(DiscreteHistory.Value)/60.0) + @var1 + @var2 + @var3  
        FROM  
           DiscreteHistory 
        WHERE  
           DiscreteHistory.TagName 
           IN ('KDCE_S04_22PMP01_Machine.FA_RF') 
           AND DiscreteHistory.Value = 1  
           AND wwRetrievalMode = 'Cyclic'
           AND wwResolution = 60000 
           AND DateTime >= @StartDate
           AND DateTime <= @EndDate
           AND DATEPART(dw, DateTime) NOT IN (1, 7)
           AND (CAST(DateTime as time) >= '22:00:00' OR CAST(DateTime as time) < '06:00:00');

Thank you.

Sample Data

I log the following information into the database:

A unique Tag Name for Run Feedback: KDCE_S04_22PMP01_Machine.FA_RF This is a run feedback which is a "1" or "0" or "null" value

A unique Tag Name for Machine running hours: me.a0_MainPump.RunningHours.FA_PV which is an Integer value of the amount of pump running hours.

Both tag names gets logged with TagName, Value, DateTime, quality, etc.

I have a table that include the following columns: 

| DateTime | TagName | Value | QualityDetail |

Script to get sample data in DB:

SET NOCOUNT ON  

DECLARE @StartDate DateTime  
DECLARE @EndDate DateTime  

SET @StartDate = '20150701 05:00:00.000'  
SET @EndDate = '20150731 05:00:00.000'  
SET NOCOUNT OFF  

SELECT 
    DateTime, TagName, Value, Quality  
FROM 
    DiscreteHistory  
WHERE 
    DiscreteHistory.TagName IN ('KDCE_S04_22PMP01_Machine.FA_RF')  
    AND DateTime >= @StartDate AND DateTime <= @EndDate

It returns this output if I export to csv: (I have shortened it) 

DateTime,TagName,Value,Quality
2015/07/01 05:00:00 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,133
2015/07/01 05:09:46 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 05:09:53 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 06:44:20 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 06:45:54 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 07:36:22 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 07:36:48 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 01:53:44 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 01:53:44 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 02:04:52 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 02:05:27 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 02:07:25 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 02:09:13 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 02:14:54 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 12:10:48 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/02 05:24:06 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 05:24:16 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/02 05:50:52 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 05:50:59 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/02 06:00:15 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/02 06:55:18 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 06:55:18 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/02 09:46:58 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 09:46:58 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/02 01:30:27 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 01:30:27 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/02 05:38:03 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/02 07:01:56 PM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/03 03:41:09 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/03 09:05:18 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/03 10:42:00 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/03 10:57:31 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/03 04:53:36 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/04 10:08:17 PM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/05 06:43:50 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/05 09:43:08 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/05 01:04:03 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/06 09:37:53 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/06 11:07:15 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/06 11:29:48 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/06 05:02:38 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/07 06:15:33 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/07 06:32:24 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/07 09:05:20 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/07 01:10:09 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/07 01:10:16 PM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/07 04:45:12 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/07 08:19:40 PM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/07 09:01:35 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
Roan
  • 892
  • 15
  • 27
  • Please add some sample from the input data! And what do You mean "sometimes"? Please add working sample, and not working sample too! – Gabor Rajczi Aug 03 '15 at 11:23
  • Hi Ive added some data, hope it is what you are looking for. By "sometimes" I mean it sometmes give me the correct data and sometimes it gives me data that doesn't add up, i.e. if I add the minutes it gives me more that what is possible. – Roan Aug 03 '15 at 12:00
  • If it adds up you probably have a join issue or your intervals are overlapping. The next logical step is to look at the detail and work out why the number is too high. – Nick.Mc Aug 03 '15 at 12:27
  • Thanks Nick, I will try to do that. I am still brand new to SQL and did not do a course on it. Basically this is my first SQL script. – Roan Aug 03 '15 at 13:38
  • 1
    After some more testing I found that the section where I CAST the times is not functioning correctly for the "standard time": first section where I use cast: AND (CAST(DateTime as time) >= '10:00:00' AND CAST(DateTime as time) < '17:00:00') second section where I use cast: AND (CAST(DateTime as time) > '20:00:00' AND CAST(DateTime as time) <= '22:00:00') – Roan Aug 05 '15 at 06:41
  • I have updated the sample script, It seems that I got it working, but most of the time it is still giving me less hours than what it should give me. – Roan Aug 24 '15 at 04:09

2 Answers2

0

Okay so the edited script above are working fine now, basically I have three variations of the code seen below for each of the time ranges (peak, standard and off-peak).
The only problem is that if you should have network communication issues between the PLC and Historian/SQL Database then you would "loose" data and you will end up with a lot of "null" readings in the database. That was why the hours wasn't adding up. The better way would be to implement this function in the PLC at the cost of SCADA IO Tags.
Nonetheless, the script below is working.

    -- this script only gets the total off-peak time hours
    SET NOCOUNT ON  
    DECLARE @StartDate DateTime  
    DECLARE @EndDate DateTime  
    DECLARE @var1 REAL;  
    DECLARE @var2 REAL; 
    DECLARE @var3 REAL; 

    SET @StartDate = '2015/08/01 05:00:00.000'  
    SET @EndDate = GetDate()  
    SET NOCOUNT OFF  

    SET @var1 = 
       (
       SELECT   
           'Count' = Count(DiscreteHistory.Value)/60.0  
       FROM   
           DiscreteHistory  
       WHERE  
           DiscreteHistory.TagName  
           IN ('KDCE_S04_22PMP01_Machine.FA_RF') 
           AND DiscreteHistory.Value = 1  
           AND wwRetrievalMode = 'Cyclic' 
           AND wwResolution = 60000  
           AND DateTime >= @StartDate  
           AND DateTime <= @EndDate  
           AND DATEPART(dw, DateTime) NOT IN (2, 3, 4, 5, 6, 7)
       )

    SET @var2 = 
       ( 
       SELECT   
           'Count' = Count(DiscreteHistory.Value)/60.0  
       FROM  
           DiscreteHistory 
       WHERE  
           DiscreteHistory.TagName 
           IN ('KDCE_S04_22PMP01_Machine.FA_RF')  
           AND DiscreteHistory.Value = 1  
           AND wwRetrievalMode = 'Cyclic'  
           AND wwResolution = 60000  
           AND DateTime >= @StartDate  
           AND DateTime <= @EndDate  
           AND DATEPART(dw, DateTime) NOT IN (1, 2, 3, 4, 5, 6)
           AND (CAST(DateTime as time) >= '20:00:00' AND CAST(DateTime as time) < '07:00:00') 
       )

    SET @var3 = 
       ( 
       SELECT   
           'Count' = Count(DiscreteHistory.Value)/60.0  
       FROM   
           DiscreteHistory  
       WHERE 
           DiscreteHistory.TagName 
           IN ('KDCE_S04_22PMP01_Machine.FA_RF') 
           AND DiscreteHistory.Value = 1  
           AND wwRetrievalMode = 'Cyclic'  
           AND wwResolution = 60000  
           AND DateTime >= @StartDate  
           AND DateTime <= @EndDate  
           AND DATEPART(dw, DateTime) NOT IN (1, 2, 3, 4, 5, 6) 
           AND (CAST(DateTime as time) >= '12:00:00' AND CAST(DateTime as time) < '18:00:00') 
       )

    IF @var1 IS NULL SET @var1 = 0
    IF @var2 IS NULL SET @var2 = 0 
    IF @var3 IS NULL SET @var3 = 0 

    SELECT  
       'Count' = (Count(DiscreteHistory.Value)/60.0) + @var1 + @var2 + @var3  
    FROM  
       DiscreteHistory 
    WHERE  
       DiscreteHistory.TagName 
       IN ('KDCE_S04_22PMP01_Machine.FA_RF') 
       AND DiscreteHistory.Value = 1  
       AND wwRetrievalMode = 'Cyclic'
       AND wwResolution = 60000 
       AND DateTime >= @StartDate
       AND DateTime <= @EndDate
       AND DATEPART(dw, DateTime) NOT IN (1, 7)
       AND (CAST(DateTime as time) >= '22:00:00' OR CAST(DateTime as time) < '06:00:00');
Roan
  • 892
  • 15
  • 27
0

i don't know if you still need help with this topic but we actually use the event system to trigger when our pumps are not running and insert records into our own tables. This tables have a trigger that flattens the 0 and 1 into a single record making a lot easier to analyze data. an example record of our database would look something like:

brake_start_time | brake_end_time | (downtime calculation) | (uptime before it broke) | reasons why it stopped working | comments

Using a similar concept you could simply have something like

pump_start_time | pump_end_time | runtime | run_category ( standard time, peak time and off-peak time )
KoU_warch
  • 2,160
  • 1
  • 25
  • 46
  • Thanks EH_warch, I will see how I can make that work. It will reduce the code significantly. Currently I only have the Run Feedback from the field (a digital 1 or 0). So I will have to find a way to create the start time and the stop time which should not be too difficult. It is the exception handling which might make it a bit more complicated. i.e. when the start is not detected or a stop is not detected. – Roan Aug 15 '16 at 04:12