creating code that will deciphers multiple records and groups them

[smuthcrmnl777]

I have some data that some engineers would like to work with, but it is very time consuming to compile what they need.

I have a machine that will stop because of a problem within that machine. Then the operator will fix the problem and then start the machine up again. I have numerous machines that record this data and the data resides in one table.

I need to have some code look at this table and take when it stop and the next time it starts up again and tell me how long it took to start up.

For Example, line 1 at 8 am stopped and then started up again at 8:01. I should get a reading of
Line 1, 1 (min).

Sometimes a line will get multiple stops before actually starting up. I need to find the first time it stopped to when it actually started up.

For Example, Line 2 Stopped at 8:02,8:03 and 8:04 and then started up at 8:07. I should get a reading of Line 2, 5 (min).

Also keep in mind that any line can and will have multiple start ups due to other problems. I am only focusing on one problem right now.

TimeStamp	Line	Stop	Start
5/17/06 8:00 AM	1	1	
5/17/06 8:00 AM	2	1	
5/17/06 8:01 AM	1		1
5/17/06 8:01 AM	2		1
5/17/06 8:02 AM	1	1	
5/17/06 8:02 AM	2	1	
5/17/06 8:03 AM	1	1	
5/17/06 8:03 AM	2	1	
5/17/06 8:04 AM	1	1	
5/17/06 8:04 AM	2	1	
5/17/06 8:07 AM	1		1
5/17/06 8:07 AM	2		1
5/17/06 8:08 AM	1		1
5/17/06 8:08 AM	2		1
5/17/06 8:09 AM	1		1
5/17/06 8:09 AM	2		1

Thanks!!

 

[smuthcrmnl777]

Can anyone help me on this?

 

[gmmastros]

What you are asking for is not easy. As such, the code I am posting should provide you with a starting point. Consider it a gentle nudge in the right direction. Work with this code, massage it, mold it, make it work for you.

This code starts off with creating a table variable that would represent sample data. You may find it easier to work with a smaller recordset to begin with. In the end, the table variable @Data should be replaced with your actual table.

Declare @Data Table(TimeStamp DateTime, Line Integer, Stop Integer, Start Integer)

Insert Into @Data
          Select '5/17/06 8:00 AM',    1,    1,    NULL
Union All Select '5/17/06 8:00 AM',    2,    1,    NULL
Union All Select '5/17/06 8:01 AM',    1,    NULL,    1
Union All Select '5/17/06 8:01 AM',    2,    NULL,    1
Union All Select '5/17/06 8:02 AM',    1,    1,    NULL
Union All Select '5/17/06 8:02 AM',    2,    1,    NULL
Union All Select '5/17/06 8:03 AM',    1,    1,    NULL
Union All Select '5/17/06 8:03 AM',    2,    1,    NULL
Union All Select '5/17/06 8:04 AM',    1,    1,    NULL
Union All Select '5/17/06 8:04 AM',    2,    1,    NULL
Union All Select '5/17/06 8:07 AM',    1,    NULL,    1
Union All Select '5/17/06 8:07 AM',    2,    NULL,    1
Union All Select '5/17/06 8:08 AM',    1,    NULL,    1
Union All Select '5/17/06 8:08 AM',    2,    NULL,    1
Union All Select '5/17/06 8:09 AM',    1,    NULL,    1
Union All Select '5/17/06 8:09 AM',    2,    NULL,    1

[green]-- End of sample data

-- Create a table variable that has a rowid column so we 
-- can sort the data and get adjacent rows together
-- so that the self join works properly[/green]

Declare @Temp 
Table   (RowId Integer Identity(1,1), 
        TimeStamp DateTime, 
        Line Integer, 
        Stop Integer, 
        Start Integer)

Insert Into @Temp(TimeStamp, Line, Stop, Start)
Select TimeStamp, Line, Stop, Start
From   @Data
Order By Line, TimeStamp

Select 	A.Line, B.TimeStamp As StartTime
From 	@Temp A
		Inner join @Temp B 
			On  A.RowId = B.RowId - 1
			And A.Line = B.Line
Where   A.Start Is NULL
		And B.Start Is Not NULL

Select	Line, StopTime
From	(
		Select	A.Line, B.TimeStamp As StopTime
		From	@Temp A
				Inner join @Temp B
					On  A.RowId = B.RowId - 1
					And A.Line = B.Line
		Where	A.Stop Is NULL
				And B.Stop Is Not NULL
		
		Union All
		
		[green]-- This part accomodates records where the line starts in the 'stop' mode[/green]
		Select	A.Line, A.TimeStamp
		From	@Temp A
				Inner join (
					Select	Line, Min(TimeStamp) As MinTimeStamp
					From	@Temp
					Group By Line
					) B On A.Line = B.Line And A.TimeStamp = B.MinTimeStamp
		Where	A.Stop = 1
		) Data
Order By Line, StopTime

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom