December 22, 2010 at 10:04 am
I have attached all the relevant documents which are being used for this query. Would that be enough ?
December 22, 2010 at 10:05 am
What format do you want me to convert the spreadsheets then ?
December 22, 2010 at 11:18 am
pwalter83 (12/22/2010)
What format do you want me to convert the spreadsheets then ?
CREATE TABLE <YourTableName> (Your Column list);
ALTER TABLE to show PK/FK/UQ constraints.
CREATE INDEX to show any indexes on the table.
INSERT INTO <YourTableName> sample data
repeat for all involved tables.
Test all of these yourself to ensure that they actually do run and set things up properly.
Then, for the sample data provided, show the expected results.
The easier it is for us (remember - we're unpaid volunteers helping out here!) to just copy/paste an environment that has everything that we need, the faster we can work on it and get something (that has been TESTED against your sample data) out to you.
Thanks for trying! I know you're getting frustrated by it all, but since we're not being paid, why should we jump through the hoops just to help you out? Meet us half way, with what is asked for, and you'll get a lot of people that will help out. (There are a lot of people that read nearly every single post, but they also refuse to help out if the sample data isn't readily consumable (cut-and-paste) into SSMS to be able to start working immediately. I wouldn't be surprised if once you post the data this way, if several others jump in with suggestions!)
Edit: Also, please use the code="sql" IFCode shortcut to wrap around your code. It's in the box to the left of the window that you type all of this stuff in.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 22, 2010 at 12:40 pm
pwalter83 (12/22/2010)
I have attached all the relevant documents which are being used for this query. Would that be enough ?
Here's a short SQL puzzle for you:
SELECT @answer = result FROM "Did you actually read the article/link we asked you to read?"
SELECT
CASE
WHEN @answer = "yes" THEN "What specifically is unclear so you can't follow it?"
WHEN @answer = "no" THEN "Is there any specific reason for you not to read that article/link?"
ELSE "Please make a decision."
END
December 22, 2010 at 2:54 pm
Paul,
it seems like you're not really interested in helping us help you.
Instead of providing the information we asked for multiple times you decide to open new threads with the very same incomplete information (for anyone who's interested: just check the post history...).
This issue you brought up about a week ago could have been resolved almost immediately with just a little effort on your side (read an article and react accordingly).
Before I start seeing some kind of parallelism to feeding a troll I'm going to leave this thread for someone else.
Side note: As a last resort you could always hire a consultant to fix the issue for you. That person would at least be able to see the table structure and some sample data in a ready to use format.
December 23, 2010 at 3:36 am
Hi Wayne,
Thanks a lot for your suggestions. The question I want to ask is-
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
Please find the the table structure for the 2 tables that I am using below along with the sample data code:
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('BR', 'Brazil', 'AMLA', 'ECSA');
insert into DHL_TRADE_ASSIGNMENT
values('TR', 'Turkey', 'EMA', 'EAST MED');
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('8235799', 'NTG100115', 'SHIPCO', 'ES', 'TR', 'YMUNS', '12W', 'W', '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');
-----------------------------------------------------------------------------------
This sample code would help you-
----------------------------------------------------------------------------------
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
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)))
------------------------------------------------------------------------------------
The tradelane would be defined based on the scenario as given in the attached. I hope this would really help anyone to find a solution to the problem I am facing. Any help would be a[ppreciated. Thanks a lot for your time.
Kind Regards,
Paul
December 23, 2010 at 4:38 am
LutzM (12/22/2010)
Before I start seeing some kind of parallelism to feeding a troll I'm going to leave this thread for someone else.
Now before you teach me some sql code posting etiquettes, could you learn some manners yourself and not write anything which is irrelevant. I am NOT begging you to give me a solution. You have the option to look away and mind your own business if you are not interested.
So do yourself some good, find some nice books and learn some etiquettes yourself.
December 23, 2010 at 4:40 am
That is much much better, but not quite complete
Now please provide the DDL for DHL_TEMP & a sample of what you would like to see in there, based on your input data.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 23, 2010 at 5:04 am
Hi Phil,
Thanks a lot for your reply. The DHL_TEMP table would include the value for the Tradelane per BL_ID. Please find the table structure for the DHL_TEMP table below:
-----------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DHL_TEMP](
[BL_ID] [decimal](10, 0) NOT NULL,
[DEPART_ACTUAL_DT] [datetime] NULL,
[TEU] [decimal](10, 0) NULL,
[TRADELANE] [nvarchar](50) 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]
-----------------------------------------------------------------------------------
The sample data should be something like:
BL_ID- 6525833
DEPART_ACTUAL_DT- 03-05-2010
TEU- 2
TRADELANE- ASPA to EURO NC
------------------------------------------------------------------------------------
Is this helpful ?
December 23, 2010 at 5:36 am
Hi paul
I think You need to create a lookup table to solve your issue
I have attached a excel sheet that contain the structure of your lookup table and the sample data for each possible condition that define the column tradelane and a text file that contains the query
Try it out
Hope this will help you.
Incase any query let me know.
Regards
Rashmi
December 23, 2010 at 7:05 am
Thanks again Rashmi....it really feels awkward to ask you everytime now as you must also be busy at your work as well. Actually I am not an expert at SQL and this new project that I am working on is different from what I have been doing in the past.
Even your last solution was correct but then I felt it would'nt work in case more data needs to be added in the table.
I will give your query a shot and get back to you in case I get stuck somewhere.
Thank you again for your timely help.
Warm Regards,
Paul
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply