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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Query

Author  Topic 

lahsiv2004
Starting Member

13 Posts
Posted - 2010-12-17 : 05:42:15
Hello Friends,

Could you please help me create a SQL query for one of the steps in the functional specs I have for my project:

The scenario is - using fields from 2 tables I have to create a query which goes as :

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

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-----Where Region/Trade = ASPA
Euro NC/UK to SPAC------ Starts with GB-----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-----Where Region/Trade = AMLA
Euro NC/UK to EMA -------Starts with GB-----Where Region/Trade = EMA

I hope this infiormation would help.

Thanks and Regards,

Paul

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-12-20 : 06:47:33
What have you tried so far?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

lahsiv2004
Starting Member

13 Posts
Posted - 2011-01-05 : 10:23:37
Well, till now I have accomplished this-


-------------------------------------------------------------------------
Select distinct a.BL_ID,
a.POL_COUNTRY_CD,
a.POD_COUNTRY_CD,
b.region_trade as POL_REGIONTRADE,
c.region_trade as POD_REGIONTRADE,
b.Sub_Region as POL_SUBREGION,
c.Sub_Region as POD_SUBREGION,
a.teu, a.saisan_month,
cast(Case When Left(A.POL_COUNTRY_CD,2) ='GB' and c.REGION_TRADE ='ASPA' THEN 'Euro NC/UK to ASPA'
When Left(A.POL_COUNTRY_CD,2) ='GB' and c.REGION_TRADE ='SPAC' THEN 'Euro NC/UK to SPAC'
When Left(A.POL_COUNTRY_CD,2) ='GB' and Left(A.POD_COUNTRY_CD,2)='US' THEN 'Euro NC/UK to US'
When Left(A.POL_COUNTRY_CD,2) ='GB' and Left(A.POD_COUNTRY_CD,2)='CA' THEN 'Euro NC/UK to CA'
When Left(A.POL_COUNTRY_CD,2) ='GB' and c.REGION_TRADE ='AMLA' THEN 'Euro NC/UK to AMLA'
When Left(A.POL_COUNTRY_CD,2) ='GB' and c.REGION_TRADE ='EMA' THEN 'Euro NC/UK to EMA'
When b.Sub_Region = 'BALTICS' and c.REGION_TRADE ='EURCO NC' THEN 'Intra Euro+MED'
When b.Sub_Region = 'EAST MED' and c.REGION_TRADE ='EMA' THEN 'Intra Euro+MED'
When b.REGION_TRADE = 'EURO MED' and c.REGION_TRADE = 'EURO MED' THEN 'Intra Euro+MED'
When b.REGION_TRADE = 'EURO NC' and c.REGION_TRADE = 'EURO NC' THEN 'Intra Euro+MED'
When b.Sub_Region = 'EURO SCAN' and c.REGION_TRADE ='EURO NC' THEN 'Intra Euro+MED'
When b.Sub_Region = 'BLACK SEA' and c.Sub_Region = 'BLACK SEA' THEN 'Intra Euro+MED'
When b.REGION_TRADE = 'EURO NC' and c.Sub_Region = 'OTHERS EURO' THEN 'Intra Euro+MED'
When b.REGION_TRADE = 'EURO MED' and c.REGION_TRADE = 'ASPA' THEN 'Euro MED to ASPA'
When b.REGION_TRADE = 'EURO MED' and c.REGION_TRADE = 'SPAC' THEN 'Euro MED to SPAC'
When b.REGION_TRADE = 'EURO MED' and Left(A.POD_COUNTRY_CD,2)='US' THEN 'Euro MED to US'
When b.REGION_TRADE = 'EURO MED' and Left(A.POD_COUNTRY_CD,2)='CA' THEN 'Euro MED to CA'
When b.REGION_TRADE = 'EURO MED' and c.REGION_TRADE = 'AMLA' THEN 'Euro MED to AMLA'
When b.REGION_TRADE = 'EURO MED' and c.REGION_TRADE = 'EMA' THEN 'Euro MED to EMA'
When b.REGION_TRADE = 'ASPA' and Left(A.POD_COUNTRY_CD,2)= 'GB' THEN 'ASPA to Euro NC/UK'
When b.REGION_TRADE = 'ASPA' and c.REGION_TRADE = 'Euro MED' THEN 'ASPA to Euro MED'
When b.REGION_TRADE = 'ASPA' and c.REGION_TRADE = 'ASPA' THEN 'ASPA to ASPA'
When b.REGION_TRADE = 'ASPA' and c.REGION_TRADE = 'SPAC' THEN 'ASPA to SPAC'
When b.REGION_TRADE = 'ASPA' and Left(A.POD_COUNTRY_CD,2)= 'US' THEN 'ASPA to US'
When b.REGION_TRADE = 'ASPA' and Left(A.POD_COUNTRY_CD,2)= 'CA' THEN 'ASPA to CA'
When b.REGION_TRADE = 'ASPA' and c.REGION_TRADE = 'AMLA' THEN 'ASPA to AMLA'
When b.REGION_TRADE = 'ASPA' and c.Sub_Region = 'MIDDLE EAST' THEN 'ASPA to MIDDLE EAST'
When b.REGION_TRADE = 'ASPA' and c.Sub_Region = 'EAST MED' THEN 'ASPA to EAST MED'
When b.REGION_TRADE = 'ASPA' and c.Sub_Region = 'AFRICA' THEN 'ASPA to AFRICA'
When b.REGION_TRADE = 'ASPA' and c.Sub_Region = 'BLACK SEA' THEN 'ASPA to BLACK SEA'
When b.REGION_TRADE = 'SPAC' and Left(A.POD_COUNTRY_CD,2)= 'GB' THEN 'SPAC to Euro NC/UK'
When b.REGION_TRADE = 'SPAC' and Left(A.POD_COUNTRY_CD,2)= 'US' THEN 'SPAC to US'
When b.REGION_TRADE = 'SPAC' and Left(A.POD_COUNTRY_CD,2)= 'CA' THEN 'SPAC to CA'
When b.REGION_TRADE = 'SPAC' and c.REGION_TRADE = 'EURO MED' THEN 'SPAC to EURO MED'
When b.REGION_TRADE = 'SPAC' and c.REGION_TRADE = 'ASPA' THEN 'SPAC to ASPA'
When b.REGION_TRADE = 'SPAC' and c.REGION_TRADE = 'EMA' THEN 'SPAC to EMA'
When Left(A.POL_COUNTRY_CD,2) ='US' and Left(A.POD_COUNTRY_CD,2)='GB' THEN 'US to Euro NC/UK'
When Left(A.POL_COUNTRY_CD,2) ='US' and c.REGION_TRADE = 'EURO MED' THEN 'US to EURO MED'
When Left(A.POL_COUNTRY_CD,2) ='US' and c.REGION_TRADE = 'ASPA' THEN 'US to ASPA'
When Left(A.POL_COUNTRY_CD,2) ='US' and c.REGION_TRADE = 'SPAC' THEN 'US to SPAC'
When Left(A.POL_COUNTRY_CD,2) ='US' and c.REGION_TRADE = 'AMLA' THEN 'US to AMLA'
When Left(A.POL_COUNTRY_CD,2) ='US' and c.REGION_TRADE = 'EMA' THEN 'US to EMA'
When Left(A.POL_COUNTRY_CD,2) ='US' and c.Sub_Region = 'BLACK SEA' THEN 'US to BLACK SEA'
When Left(A.POL_COUNTRY_CD,2) ='CA' and c.REGION_TRADE = 'EURO MED' THEN 'CA to Euro MED'
When Left(A.POL_COUNTRY_CD,2) ='CA' and c.REGION_TRADE = 'ASPA' THEN 'CA to ASPA'
When Left(A.POL_COUNTRY_CD,2) ='CA' and c.REGION_TRADE = 'AMLA' THEN 'CA to AMLA'
When Left(A.POL_COUNTRY_CD,2) ='CA' and c.REGION_TRADE = 'EMA' THEN 'CA to EMA'
When Left(A.POL_COUNTRY_CD,2) ='CA' and Left(A.POD_COUNTRY_CD,2)= 'GB' THEN 'CA to Euro NC/UK'
When b.REGION_TRADE = 'AMLA' and c.REGION_TRADE = 'EURO MED' THEN 'AMLA to EURO MED'
When b.REGION_TRADE = 'AMLA' and c.REGION_TRADE = 'ASPA' THEN 'AMLA to ASPA'
When b.REGION_TRADE = 'AMLA' and c.REGION_TRADE = 'SPAC' THEN 'AMLA to SPAC'
When b.REGION_TRADE = 'AMLA' and c.REGION_TRADE = 'AMNO' THEN 'AMLA to AMNO'
When b.REGION_TRADE = 'AMLA' and c.REGION_TRADE = 'AMLA' THEN 'AMLA to AMLA'
When b.REGION_TRADE = 'AMLA' and c.REGION_TRADE = 'EMA' THEN 'AMLA to EMA'
When b.REGION_TRADE = 'AMLA' and Left(A.POD_COUNTRY_CD,2)= 'GB' THEN 'AMLA to Euro NC/UK'
When b.REGION_TRADE = 'EMA' and c.REGION_TRADE = 'EURO MED' THEN 'EMA to EURO MED'
When b.REGION_TRADE = 'EMA' and c.REGION_TRADE = 'ASPA' THEN 'EMA to ASPA'
When b.REGION_TRADE = 'EMA' and c.REGION_TRADE = 'SPAC' THEN 'EMA to SPAC'
When b.REGION_TRADE = 'EMA' and c.REGION_TRADE = 'AMNO' THEN 'EMA to AMNO'
When b.REGION_TRADE = 'EMA' and c.REGION_TRADE = 'AMLA' THEN 'EMA to AMLA'
When b.REGION_TRADE = 'EMA' and c.REGION_TRADE = 'EMA' THEN 'EMA to EMA'
When b.REGION_TRADE = 'EMA' and Left(A.POD_COUNTRY_CD,2)= 'GB' THEN 'EMA to Euro NC/UK'
When b.Sub_Region = 'BLACK SEA' and Left(A.POD_COUNTRY_CD,2)= 'US' THEN 'Black Sea to US'

else 'OTHERS'
END as nvarchar) as TRADELANE
from NCV_BL_DHL_TEMP a
INNER JOIN
DHL_TRADE_ASSIGNMENT b

on a.POL_COUNTRY_CD = ltrim(rtrim(substring(b.COUNTRY_CD,1,2)))
INNER JOIN DHL_TRADE_ASSIGNMENT c
on a.POD_COUNTRY_CD = ltrim(rtrim(substring(c.COUNTRY_CD,1,2)))

/*
join Minigapp..MG_Location mgl
on (mgl.continent_cd = 'EUR' and b.country_cd = 'FRN')
or (mgl.continent_cd = 'MED' and b.country_cd = 'FRS')

*/

--where pol_country_cd <>'FR'

----------------------------------------------------------------------

However, now I am stuck with one more functionality I need to include in the above code which goes like this:


I am trying to modify a query based on changes in the functional specs. Would it be possible for you to help me with this ?

In a table I have a list of countries and each country has been assigned a two-character code and each code is unique. Only in the case of France(FR), there are two versions- FRN and FRS.

As the resultset is displayed based on only the first two characters, I am getting duplicate data for the same

What I wish to accomplish is only display data for either FRN or FRS. If the country code begins with FR(France) in the POL_COUNTRY_CD or POD_COUNTRY_CD column in NCV_BL_DHL_TEMP table then join to table, MG_LOCATION to identify continent. If continent is equal to 'EUR' then lookup 'FRN' in a table called DHL_TRADE_ASSIGNMENT. If continent is equal to 'MED' then lookup 'FRS'.

I have already created a query and need to fit in the above scenario in the same. Please find attached the query.

Please find the the table structure for the 3 tables that I am using below -

First one is the DHL_TRADE_ASSIGNMENT table-

-----------------------------------------------------------------
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DHL_TRADE_ASSIGNMENT](
[COUNTRY_CD] [nvarchar](20) NOT NULL,
[COUNTRY_DSC] [nvarchar](50) NULL,
[REGION_TRADE] [nvarchar](50) NULL,
[SUB_REGION] [nvarchar](50) NULL,
UNIQUE NONCLUSTERED
(
[COUNTRY_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

-----------------------------------------------------------------------
Sample data for DHL_TRADE_ASSIGNMENT-
-----------------------------------------------------------------------
insert into DHL_TRADE_ASSIGNMENT
values('FRS', 'France South', 'EURO MED', 'EURO MED');
insert into DHL_TRADE_ASSIGNMENT
values('FRN', 'France North', 'EURO NC', 'EURO NC');
insert into DHL_TRADE_ASSIGNMENT
values('ES', 'SPAIN', 'EURO MED', 'EURO MED');
insert into DHL_TRADE_ASSIGNMENT
values('AT', 'AUSTRIA', 'EURO NC', 'EURO NC');
insert into DHL_TRADE_ASSIGNMENT
values('AU', 'Australia', 'SPAC', 'SPAC');
insert into DHL_TRADE_ASSIGNMENT
values('IN', 'India', 'ASPA', 'ASPA');
insert into DHL_TRADE_ASSIGNMENT
values('RO', 'Romania', 'BLACK SEA', 'BLACK SEA');
insert into DHL_TRADE_ASSIGNMENT
values('CA', 'Canada', 'AMNO', 'AMNO');
insert into DHL_TRADE_ASSIGNMENT
values('AG', 'Antigua', 'AMLA', 'NCSA');
insert into DHL_TRADE_ASSIGNMENT
values('IS', 'Iceland', 'EURO NC', 'OTHERS EURO');
insert into DHL_TRADE_ASSIGNMENT
values('EC', 'EC', 'AMLA', 'WCSA');
insert into DHL_TRADE_ASSIGNMENT
values('CN', 'China', 'ASPA', 'ASPA');
insert into DHL_TRADE_ASSIGNMENT
values('GB', 'Great Britain', 'EURO NC', 'EURO NC');

---------------------------------------------------------------------
2nd table- NCV_BL_DHL_TEMP
-----------------------------------------------------------------------

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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,
PRIMARY KEY CLUSTERED
(
[BL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------------------------------------------------------------
Sample data for NCV_BL_DHL_TEMP-
-----------------------------------------------------------------------------------------

insert into NCV_BL_DHL_TEMP
values('6525833', 'MAA012330', 'SAIMA', 'IN', 'GB', 'HCMB', '0083W', 'W', '03-05-2010', '2');
insert into NCV_BL_DHL_TEMP
values('7102907', 'SH8634919', 'AL_FILTER', 'GB', 'IN', 'XYKOU', '0058W', 'W', '06-05-2010', '2');
insert into NCV_BL_DHL_TEMP
values('6117626', 'GLA007219', 'HANKYU', 'AU', 'AT', 'HANB', '01E', 'E', '02-02-2010', '1');
insert into NCV_BL_DHL_TEMP
values('5772271', 'BLR009972', 'TTP_TECHNOLOGIE', 'AT', 'AU', 'HJCH', '0036W', 'W', '04-05-2010', '1');
insert into NCV_BL_DHL_TEMP
values('6106668', 'LEH901114', 'SAEME', 'FR', 'HK', 'SVB', '648E', 'E', '02-06-2009', '2');
insert into NCV_BL_DHL_TEMP
values('6137996', 'FXT128719', 'FELIANCE_FIBRES', 'TR', 'ES', 'CNTSNG', '258E', 'E', '01-09-2010', '4');
insert into NCV_BL_DHL_TEMP
values('6168340', 'ANR490955', 'DHL_DANZAS', 'BR', 'CA', 'CSGNZU', '006E', 'E', '02-08-2010', '2');
insert into NCV_BL_DHL_TEMP
values('6168346', 'ANR440555', 'DHL_DANZAS', 'CA', 'BR', 'CSGWZU', '006E', 'E', '02-01-2008', '2');
insert into NCV_BL_DHL_TEMP
values('6581123', 'HK1016941', 'OKI', 'CN', 'GB', 'YPULT', '03W', 'W', '02-02-2007', '2');
insert into NCV_BL_DHL_TEMP
values('6581127', 'HK5010911', 'KOK', 'AG', 'IS', 'YMULT', '03W', 'W', '04-06-2008', '2');
insert into NCV_BL_DHL_TEMP
values('6581123', 'HD1086941', 'KPP', 'IS', 'AG', 'YKULT', '03W', 'W', '02-05-2009', '2');

-----------------------------------------------------
3rd table- MG_LOCATION
-----------------------------------------------------


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MG_LOCATION](
[LOCATION_CD] [varchar](5) NOT NULL,
[LOCATION_DSC] [varchar](35) NULL,
[STATE_CD] [varchar](3) NULL,
[COUNTRY_CD] [varchar](2) NOT NULL,
[CONTINENT_CD] [varchar](3) NULL,

CONSTRAINT [PK_MG_LOCATION] PRIMARY KEY CLUSTERED
(
[LOCATION_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

--------------------------------------------------------------------
Sample data for MG_LOCATION
-----------------------------------------

insert into MG_LOCATION
values('FRTAR', 'TARNOS', 'B', 'FR', 'MED');
insert into MG_LOCATION
values('FRTCC', 'TOCANE-SAINT-APRE', 'B', 'FR', 'EUR');
insert into MG_LOCATION
values('FRTCN', 'TUCHAN', 'C', 'FR', 'MED');
insert into MG_LOCATION
values('GBCNE', 'CALNE', 'D', 'GB', 'EUR');
insert into MG_LOCATION
values('IT2QW', 'CASTEL CONDINO', 'E', 'IT', 'EUR');
------------------------------------------------------------

I hope the above information would help. Any help or starting point would be deeply appreciated.

Thanks a lot in advance !!!

Regards,

Paul
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site