title: Get the total machine running hours between two dates but split into 3 time ranges: standard time, peak time and off-peak time
body: <p>Get the total machine running hours between two dates but split into 3 time ranges: standard time, peak time and off-peak time.</p>
  
  <p><strong>Context</strong><br>
  <em>Programming environment:</em> Wonderware ArchestrA<br>
  <em>Programming language:</em> ArchestrA Quick Script .Net<br>
  <em>Database: Historian</em> - SQL Server (In-SQL)<br>
  <em>External:</em> 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). </p>
  
  <p>Weekdays:<br>
  <em>Standard Time:</em> 09:00 to 17:00 AND 19:00 to 22:00<br>
  <em>Peak Time:</em> 06:00 to 09:00 AND 17:00 to 19:00<br>
  <em>Off-Peak Time:</em> 22:00 to 06:00  </p>
  
  <p>Saturdays:<br>
  <em>Standard Time:</em> 07:00 to 12:00 AND 18:00 to 20:00<br>
  <em>Off-Peak Time:</em> 20:00 to 07:00 AND 12:00 to 18:00   </p>
  
  <p>Sundays:<br>
  <em>Off-Peak Time:</em> The whole Sunday is off-peak  </p>
  
  <p><strong>I need</strong><br>
  Between two dates:  </p>
  
  <ul>
  <li>Total Hours pump ran during Peak Time.  </li>
  <li>Total Hours pump ran during Off-Peak Time.  </li>
  <li>Total Hours pump ran during Standard Time.</li>
  </ul>
  
  <p>What I've tried: (Total off-peak time in hours that the machine ran).<br>
  It works, but most of the times I get less hours than what I should get.  </p>
  
  <pre><code>-- 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 &gt;= @StartDate  
                 AND DateTime &lt;= @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 &gt;= @StartDate  
                 AND DateTime &lt;= @EndDate  
                 AND DATEPART(dw, DateTime) NOT IN (1, 2, 3, 4, 5, 6)
                 AND (CAST(DateTime as time) &gt;= '20:00:00' AND CAST(DateTime as time) &lt; '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 &gt;= @StartDate  
                 AND DateTime &lt;= @EndDate  
                 AND DATEPART(dw, DateTime) NOT IN (1, 2, 3, 4, 5, 6) 
                 AND (CAST(DateTime as time) &gt;= '12:00:00' AND CAST(DateTime as time) &lt; '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 &gt;= @StartDate
             AND DateTime &lt;= @EndDate
             AND DATEPART(dw, DateTime) NOT IN (1, 7)
             AND (CAST(DateTime as time) &gt;= '22:00:00' OR CAST(DateTime as time) &lt; '06:00:00');  
  </code></pre>
  
  <p>Thank you.  </p>
  
  <p><strong>Sample Data</strong>  </p>
  
  <p>I log the following information into the database:  </p>
  
  <p>A unique Tag Name for Run Feedback: <em>KDCE_S04_22PMP01_Machine.FA_RF</em> This is a run feedback which is a "1" or "0" or "null" value  </p>
  
  <p>A unique Tag Name for Machine running hours: <em>me.a0_MainPump.RunningHours.FA_PV</em> which is an Integer value of the amount of pump running hours.   </p>
  
  <p>Both tag names gets logged with TagName, Value, DateTime, quality, etc.  </p>
  
  <p>I have a table that include the following columns:  </p>
  
  <pre><code>| DateTime | TagName | Value | QualityDetail |   
  </code></pre>
  
  <p>Script to get sample data in DB:</p>
  
  <pre><code>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 &gt;= @StartDate AND DateTime &lt;= @EndDate  
  </code></pre>
  
  <p>It returns this output if I export to csv: (I have shortened it)  </p>
  
  <pre><code>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
  </code></pre>