SQL Query

Question

SQL Query

pwalter83

SSChampion

Points: 14575
More actions
December 16, 2010 at 7:49 am

#97541

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

pwalter83 SSChampion Points: 14575 More actions · Answer 2

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

pwalter83 SSChampion Points: 14575 More actions · Answer 3

If you need more information, please let me know....

GSquared SSC Guru Points: 260824 More actions · Answer 4

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

pwalter83 SSChampion Points: 14575 More actions · Answer 5

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

pwalter83 SSChampion Points: 14575 More actions · Answer 6

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

harinerella Mr or Mrs. 500 Points: 548 More actions · Answer 7

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...)