Creating a Stored Procedure to group data together 2

[smuthcrmnl777]

I have a unit that will start off with zero or close to zero lbs and when it completes there will be either 1500 lbs or 2000 lbs. During the time it's weight is growing there are things that could go wrong and make the unit stop growing. This is indicated by either a Slow Stop or a Fast Stop. For every unit there is at least one Line_Start and one End of Reel. I need to separate this data and give each unit a number so I can easily call on this data. Can someone give me an idea of how to build a stored procedure to look at this data and then name the unit? Below is what the data looks like in an SQL table.

tRSSQL_TRANS	nWEIGHT	bLINE_START	bSLOW_STOP	bFAST_STOP	bEND_OF_REEL	nLINE
6/9/2006 0:58	0         1              0             0             0               1
6/9/2006 0:58	0         0              1             0             0               1
6/9/2006 0:59	2         1              0             0             0               1
6/9/2006 1:00	21        0              0             1             0               1
6/9/2006 1:02	25        1              0             0             0               1
6/9/2006 2:10	1140      0              1             0             0               1
6/9/2006 2:14	1144      1              0             0             0               1
6/9/2006 2:16	1178      0              0             1             0               1
6/9/2006 2:20	1182      1              0             0             0               1
6/9/2006 2:41	1517      0              1             0             1               1
***************************************************************************************
6/9/2006 2:43	0         1              0             0             0               1
6/9/2006 2:44	10        0              1             0             0               1
6/9/2006 2:48	16        1              0             0             0               1
6/9/2006 3:50	1028      0              1             0             0               1
6/9/2006 3:53	1033      1              0             0             0               1
6/9/2006 4:24	1517      0              1             0             1               1
***************************************************************************************
6/9/2006 4:26	0         1              0             0             0               1
6/9/2006 5:16	806       0              1             0             0               1
6/9/2006 5:19	810       1              0             0             0               1
6/9/2006 6:02	1517      0              1             0             1               1
***************************************************************************************
6/9/2006 6:04	0         1              0             0             0               1
6/9/2006 7:37	1516      0              1             0             1               1

 

[gmmastros]

When 'End Of Reel' goes from 0 to 1, the 'group' ends, and when the 'End or Reel' goes from 1 to 0, the next group starts? Basically, is it 'end or reel' that identifies the group?

-George

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

 

[smuthcrmnl777]

Absolutely correct!!

 

[smuthcrmnl777]

I need to be able to identify that group with a field that will number each record for that group.

 

[gmmastros]

Here's a start for you. The code I show below should help you identify where 1 group starts and end. With the sample data you provide, this appears to work correctly. Once you know where the group starts and end, the rest should be fairly simple.

To accomplish this, I put the relevant data in to a table variable with an identity column so that I can perform a self join.

The @Temp stuff I show here is just so that I can dummy up some data. In your final query, you'll want to replace @Temp with your table name.

[green]-- building dummy data[/green]
Declare @Temp 
Table   (tRSSQL_TRANS DateTime, 
        nWEIGHT Integer,
        bLINE_START Bit,
        bSLOW_STOP Bit,
        bFAST_STOP Bit,
        bEND_OF_REEL Bit,
        nLINE Integer
		)

Insert Into @Temp Values('6/9/2006 0:58',0   ,1,0,0,0,1)
Insert Into @Temp Values('6/9/2006 0:58',0   ,0,1,0,0,1)
Insert Into @Temp Values('6/9/2006 0:59',2   ,1,0,0,0,1)
Insert Into @Temp Values('6/9/2006 1:00',21  ,0,0,1,0,1)
Insert Into @Temp Values('6/9/2006 1:02',25  ,1,0,0,0,1)
Insert Into @Temp Values('6/9/2006 2:10',1140,0,1,0,0,1)
Insert Into @Temp Values('6/9/2006 2:14',1144,1,0,0,0,1)
Insert Into @Temp Values('6/9/2006 2:16',1178,0,0,1,0,1)
Insert Into @Temp Values('6/9/2006 2:20',1182,1,0,0,0,1)
Insert Into @Temp Values('6/9/2006 2:41',1517,0,1,0,1,1)
Insert Into @Temp Values('6/9/2006 2:43',0   ,1,0,0,0,1)
Insert Into @Temp Values('6/9/2006 2:44',10  ,0,1,0,0,1)
Insert Into @Temp Values('6/9/2006 2:48',16  ,1,0,0,0,1)
Insert Into @Temp Values('6/9/2006 3:50',1028,0,1,0,0,1)
Insert Into @Temp Values('6/9/2006 3:53',1033,1,0,0,0,1)
Insert Into @Temp Values('6/9/2006 4:24',1517,0,1,0,1,1)
Insert Into @Temp Values('6/9/2006 4:26',0   ,1,0,0,0,1)
Insert Into @Temp Values('6/9/2006 5:16',806 ,0,1,0,0,1)
Insert Into @Temp Values('6/9/2006 5:19',810 ,1,0,0,0,1)
Insert Into @Temp Values('6/9/2006 6:02',1517,0,1,0,1,1)
Insert Into @Temp Values('6/9/2006 6:04',0   ,1,0,0,0,1)
Insert Into @Temp Values('6/9/2006 7:37',1516,0,1,0,1,1)

[green]-- The query[/green]
Declare @Data 
Table   (RowId Integer Identity(1,1),
		tRSSQL_TRANS DateTime, 
        bEND_OF_REEL bit,
        nLINE Integer
		)

Insert Into @Data(tRSSQL_TRANS, bEND_OF_REEL, nLINE)
Select tRSSQL_TRANS, bEND_OF_REEL, nLINE 
From   @Temp
Order By nLine, tRSSQL_TRANS

Select  A.nLine,
        A.StartGroup,
        Min(D.tRSSQL_TRANS) As EndGroup
From    (
        Select Min(tRSSQL_TRANS) As StartGroup, 
               nLine
        From   @Data
        Group By nLine
		
        Union 
		
        Select B.tRSSQL_TRANS, B.nLine
        From   @Data A 
               Inner Join @Data B 
                 On  A.RowId = B.RowId -1 
                 And A.nLine = B.nLine
        Where  A.bEND_OF_REEL = 1
		       And B.bEND_OF_REEL = 0
        ) A
        Inner Join @Data D 
          On A.nLine = D.nLine
          And A.StartGroup < D.tRSSQL_TRANS
          And D.bEND_OF_REEL = 1
Group By A.StartGroup, A.nLine

-George

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

 

[smuthcrmnl777]

Where can I modify the query so it will compile all of the lines I have instead of just line #1?

 

[smuthcrmnl777]

nevermind. You are the man!!

 

[smuthcrmnl777]

I have taken your code and added my insert to a table. I am getting an error on the datatype for nline. I thought it was Integer, but I get an error. Here is my code.

Declare @Data 
Table   (RowId Integer Identity(1,1),
        tRSSQL_TRANS DateTime, 
        bEND_OF_REEL bit,
        nLINE Integer
        )

Insert Into @Data(tRSSQL_TRANS, bEND_OF_REEL, nLINE)
Select tRSSQL_TRANS, bEND_OF_REEL, nLINE 
From   vM_FR_STOPS_Grouping
Order By nLine, tRSSQL_TRANS

Select  A.nLine,
        A.StartGroup,
        Min(D.tRSSQL_TRANS) As EndGroup
From    (
        Select Min(tRSSQL_TRANS) As StartGroup, 
               nLine
        From   @Data
        Group By nLine
        
        Union 
        
        Select B.tRSSQL_TRANS, B.nLine
        From   @Data A 
               Inner Join @Data B 
                 On  A.RowId = B.RowId -1 
                 And A.nLine = B.nLine
        Where  A.bEND_OF_REEL = 1
               And B.bEND_OF_REEL = 0
        ) A
        Inner Join @Data D 
          On A.nLine = D.nLine
          And A.StartGroup < D.tRSSQL_TRANS
          And D.bEND_OF_REEL = 1
Group By A.StartGroup, A.nLine

[b]--NEWCODE[/b]

[COLOR=red]
Insert Into tM_FR_WIP_UnitID_FastStops(nLINE, StartGroup, EndGroup)
Select tRSSQL_TRANS, bEND_OF_REEL, nLINE 
From   @Data
Order By nLine, tRSSQL_TRANS
[/color]

 

[SQLDenis]

shouldn't this

Insert Into tM_FR_WIP_UnitID_FastStops(nLINE, StartGroup, EndGroup)
Select tRSSQL_TRANS, bEND_OF_REEL, nLINE
From @Data
Order By nLine, tRSSQL_TRANS

be this?

Insert Into tM_FR_WIP_UnitID_FastStops(nLINE, StartGroup, EndGroup)
Select nLINE,tRSSQL_TRANS, bEND_OF_REEL
From @Data
Order By nLine, tRSSQL_TRANS

notice how in the tM_FR_WIP_UnitID_FastStops table the nLine column is first but in your select it is last?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[smuthcrmnl777]

I detected a slight problem with the query. Since the table is too big to test this query, I had to only return data for the past 8 hours. So the query could potentially slice the middle a group of records. (example below) When this happens, your query then detects 2 start times with the same end time.

Can you help me eliminate this? Or can you guide me to how I should have my view (vM_FR_STOPS_Grouping) setup (below)?

tRSSQL_TRANS     nWEIGHT     bLINE_START     bSLOW_STOP     bFAST_STOP     bEND_OF_REEL     nLINE
6/9/2006 3:50    1028        0               1              0              0                1
6/9/2006 3:53    1033        1               0              0              0                1
6/9/2006 4:24    1517        0               1              0              1                1

@data displays

nLine        StartGroup        EndGroup
1            06/09/2006 3:50   06/09/2006 4:24
1            06/09/2006 3:53   06/09/2006 4:24

vM_FR_STOPS_Grouping

SELECT     tRSSQL_TRANS, nWEIGHT, bLINE_START, bSLOW_STOP, bFAST_STOP, bEND_OF_REEL, nLINE
FROM         Mentor_Bizware.BizBikesUser.tM_FR_STOPS
WHERE     (tRSSQL_TRANS >= DATEADD(hh, - 8, GETDATE()))