asked on

SQL LAG Issue

We have 5 machines, each machine inserts a record to a SQL database every second.
At the record generation time, it writes the order number, machineID, a machine status (either U up or D down) and the time period).
Machine data is attached.

Ref:
(note that ordernumber is 7 digits and the next digit is machineID, so first line order is 2633448 and machine is 2)
OrderNumber      Machine ID      Status      TimePeriod
2633448      2      U      2018-12-07 08:08:13.000
2601441      4      U      2018-12-07 08:08:12.000
2637183      3      D      2018-12-07 08:08:08.000
2624657      5      U      2018-12-07 08:08:08.000
2633448      2      U      2018-12-07 08:08:03.000
2601441      4      U      2018-12-07 08:08:02.000
2637183      3      D      2018-12-07 08:07:59.000
2624657      5      U      2018-12-07 08:07:59.000
2633448      2      U      2018-12-07 08:07:54.000
2601441      4      U      2018-12-07 08:07:52.000

What I'm trying to accomplish is a report of time-based data showing when the machine switches status and the total duration of the status.
Something like this:
OrderNumber      Machine ID      Status      Status End      Status Start      Duration
2633448      2      U      2018-12-07 08:08:13.000      2018-12-07 07:57:10.000      0:11:03
2633448      2      D      2018-12-07 07:57:49.000      2018-12-07 07:57:42.000      0:00:07
2633448      2      U      2018-12-07 07:57:41.000      2018-12-07 07:57:11.000      0:00:30

As part of the first step, my thinking is to find the points where the status changed using a lag.
Like this...

Select 
[OrderNumber],
[MachineNumber],
Status,
Lag(Timeperiod, 1) Over (partition by Ordernumber, [Status] order by OrderNumber, TimePEriod) As StatusChange,
TimePeriod
from #MyTempdata
order by OrderNumber,Timeperiod

Open in new window

This however is sometimes working:

then later on in the same result, it doesn't:

Any help that can be provided would be awesome.
If I'm making this way too complicated and there is a more efficient way, I'm open to suggestions.

Thanks in advance.

awking00

Your sample data and what you indicate as what you're trying to accomplish do not match up (e.g The sample shows all of the status values for machine 2 of U). Perhaps you can provide some real sample data and what you would want as a result from that data (please post as text and not as a picture).

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kerrymr

ASKER

Ste5an,
Thanks for replying I'm looking at the result against the whole dataset.
You have the right idea, I just need to make sure it works with the data.

In regards to your question:
"What is the correct condition? Status change per machine and order or status change per machine?"

Only one order can run on one machine at one time. The table data contains all orders on all machines.
I think "Status change per machine and order" is correct.

Does this help?

K.

awking00

ste5an definitely has you on the right track although there will be some datetime math required to compute the duration. Also, are you looking for the duration that the machine was up or when it was down or both?

kerrymr

ASKER

You got it.
Thank you.

I think it was a lag and sorting problem I had an issue with.

Happy Holidays.
K