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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Please help with SQL code

Author  Topic 

lahsiv2004
Starting Member

13 Posts
Posted - 2011-01-05 : 10:08:52
Hello Friends,

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 the query I have created below this message.

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

--where b.REGION_TRADE = 'AMLA' and c.REGION_TRADE = 'AMNO'
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site