Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating a Stored Procedure to group data together 2

Status
Not open for further replies.
smuthcrmnl777

smuthcrmnl777

Technical User
Jan 16, 2006
104
US
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.

Code: 
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
I need to be able to identify that group with a field that will number each record for that group.
 
Upvote 0 Downvote
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.

Code: 
[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
 
Upvote 0 Downvote
Where can I modify the query so it will compile all of the lines I have instead of just line #1?
 
Upvote 0 Downvote
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.

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]
 
Upvote 0 Downvote
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:
Personal Blog:
 
Upvote 0 Downvote
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)?

Code: 
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
Code: 
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
Code: 
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()))


 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
171
grnzbra
grnzbra
coorsman
  • Locked
  • Question
Column Data Type. 2
Replies
9
Views
300
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
375
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
How to add a Letter to datepart
Replies
5
Views
290
Andrzejek
Andrzejek
Cpreston
  • Locked
  • Question
Seconds to minutes 2
Replies
5
Views
453
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top