September 15, 2014 at 1:41 pm
I am looking to find the downtime of my machines. All the information is being pulled from SQL views and tables. When the machines is ON it is =1. When it is off it is =0.
Here is a sample of what it looks like queried.
2014-09-15 09:53:38.6730000 LimitSwitch 1
2014-09-15 10:17:30.7280000 LimitSwitch 0
I am using Visual Studio 2013 and SQL MS 2012
I want to insert a piece of code into my Stored procedure that lets me see how long it was in the 0 state. I have everything broken down into three shifts. 6:30 am....2:30 pm....and 10:30 pm.
I have this stored procedure running through my Visual Studio program. I will be using DownTime for that column name. I also have a downtime column in my table in SQL.
September 16, 2014 at 12:02 pm
I believe what you have is a classic gaps and islands problem for which there have been many articles written. This search will point you to a few articles that should point you in the right direction.
Had you posted some more example data with your expected outcomes as mentioned in the first link in my signature I would have provided some code, but due to the time it would take to mock up an example, I can't do that right now.
Also you mention shifts, does this mean that you want downtime broken down by shift or by day?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply