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

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

Solution

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');