Databases
StackOverflow2013
Postsdbo
POSQL Server query buildup
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server query buildup
    primarykey
    data
    text
    <p>I have a set with data in a SQL Server database and I need to calculate uptime of a machine. I'm using two variables to determine uptime or downtime. These two variables are <code>machine_ON</code> and <code>failure(s)</code>. <code>machine_ON</code> is only one variable in the database, <code>failure</code> can be 64 different failures, but all indicates as <code>fx_x</code>.</p> <p>The status information of these variables is stored in the database as follow:</p> <pre><code>timestamp failurebitNr timestampOutOfAlarm 2012-01-17 10:38:58.000 f1_14 2012-01-17 10:39:05.000 </code></pre> <p>Meaning: failure <code>f1_14</code> was active from <code>2012-01-17 10:38:58.000</code> until <code>2012-01-17 10:39:05.000</code></p> <p>Also the <code>machine_ON</code> state is saved in the same table on the same way, only the <code>failurebitNr</code> has a different value [<code>t2_13</code>].</p> <p>So to determine the uptime, I need to get the timediff between <code>timestamp</code> and <code>timestampOutOfAlarm</code> where <code>failurebutNr = 't2_13'</code> minus any failure time.</p> <p>So for example I have those rows in my database:</p> <p><img src="https://i.stack.imgur.com/hkgW8.png" alt="Click for image of table representation"></p> <p>This should give the following graphical representation:</p> <p><img src="https://i.stack.imgur.com/aoQTg.png" alt="Graphical representation of the dataser"></p> <p>Green is uptime, red is downtime.</p> <p>I'm used to work with PHP and than use a while loop and file some array's and do other scripting. But now I need to do this all in a SQL Server database in query ways...</p> <p>So, how can I calculate the uptime (green) and downtime (red)?</p> <p><strong>UPDATE</strong></p> <p>I've tried to get the time in seconds that the machine is ON. I used this query:</p> <pre><code>&lt;!-- language: lang-sql --&gt; DECLARE @startDate datetime DECLARE @endDate datetime DECLARE @projectNr int DECLARE @MachineNr nvarchar(10) SET @startDate = '2012-01-01 00:00:00.000' SET @endDate = '2012-02-01 00:00:00.000' SET @projectNr = '1234567' SET @MachineNr = '2' SELECT DATEDIFF("SECOND", CASE WHEN timestamp &lt; @startDate THEN @startDate ELSE timestamp END, CASE WHEN timestampOutOfAlarm &gt; @endDate OR timestampOutOfAlarm IS NULL THEN @endDate ELSE timestampOutOfAlarm END) AS Uptime FROM [table] WHERE timestamp &lt; @endDate AND (timestampOutOfAlarm &gt; @startDate OR timestampOutOfAlarm IS NULL) AND fileProjectNr = @projectNr AND fileProjectMachineNr = @MachineNr AND (failureBitNr = 't2_13' AND failureBitValue = '1') </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UShalfer
    1. USTimo002
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server query buildup
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Server query buildup
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL Server query buildup
      1. This table or related slice is empty.
      1. VTUpMod
    1. COQuery added to get the time when the machine is on. Now, how to substract the failure time..? Because there can be more failures simultaneously, how to determine the failure time, only from start to end, regardless of the amount of failures.
      singulars
      1. POSQL Server query buildup
      1. USTimo002
    2. COI think I found a solution for my problem. I need to work this out and when I'm sure it is fine, I will post it!
      singulars
      1. POSQL Server query buildup
      1. USTimo002
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload