smuthcrmnl777
Technical User
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.
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
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