Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
lahsiv2004
Starting Member
13 Posts |
Posted - 2013-06-13 : 11:12:00
|
Hi,I have a requirement based on 2 parts to determine a row of data in a table through SSIS :a. Work out the LEG_SEQ_NBR of the VESSEL, VOYAGE and LEG already stored in table1.To do this join table1 and join table2 through BL_ID column.b. Work out the 2ndVESSEL, 2ndVOYAGE and 2ndLEG.Once we have identified the LEG_SEQ_NBR of the VESSEL,VOYAGE, LEG already stored in table1 we need to add '1' to this value and then find that LEQ_SEQ_NBR in table2.The DDL of table1 and table2 along with the test data are as below:------------------------------------------------------------CREATE TABLE [dbo].[table1]([BL_ID] [decimal](10, 0) NOT NULL,[VESSEL] [nvarchar](10) NULL,[VOYAGE] [nvarchar](12) NULL,[LEG] [nchar](3) NULL,[BLNO] [nvarchar](17) NULL) ON [PRIMARY]CREATE TABLE [dbo].[table2]([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,) ON [PRIMARY]INSERT INTO [table1]VALUES('1','CEUR','032E','E','21')INSERT INTO [table2]VALUES('1','1','CEUR','032E','E')INSERT INTO [table2]VALUES('1','2','MARB','794S','S')---------------------------------------------------------For Example:BLNO = 21 and BL_ID = 1Current Vessel/Voyage/Leg in table1 = CEUR 032E ELEG_SEQ_NBR of this Vessel/Voyage/Leg in table1 = '1'Therefore if we add '1' to this value we'd be looking for LEG_SEQ_NBR '2' as the 2ndVESSEL,2ndVOYAGE,2ndLEG. In this case that would = MARB 794S S Does somebody pls know how this can be worked out through an SSIS job ?Thanks. |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-06-13 : 12:44:57
|
I'm not sure I'm understanding the requirement, but maybe this will help:SELECT *FROM ( SELECT Table2.VESSEL_CD, Table2.VOYAGE_CD, Table2.LEG_CD, ROW_NUMBER() OVER (PARTITION BY Table2.BL_ID ORDER BY Table2.LEG_SEQ_NBR) AS RowNum FROM Table1 INNER JOIN table2 ON Table1.BL_ID = Table2.BL_ID ) AS TWHERE RowNum = 2 |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-06-13 : 12:55:57
|
Or this?[CODE]DECLARE @LegSN numeric;DECLARE @BL_ID Numeric;SELECT @LegSN = [LEG_SEQ_NBR], @BL_ID = T1.BL_ID FROM Table2 T2 inner join Table1 T1 on T1.BL_ID = T2.BL_ID and VESSEL = VESSEL_CD and VOYAGE = VOYAGE_CD;SELECT @LegSN;SET @LegSN = @LegSN + 1;SELECT * FROM Table2 where [LEG_SEQ_NBR] = @LegSN and BL_ID = @BL_ID;[/CODE] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-14 : 01:17:39
|
As per what I understood this is what you're afterSELECT Table2.VESSEL_CD, Table2.VOYAGE_CD, Table2.LEG_CD, Table1.[VESSEL], Table1.[VOYAGE], Table1.[LEG], t1.[VESSEL] AS [2ndVESSEL], Table1.[VOYAGE] AS [2ndVOYAGE], Table1.[LEG] AS [2ndLEG] FROM Table1 INNER JOIN table2 ON Table1.BL_ID = Table2.BL_ID INNER JOIN table2 t1 ON t2.LEG_SEQ_NBR = table2.LEG_SEQ_NBR + 1 INNER JOIN table1 t1 ON t1.BL_ID = t2.BL_ID ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
lahsiv2004
Starting Member
13 Posts |
Posted - 2013-09-09 : 05:45:26
|
quote: Originally posted by Lamprey I'm not sure I'm understanding the requirement, but maybe this will help:SELECT *FROM ( SELECT Table2.VESSEL_CD, Table2.VOYAGE_CD, Table2.LEG_CD, ROW_NUMBER() OVER (PARTITION BY Table2.BL_ID ORDER BY Table2.LEG_SEQ_NBR) AS RowNum FROM Table1 INNER JOIN table2 ON Table1.BL_ID = Table2.BL_ID ) AS TWHERE RowNum = 2
Thanks very much for your code, it worked !! Sorry for the very late reply though. |
|
|
|
|
|
|
|