ChrisM@Work (1/30/2015)

---

Try this:

CREATE TABLE #tbl_TMIS_DATASET (

[BL_ID] [decimal](10, 0) NOT NULL,

[VESSEL] [nvarchar](10) NULL,

[VOYAGE] [nvarchar](12) NULL,

[LEG] [nchar](3) NULL,

)

INSERT INTO #tbl_TMIS_DATASET

VALUES('19516116','HJAMRC','0010E','E')

CREATE TABLE #MG_BL_ITINERARY(

[BL_ID] [numeric](10, 0) NULL,

[LEG_SEQ_NBR] [numeric](3, 0) NULL,

[VESSEL_CD] [varchar](10) NULL,

[VOYAGE_CD] [varchar](12) NULL,

[LEG_CD] [char](1) NULL,

)

INSERT INTO #MG_BL_ITINERARY

VALUES('19516116','2','HJAMRC','0010E','E')

INSERT INTO #MG_BL_ITINERARY

VALUES('19516116','1','ROBERT','3814S','S')

INSERT INTO #MG_BL_ITINERARY

VALUES('19516116','3','YMUNCN','08E','E')

INSERT INTO #MG_BL_ITINERARY

VALUES('19516116','4','JTWR','002S','S')

SELECT

t.[BL_ID], t.[VESSEL], t.[VOYAGE], t.[LEG],

i.LEG_SEQ_NBR,

x.*

FROM #tbl_TMIS_DATASET t

INNER JOIN #MG_BL_ITINERARY i

ON t.BL_ID = i.BL_ID

AND t.VESSEL = i.VESSEL_CD

AND t.VOYAGE = i.VOYAGE_CD

AND t.LEG = i.LEG_CD

OUTER APPLY (

SELECT VESSEL_CD, VOYAGE_CD, LEG_CD

FROM #MG_BL_ITINERARY i2

WHERE i2.BL_ID = t.BL_ID

AND LEG_SEQ_NBR = i.LEG_SEQ_NBR + 1

) x