script to find down time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

script to find down time

Author

Topic

jobimathew4
Starting Member

8 Posts

Posted - 2013-08-07 : 16:56:20

I have a table as seen below and need to calculate the downtime in a given period. Does anyone have any ideas on how this can be done? Please let me know if the posting is in the wrong location.

dateandtime Equipment No. Status
1/2/2013 ! 4 ! 0
1/5/2013 ! 3 ! 1
1/8/2013 ! 8 ! 0
1/3/2013 ! 5 ! 1
1/2/2013 ! 1 ! 0
1/4/2013 ! 4 ! 1

The ! is my attempt to make a line between columns

Jmathew

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 17:02:25

quote:
Originally posted by jobimathew4

I have a table as seen below and need to calculate the downtime in a given period. Does anyone have any ideas on how this can be done? Please let me know if the posting is in the wrong location.

dateandtime Equipment No. Status
1/2/2013 4 0
1/5/2013 3 1
1/8/2013 8 0
1/3/2013 5 1
1/2/2013 1 1
1/4/2013 4 0

Jmathew

What is the expected result for this set of input data?

jobimathew4
Starting Member

8 Posts

Posted - 2013-08-07 : 17:07:36

The expected output is Equipment # in first column and Downtime(hours or minutes)in the next.

Jmathew

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 17:11:15

quote:
Originally posted by jobimathew4

The expected output is Equipment # in first column and Downtime(hours or minutes)in the next.

Jmathew

That is not what I meant. I was looking for actual data to infer the rule you would use to calculate the downtime. For example, for Equipment #4, what is the downtime? is it 1, 2 or something else?

In other words, if you had to sit down with a pencil and paper, how would you calculate the downtime?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-07 : 17:12:41

So, you do realize that we know nothing about your data, right? What is a Status of 1; Is that up or down or something else? How do you calculate downtime given your sample data? Can you put your sample data in a consumable format?

Here are some links that can help you when prepaing your question so we can help you better:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jobimathew4
Starting Member

8 Posts

Posted - 2013-08-07 : 17:22:59

Thankyou for your responses. Do apologize for not clarifying it.
CREATE TABLE [dbo].[Equip](
[DateAndTime] [datetime] NULL,
[EqiupNumber] [int] NULL,
[TagNumber] [int] NULL,
[TagValue] [float] NULL
) ON [PRIMARY]
GO

INSERT INTO [MyTest].[dbo].[Equip]
VALUES
('1/2/2013' ,4,1,0),
('1/5/2013' ,3,1,1),
('1/8/2013' ,8,1,0),
('1/3/2013' ,5,1,1),
('1/2/2013' ,1,1,1),
('1/4/2013' ,4,1,0),
('1/5/2013' ,4,1,1),
('1/6/2013' ,3,1,1),
('1/5/2013' ,8,1,1)

Definitions for each column
Dateandtime is time it got recorded.
EquipNumber is for the number assinged to the equipment
TagNumber is the property number that we are monitoring
TagValue is the status of tagnumber for equipnumber
Tagvalue 0 means it is off and 1 means it is on.

We need to find how long the equipment was off. I do this calculation by subtracting the dateandtime for 1 with dateandtime for 0 but how do I take into consideration times that the status was recorded twice. Let me know if you guys have any other questions.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-08 : 14:13:48

What do you want the output to be, can you fill in the DownTime column below?
[CODE]
EquiNo Tag Status DateAndTime DownTime
1 1 1 2013-01-02 00:00:00.000
3 1 1 2013-01-05 00:00:00.000
3 1 1 2013-01-06 00:00:00.000
4 1 0 2013-01-02 00:00:00.000
4 1 0 2013-01-04 00:00:00.000
4 1 1 2013-01-05 00:00:00.000
5 1 1 2013-01-03 00:00:00.000
8 1 1 2013-01-05 00:00:00.000
8 1 0 2013-01-08 00:00:00.000
[/CODE]

jobimathew4
Starting Member

8 Posts

Posted - 2013-08-08 : 15:49:50

it should insert into the below table and the stored procedure is expected to run daily.

CREATE TABLE [dbo].[EqiupDowntime](
[DateandTime] [datetime] NULL,
[EquipNumber] [int] NULL,
[Downtime] [float] NULL
) ON [PRIMARY]

GO

quote:
Originally posted by MuMu88

What do you want the output to be, can you fill in the DownTime column below?
[CODE]
EquiNo Tag Status DateAndTime DownTime
1 1 1 2013-01-02 00:00:00.000
3 1 1 2013-01-05 00:00:00.000
3 1 1 2013-01-06 00:00:00.000
4 1 0 2013-01-02 00:00:00.000
4 1 0 2013-01-04 00:00:00.000
4 1 1 2013-01-05 00:00:00.000
5 1 1 2013-01-03 00:00:00.000
8 1 1 2013-01-05 00:00:00.000
8 1 0 2013-01-08 00:00:00.000
[/CODE]

Jmathew

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-08 : 16:29:42

quote:
Originally posted by jobimathew4

it should insert into the below table and the stored procedure is expected to run daily.

CREATE TABLE [dbo].[EqiupDowntime](
[DateandTime] [datetime] NULL,
[EquipNumber] [int] NULL,
[Downtime] [float] NULL
) ON [PRIMARY]

GO

quote:
Originally posted by MuMu88

What do you want the output to be, can you fill in the DownTime column below?
[CODE]
EquiNo Tag Status DateAndTime DownTime
1 1 1 2013-01-02 00:00:00.000
3 1 1 2013-01-05 00:00:00.000
3 1 1 2013-01-06 00:00:00.000
4 1 0 2013-01-02 00:00:00.000
4 1 0 2013-01-04 00:00:00.000
4 1 1 2013-01-05 00:00:00.000
5 1 1 2013-01-03 00:00:00.000
8 1 1 2013-01-05 00:00:00.000
8 1 0 2013-01-08 00:00:00.000
[/CODE]

Jmathew

In the above example Equipment 4 is off on the first and second day, what should the down time be ? For equipment 8, is the down time 72 hours ?

jobimathew4
Starting Member

8 Posts

Posted - 2013-08-08 : 17:56:59

Sorry, I meant to say Monthly not daily. But for this example we can run it from 1st to 8th. So equipment 4 should have down time of 96 hours (1/5/2013 -1/1/2013) and equipment 8 should have a downtime of 120 hours(01/05/2013 - 01/01/2013 + 01/08/2013). Let me know if you guys have anymore questions.

quote:
Originally posted by MuMu88

quote:
Originally posted by jobimathew4

it should insert into the below table and the stored procedure is expected to run daily.

CREATE TABLE [dbo].[EqiupDowntime](
[DateandTime] [datetime] NULL,
[EquipNumber] [int] NULL,
[Downtime] [float] NULL
) ON [PRIMARY]

GO

quote:
Originally posted by MuMu88

What do you want the output to be, can you fill in the DownTime column below?
[CODE]
EquiNo Tag Status DateAndTime DownTime
1 1 1 2013-01-02 00:00:00.000
3 1 1 2013-01-05 00:00:00.000
3 1 1 2013-01-06 00:00:00.000
4 1 0 2013-01-02 00:00:00.000
4 1 0 2013-01-04 00:00:00.000
4 1 1 2013-01-05 00:00:00.000
5 1 1 2013-01-03 00:00:00.000
8 1 1 2013-01-05 00:00:00.000
8 1 0 2013-01-08 00:00:00.000
[/CODE]

Jmathew

In the above example Equipment 4 is off on the first and second day, what should the down time be ? For equipment 8, is the down time 72 hours ?

Jmathew

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-08 : 18:06:03

Is it safe to assume equipment 1, 3 and 5 have zero downtime?

jobimathew4
Starting Member

8 Posts

Posted - 2013-08-08 : 18:39:17

Yes..

quote:
Originally posted by MuMu88

Is it safe to assume equipment 1, 3 and 5 have zero downtime?

Jmathew

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-08 : 18:59:52

How did you get 120 hours as down time for Equipment 8? It is off only between 01/05/2013 and 01/08/2013 ?

quote:
Originally posted by jobimathew4

Sorry, I meant to say Monthly not daily. But for this example we can run it from 1st to 8th. So equipment 4 should have down time of 96 hours (1/5/2013 -1/1/2013) and equipment 8 should have a downtime of 120 hours(01/05/2013 - 01/01/2013 + 01/08/2013). Let me know if you guys have anymore questions.

Jmathew

jobimathew4
Starting Member

8 Posts

Posted - 2013-08-09 : 13:26:32

Equipment 8 was off from 01/01/2013 to 01/05/2013 that is why we have a condition of 1 on 01/05/2013 because it was zero before 01/05/2013 and then it was off again on 01/08/2013. Let me know if you have any other questions.

quote:
Originally posted by MuMu88

How did you get 120 hours as down time for Equipment 8? It is off only between 01/05/2013 and 01/08/2013 ?

quote:
Originally posted by jobimathew4

Sorry, I meant to say Monthly not daily. But for this example we can run it from 1st to 8th. So equipment 4 should have down time of 96 hours (1/5/2013 -1/1/2013) and equipment 8 should have a downtime of 120 hours(01/05/2013 - 01/01/2013 + 01/08/2013). Let me know if you guys have anymore questions.

Jmathew

Jmathew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources