SQL query to get the total machine running hours between two dates but split into 3 time zones: standard time, peak time and off-peak time

SQL query to get the total machine running hours between two dates but split into 3 time zones: standard time, peak time and off-peak time.

Context
Programming environment: Wonderware ArchestrA
Programming language: ArchestrA Quick Script .Net
Database: Historian - SQL Server (InSQL)
External: A couple of pumps in the mining industry, need to know the pump usage during 3 different power tarif times (peak, standard, off-peak times).
Standard Time: 10:00 to 17:00 AND 20:00 to 22:00
Peak Time: 6:00 to 10:00 AND 17:00 to 20:00
Off-Peak Time: 22:00 to 06:00

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, it works sometimes.

-- this script only gets the total peak time hours
SET NOCOUNT ON  

DECLARE @StartDate DateTime  
DECLARE @EndDate DateTime  
DECLARE @var1 REAL  
DECLARE @Date24 DateTime  

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

-- Get the Date and time where the running hours is running hours minus 24
SET @Date24 = (SELECT TOP 1 [DateTime] 
               FROM  
                   (SELECT 
                        History.TagName, DateTime, Value, StartDateTime  
                    FROM 
                        History  
                    WHERE 
                        History.TagName IN ('me.a0_MainPump.RunningHours.Tagname.FA_PV')  
                        AND Value IS NOT NULL  
                        AND Value = " + Text(me.a0_MainPump.RunningHours.FA_PV - 24, "#") 
                        AND wwVersion = 'Latest'  
                        AND DateTime >= @StartDate) temp)  

-- Get the first portion of the peak time hours  
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 >= @Date24  
AND (CAST(DateTime as time) >= '06:00:00' AND CAST(DateTime as time) < '10:00:00')  
GROUP BY DiscreteHistory.TagName  
)  -- Get the second portion of the peak time hours
SELECT 'Count' = (Count(DiscreteHistory.Value)/60.0 )+@var1  
FROM DiscreteHistory  
WHERE DiscreteHistory.TagName IN ('KDCE_S04_22PMP01_Machine.FA_RF')  
AND DiscreteHistory.Value = 1  
AND wwRetrievalMode = 'Cyclic'  
AND wwResolution = 60000  
AND DateTime >= @Date24  
AND (CAST(DateTime as time) >= '17:00:00' AND CAST(DateTime as time) <= '20: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"

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 |   

This works:

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:

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

via Chebli Mohamed