StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
12348765
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2012-09-10T09:23:59.303
FavoriteCount
0
LastActivityDate
2012-09-10T09:23:59.303
LastEditDate
LastEditorUserId
0
OwnerUserId
1643487
ParentId
12278236
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
Problem solved: I did it in 2 stored procedures, or Query's. One for getting the "ON" time and one for getting all the "DOWN" time within the "ON" time. Whith those two times I can calculate the uptime, downtime, maintenance time. Query for ON time: <pre><code> SELECT fctAlarmId, fileProjectNr, fileProjectMachineNr, failureBitNr, timestamp, CASE WHEN timestampOutOfAlarm > @endDate OR timestampOutOfAlarm IS NULL THEN @endDate ELSE timestampOutOfAlarm END AS timestampOutOfAlarm INTO #tempTable1 FROM fctAlarmHistory WHERE (timestampOutOfAlarm >= @startDate OR timestampOutOfAlarm = NULL) AND timestamp < @endDate AND failureBitValue = 1 AND failureBitNr = 't2_13' AND fileProjectNr = @projectNr And fileProjectMachineNr = @ProjectMachineNr -- SUM the result of all ON times into OnTime in seconds SELECT SUM(DATEDIFF("SECOND", CASE WHEN timestamp < @startDate THEN @startDate ELSE timestamp END, CASE WHEN timestampOutOfAlarm > @endDate THEN @endDate ELSE timestampOutOfAlarm END)) AS OnTime FROM #tempTable1 </code></pre> Query for Downtime during ON time: <pre><code> SELECT fctAlarmId, fileProjectNr, fileProjectMachineNr, failureBitNr, timestamp, CASE WHEN timestampOutOfAlarm > @endDate OR timestampOutOfAlarm IS NULL THEN @endDate ELSE timestampOutOfAlarm END AS timestampOutOfAlarm INTO #tempTable1 FROM fctAlarmHistory WHERE (timestampOutOfAlarm >= @startDate OR timestampOutOfAlarm = NULL) AND timestamp < @endDate AND failureBitValue = 1 AND failureBitNr = 't2_13' AND fileProjectNr = @projectNr And fileProjectMachineNr = @ProjectMachineNr SELECT fctAlarmId, fileProjectNr, fileProjectMachineNr, failureBitNr, timestamp, CASE WHEN timestampOutOfAlarm > @endDate OR timestampOutOfAlarm IS NULL THEN @endDate ELSE timestampOutOfAlarm END AS timestampOutOfAlarm INTO #tempTable2 FROM fctAlarmHistory WHERE (timestamp BETWEEN @startDate AND @endDate) AND failureBitValue = 1 AND (failureBitNr LIKE'f%') AND fileProjectNr = @projectNr And fileProjectMachineNr = @ProjectMachineNr CREATE TABLE #tempTable3 ( ID int IDENTITY(1,1), timestamp datetime, timestampOutOfAlarm datetime ) DECLARE failure_Cursor CURSOR FOR SELECT timestamp, timestampOutOfAlarm FROM #tempTable2 ORDER BY timestamp ASC OPEN failure_Cursor -- Perform the first fetch. FETCH NEXT FROM failure_Cursor INTO @rij_timestamp, @rij_timestampOutOfAlarm INSERT INTO #tempTable3 (timestamp, timestampOutOfAlarm) VALUES(@rij_timestamp,@rij_timestampOutOfAlarm) -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN PRINT @rij_timestamp IF @rij_timestamp <= (SELECT TOP 1 timestampOutOfAlarm FROM #tempTable3 ORDER BY timestamp DESC) BEGIN IF @rij_timestampOutOfAlarm > (SELECT TOP 1 timestampOutOfAlarm FROM #tempTable3 ORDER BY timestamp DESC) BEGIN UPDATE #tempTable3 SET timestampOutOfAlarm = @rij_timestampOutOfAlarm WHERE ID = (SELECT TOP 1 ID FROM #tempTable3 ORDER BY timestamp DESC) END END ELSE INSERT INTO #tempTable3 (timestamp, timestampOutOfAlarm) VALUES(@rij_timestamp,@rij_timestampOutOfAlarm) FETCH NEXT FROM failure_Cursor INTO @rij_timestamp, @rij_timestampOutOfAlarm END CLOSE failure_Cursor DEALLOCATE failure_Cursor -- Select the failure time. SELECT SUM(DATEDIFF("SECOND", CASE WHEN tt3.timestamp < @startDate THEN @startDate ELSE tt3.timestamp END, CASE WHEN tt3.timestampOutOfAlarm > tt1.timestampOutOfAlarm THEN tt1.timestampOutOfAlarm ELSE tt3.timestampOutOfAlarm END)) AS DownTime FROM #tempTable3 tt3 INNER JOIN #tempTable1 tt1 ON tt3.timestamp BETWEEN tt1.timestamp AND tt1.timestampOutOfAlarm </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POSQL Server query buildup
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USTimo002
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POSQL Server query buildup
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTAcceptedByOriginator
CommentsPostId
1. This table or related slice is empty.

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.