December 21, 2010 at 9:57 am
Hi Friends,
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
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
--------------------------------------------------------------------------------------------------
The solution to the above is as follows-
select distinct a.BL_ID,a.POL_COUNTRY_CD,a.POD_COUNTRY_CD,b.REGION_TRADE,
TRADELANE=
case ltrim(rtrim(substring(a.POL_COUNTRY_CD,1,2) ))
when 'GB' then case b.REGION_TRADE when 'SPAC' then 'Euro NC/UK to SPAC'
else case b.REGION_TRADE when 'ASPA' then 'Euro NC/UK to ASPA'
else case ltrim(rtrim(substring(a.POD_COUNTRY_CD,1,2) )) when 'US' then 'Euro NC/UK to US'
else case ltrim(rtrim(substring(a.POD_COUNTRY_CD,1,2) )) when 'CA' then 'Euro NC/UK to CA'
else case b.REGION_TRADE when 'AMLA' then 'Euro NC/UK to AMLA'
else case b.REGION_TRADE when 'EMA' then 'Euro NC/UK to EMA'
else 'Others' end end end end end end
when 'US' then case b.REGION_TRADE when 'ASPA' then 'US to ASPA'
else case ltrim(rtrim(substring(a.POD_COUNTRY_CD,1,2) )) when 'GB' then 'US to Euro NC/UK'
else case b.REGION_TRADE when 'EURO MED' then 'US to Euro MED'
else case b.REGION_TRADE when 'AMLA' then 'US to AMLA'
else case b.REGION_TRADE when 'EMA' then 'US to EMA'
else case b.REGION_TRADE when 'BLACK SEA' then 'US to BLACK SEA'
else 'Others' end end end end end end
when 'CA' then case ltrim(rtrim(substring(a.POD_COUNTRY_CD,1,2) )) when 'GB' then 'CA to Euro NC/UK'
else case b.REGION_TRADE when 'EURO MED' then 'CA to Euro MED'
else case b.REGION_TRADE when 'ASPA' then 'CA to ASPA'
else case b.REGION_TRADE when 'AMLA' then 'CA to AMLA'
else case b.REGION_TRADE when 'EMA' then 'CA to EMA'
else 'Others' end end end end end
end
from NCV_BL_DHL_TEMP a
INNER JOIN
DHL_TRADE_ASSIGNMENT b
on a.POD_COUNTRY_CD = ltrim(rtrim(substring(b.COUNTRY_CD,1,2)))
-------------------------------------------------------------------------------------------------
Now what I want to do is write a query for this part-
TRADELANE-----------POL_COUNTRY_CD------ --------------------POD_COUNTRY_CD
Intra Euro+MED---Any country where Region/Trade= EURO MED---Any country where region/Trade = EMA
I would completely understand if you seem disinterested now due to the length of this message. However, any help would be deeply appreciated.
Thanks and Regards,
Paul
December 21, 2010 at 10:15 am
Plz don't cross-post.
Plz post answers to this thread :
http://qa.sqlservercentral.com/Forums/Topic1037636-148-1.aspx#bm1037793
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply