2

I am working on another report using SSRS via Visual Studio 2010. I am a rookie with SQL but things are starting to make sense as the weeks go on.

Purpose is to create a report that displays the Batch up time / downtime calculated from the timestamps taken from a SQL server database. The time stamp is represented as a date/time input, an example, 2018-02-09 14:43:29.193.

There are 4 Event ID's 1-4 that must be met to trigger a time stamp in the database. Each batch has it's own 3 digit integer ID.

1-Batch Start, Not running production, but system is initiated. (Down)
2-Production Start, Running production (Up Time)
3-Production Stop, Machine is running, but not producing (Down)
4-Batch Stop, Production complete. (Down)

The difference between,

2&1 = Downtime
4&1 = Downtime
2&3 = Up time
If 4 is after 2 = Up time
If 4 is after 3 = Downtime

Database

I have done my best with this report with limited knowledge, and in the report I have created parameters to choose dates for the report, along with asking the user to pick the Batch ID along with Batch 1-4 Event's, however I am not sure how to tackle the calculated fields.

Below is the SQL code,

 /****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 
    [Productio_CrtNo]
  , [BatchEvent_ID]
  , [BatchEvent_TIME]
  , [Operator_ID]
  , [DieRolls_ID]
  , [Batch_ID]
  , [NoOfPockets]
  , [WetCapsulesWeight]
FROM [SCM_DataCollecion].[dbo].[ProductionTable]

Current Report

I have been reading up on pivoting tables and I think this may be the way to go in calculated the up / downtime, however I need a bit of help on this part.

Thanks again

aduguid
  • 3,099
  • 6
  • 18
  • 37
Jester
  • 21
  • 2
  • 1
    Was there a question? – Harry Feb 28 '18 at 20:46
  • Hey, my question is what is the best way to calculate the down / uptime based on the data set. I would like to add in either a calculated field which outputs the downtime/uptime in seconds for a specific batch, and or have a drop down parameter which asks the user based off of this chosen batch to select uptime/downtime or both. Sorry if it's confusing, it's confusing me as well. – Jester Feb 28 '18 at 20:58
  • A better way to explain this would be to show desired result with sample data – Harry Feb 28 '18 at 21:02
  • You should also update your question to include your question. – Wes H Feb 28 '18 at 21:35
  • Hi, I'm running SQL server 2012 standard edition. Thx – Jester Mar 02 '18 at 13:29

1 Answers1

0

Here are 3 ways I could think of to solve this.

  1. Use a ranking in the SQL based on the 3 digit id number
  2. Use a Pivot in the SQL based on the event id
  3. Use a matrix to do the pivot and perform the calculations in the report.
aduguid
  • 3,099
  • 6
  • 18
  • 37
  • Hi, thanks for the feedback. I included my SQL query along with how the report currently looks. Thanks again, all the best – Jester Mar 01 '18 at 14:18