x

sigle event recorded as multiple events due to date change

Hello all This is my first post to any forum. I have a problem that I cannot seem to find the solution for its kind of a strange one so ill try to explain.

I have a databse that track information such as when a piece of equipment breaks down and when it is put back into production. We run 24 hours a day consisting of two 12 hour shifts. Here is where the problem starts:

Say a piece of equipment goes down 2 hours before the end of shift A and repairs are not complete when shift B begins. I get : 1 record showing the couple of hours on the first date, another record for shift B. if the equipment stays down for a few days I can have many records depictions a single event.

What I need to be able to do is query the table. And retrieve the intial time which the machine went down, and the time that the achine came up which might be days later and quite a few records.

Any Ideas?

Thanks in advance for any suggestions

more ▼

asked Feb 24 '10 at 06:08 PM in Default

Clint gravatar image

Clint
100 6 6 7

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You could try to use an aggregation based on the machines identifier and some other identifier that indicates whether the machine is down or repaired.

It is difficult to give more than that not knowing more about your table structure and what queries you are already trying to deal with to make it work.

more ▼

answered Feb 24 '10 at 07:21 PM

CirqueDeSQLeil gravatar image

CirqueDeSQLeil
4k 10 11 15

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x111

asked: Feb 24 '10 at 06:08 PM

Seen: 659 times

Last Updated: Feb 24 '10 at 06:08 PM