December 22, 2010 at 10:13 am
Hello,
Could anyone please help me find a solution to this-
What I am trying to do is to populate a newly created column called Tradelane based on the values contained in a spreadsheet (Tradelane) - Using table DHL_TRADE_ASSIGNMENT and POL_Country_CD / POD_Country_CD from table NCV_BL_DHL_TEMP and assign DHL Tradelane to each BL_ID.
This is to be achieved using columns contained in 2 tables- DHL_TRADE_ASSIGNMENT (Region_Trade and Sub_Region)and NCV_BL_DHL_TEMP (BL_ID, POL_COUNTRY_CD and POD_COUNTRY_CD ).
Below is the sample query I created which could be helpful:
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,
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' end
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)))
Please find attached the Tradeland.xls(spreadsheet), data structures for DHL_TRADE_ASSIGNMENT and NCV_BL_DHL_TEMP tables and sample data for both of them.
If you can help that would be great..I am stuck here for a long time now as this project is a combination of SSIS and SSRS.
Thanks and Regards,
Paul
December 22, 2010 at 11:27 am
Duplicate post. Please do not reply in this thread.
See original post here.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply