December 16, 2010 at 7:49 am
Helo Friends,
Could you please help me create a SQL query for one of the steps in the functional specs:
The scenario is - using fields from 2 tables I have to create a query which goes as :
''Assign Tradelane to each BL_ID (using 'NCV_BL_DHL_TEMP' table)
fields - POL_COUNTRY_CD and POD_COUNTRY_CD ('NCV_BL_DHL_TEMP' table) and
COUNTRY_CD from 'DHL_TRADE_ASSIGNMENT' table. Also, any BL_ID where it is not possible to map/set a tradelane - set tradelane as 'OTHERS'.''
I have attached the structure of the 2 tables which are to be used for this along with the Tradelane mapping document.
Thanks in advance for your help in this regard.
Regards,
Paul
December 16, 2010 at 8:06 am
I can't open Office documents from a non-secure source. Can you provide the table definitions and sample data as DDL statements and insert statements?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 16, 2010 at 8:27 am
Thank you for your response, GSquared. Please find the table definitions below:
NCV_BL_DHL_TEMP-
CREATE TABLE [dbo].[NCV_BL_DHL_TEMP]
(
[BL_ID] [decimal](10, 0) NOT NULL,
[BL_NUM] [nvarchar](13) NULL,
[CP_GROUP_CD] [nvarchar](30) NULL,
[POL_COUNTRY_CD] [nvarchar](2) NULL,
[POD_COUNTRY_CD] [nvarchar](2) NULL,
[SAISAN_VESSEL_CD] [nvarchar](6) NULL,
[SAISAN_VOYAGE_CD] [nvarchar](6) NULL,
[SAISAN_LEG_CD] [nvarchar](1) NULL,
[DEPART_ACTUAL_DT] [datetime] NULL,
[TEU] [decimal](10, 0) NULL
)
DHL_TRADE_ASSIGNMENT-
CREATE TABLE [dbo].[DHL_TRADE_ASSIGNMENT]
(
[COUNTRY_CD] [nvarchar](2) NOT NULL,
[COUNTRY_DSC] [nvarchar](50) NULL,
[REGION_TRADE] [nvarchar](3) NULL,
[SUB_REGION] [nvarchar](50) NULL
)
The Tradelane definitions are based on this sample data-
TRADELANE-----------------POL_COUNTRY_CD----------------POD_COUNTRY_CD
Euro NC/UK to ASPA---------Starts with GB-------------Any country where Region/Trade = ASPA
Euro NC/UK to SPAC-------- Starts with GB-------------Any country where Region/Trade = SPAC
Euro NC/UK to US-----------Starts with GB-------------Starts with US
Euro NC/UK to CA-----------Starts with GB-------------Starts with CA
Euro NC/UK to AMLA-------- Starts with GB-------------Any country where Region/Trade = AMLA
Euro NC/UK to EMA ---------Starts with GB-------------Any country where Region/Trade = EMA
Here is the query again:
''Assign Tradelane to each BL_ID ('NCV_BL_DHL_TEMP' table) using
fields - POL_COUNTRY_CD ('NCV_BL_DHL_TEMP' table) and POD_COUNTRY_CD ('NCV_BL_DHL_TEMP' table) and
COUNTRY_CD ('DHL_TRADE_ASSIGNMENT' table). Also, any BL_ID where it is not possible to map/set a tradelane - set tradelane as 'OTHERS'.''
I hope this would be more helpful to you.
Thanks and Regards,
Paul
December 16, 2010 at 8:31 am
If you need more information, please let me know....
December 17, 2010 at 6:43 am
When you say "assign", are you selecting something, or updating a table?
Also, are the "trade lanes" stored somewhere, or are you constructing them from string snippets in the columns of these tables?
(That's what I mean by sample data for the tables. If I had some insert statements for some sample data for those tables, I could look at where you're starting, and where you want to end up, and be a bit more helpful. I can't see the start point, so finding the right route to the destination is tough.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 17, 2010 at 7:16 am
Hi GSquared,
Thanks again for your reply. Actually this project is divided into 2 parts, firstly an SSIS package would need to be created to populate the tables(which are new tables)-
DHL_TRADE_ASSIGNMENT
NCV_BL_DHL_TEMP
NCV_FREIGHT_DHL_TEMP
DHL_TEMP
However, the tables, NCV_BL_DHL_TEMP and NCV_FREIGHT_DHL_TEMP will be populated from the existing tables. The other two tables- DHL_TRADE_ASSIGNMENT and DHL_TEMP will be further populated from NCV_BL_DHL_TEMP and NCV_FREIGHT_DHL_TEMP tables.
the 2nd part would be to create an SSRS report which would basically pivot the DHL_TEMP table.
The query that I asked you is part of an SSIS package. The tradelanes would need to be assigned based on the tradelane definition sample data. This would mean for example (as per the sample data) if the value in POL_COUNTRY_CD column is GB and POD_COUNTRY_CD is ASPA, the value for tradelane should reflect as Euro NC/UK to ASPA(which would be I assume updating the table).
The tradelane concept is also new to the project and has to be created from scratch(constructing them from string snippets in the columns of these tables).
I currently do not have a sample data for the tables as they are all new tables. The project does not use any previously created tables. Sorry if I am able to give you only limited information. Based on this if possible could you please give me some idea or a starting point.
I can then at least try to build something up. Thanks again for your help.
Regards,
Paul
December 20, 2010 at 4:12 am
Hi GSquared,
Would you be able to help me now with some detailed information below:
I need to assign a value to a column called 'tradelane' for each unique BL_ID in the NCV_BL_DHL_TEMP
table. This column will be part of a newly created table called- DHL_TEMP and table structure is as follows:
CREATE TABLE [dbo].[DHL_TEMP]
(
[BL_ID] [decimal](10, 0) NOT NULL,
[DEPART_ACTUAL_DT] [datetime] NULL,
[TEU] [decimal](10, 0) NULL,
[FRT_USD] [decimal](12, 0) NULL,
[TRADELANE] [nvarchar](50) NULL,
)
The value is to be assigned based on using columns from 2 other tables-
1. NCV_BL_DHL_TEMP
CREATE TABLE [dbo].[NCV_BL_DHL_TEMP]
(
[BL_ID] [decimal](10, 0) NOT NULL,
[BL_NUM] [nvarchar](13) NULL,
[CP_GROUP_CD] [nvarchar](30) NULL,
[POL_COUNTRY_CD] [nvarchar](2) NULL,
[POD_COUNTRY_CD] [nvarchar](2) NULL,
[SAISAN_VESSEL_CD] [nvarchar](6) NULL,
[SAISAN_VOYAGE_CD] [nvarchar](6) NULL,
[SAISAN_LEG_CD] [nvarchar](1) NULL,
[DEPART_ACTUAL_DT] [datetime] NULL,
[TEU] [decimal](10, 0) NULL
)
2. DHL_TRADE_ASSIGNMENT-
CREATE TABLE [dbo].[DHL_TRADE_ASSIGNMENT]
(
[COUNTRY_CD] [nvarchar](2) NOT NULL,
[COUNTRY_DSC] [nvarchar](50) NULL,
[REGION_TRADE] [nvarchar](3) NULL,
[SUB_REGION] [nvarchar](50) NULL
)
The DHL_TRADE_ASSIGNMENT is a static table and will have limited rows. The sample data from the same is as under:
Country Code--Country------Region/Trade-----Sub Region
AO------------Angola-------EMA------------- AFRICA
BI------- -----Burundi-------EMA------------- AFRICA
BJ-------------Benin--------EMA------------- AFRICA
CG------------Congo--------EMA-------------AFRICA
GB------------Great Britain--EURONC----------EURO NC
The Tradelane definitions are based on this sample data-
TRADELANE-----------------POL_COUNTRY_CD----------------POD_COUNTRY_CD
Euro NC/UK to ASPA---------Starts with GB-------------Any country where Region/Trade = ASPA
Euro NC/UK to SPAC-------- Starts with GB-------------Any country where Region/Trade = SPAC
Euro NC/UK to US-----------Starts with GB-------------Starts with US
Euro NC/UK to CA-----------Starts with GB-------------Starts with CA
Euro NC/UK to AMLA-------- Starts with GB-------------Any country where Region/Trade = AMLA
Euro NC/UK to EMA ---------Starts with GB-------------Any country where Region/Trade = EMA
Sample data for POL_COUNTRY_CD and POD_COUNTRY_CD
POL_COUNTRY_CD----POD_COUNTRY_CD
NL--------------SG
JP--------------CN
TH--------------IT
JP--------------ID
TW-------------IL
This query is part of an SSIS package and I am stuck with this for a long time now. Any little help would be deeply appreciated as it would at least give me a start. Thanks a lot in advance.
Regards.
Paul
December 22, 2010 at 3:27 am
How POD_COUNTRY_CD (SG,CN,IT...) are related with The Tradelane definitions POD_COUNTRY_CD (Any country where Region/Trade = ASPA
,Any country where Region/Trade = SPAC...)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply