Reel Data groupings need improvement

[smuthcrmnl777]

http://www.tek-tips.com/threadminder.cfm?pid=183

This is a continuation of the above link.

I have the below query inserting into table tM_FR_WIP_UnitID_FastStops and I would like to have the query JOIN on ENDGROUP and LINE to the table so it can determine whether or not the data is there and to be ignored.

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

Insert Into tM_FR_WIP_UnitID_FastStops(nLINE, StartGroup, EndGroup)

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

 

[gmmastros]

Try this...

Select  A.nLine,
        A.StartGroup,
        Min(D.tRSSQL_TRANS) As EndGroup
From    (
        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]

I get 3 errors

Server: Msg 207, Level 16, State 3, Line 15
Invalid column name 'StartGroup'.
Server: Msg 207, Level 16, State 1, Line 15
Invalid column name 'StartGroup'.
Server: Msg 207, Level 16, State 1, Line 15
Invalid column name 'StartGroup'.

 

[gmmastros]

Sorry, I forgot the Column Alias

Select  A.nLine,
        A.StartGroup,
        Min(D.tRSSQL_TRANS) As EndGroup
From    (
        Select B.tRSSQL_TRANS[!] As StartGroup[/!], 
               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]

Below in Red is what I added to your original code this morning. I think I just can not visual what this code is doing for me. Using this code it produces NO results. I am sure the logic is off, but I know that for instance my line 1 has produced 2 more reels since I first Inserted the table. I commented the Insert so I can test the code. It is not part of the solution. Pleas else me know what it is I am doing wrong.

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

--Insert Into tM_FR_WIP_UnitID_FastStops(nLINE, StartGroup, EndGroup)

Select  A.nLine,
        A.StartGroup,
        Min(D.tRSSQL_TRANS) As EndGroup
From    [COLOR=red][b]tM_FR_WIP_UnitID_FastStops RIGHT OUTER JOIN

	([/b][/color]
	(
        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
	[COLOR=red][b])
	ON tM_FR_WIP_UnitID_FastStops.EndGroup = EndGroup
Where	(tM_FR_WIP_UnitID_FastStops.EndGroup IS NULL)[/b][/color]
Group By A.StartGroup, A.nLine

 

[gmmastros]

Which table are you referring to? @Data ?

@Data is a table variable. As soon as the query finishes, @Data is gone. So, you need to insert in to it every time, but more importantly, it's always empty at the beginning of the stored procedure.

-George

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