Link to home
Start Free TrialLog in
Avatar of ExpressMan1
ExpressMan1Flag for Canada

asked on

How can I use SQL Stored Procedure to return FedEx Zone

I am trying to get the FedEx Canadian Ground Zone using 2 tables, First table is the FedExIndex and second table is FedExZones.  2 attached excel tables just to show the table structure.  Using a ship from addresses PostalPrefix to get the ship from FedExIndex, and using a ship to address PostalPrefix to get the ship to FedExIndex.

Then matching both FedExindex, to FromZone and ToZone in table FedExZones, to get the FedExZone

CREATE PROCEDURE [dbo].[spGetZone4]

(
@FedExIndex NVARCHAR(9),
@FedExIndex2 NVARCHAR(9)
)

AS
BEGIN

SELECT StartPrefix, EndPrefix, FedExIndex, FedExZones.FedExGroundZone FROM FedExIndex
INNER JOIN
FedExZones ON FedExIndex.FedExIndex = FedExZones.FromZone
INNER JOIN
FedExZones A ON FedExIndex.FedExIndex2 = FedExZones.ToZone

WHERE
SUBSTRING(@FedExIndex,1,3) BETWEEN StartPrefix AND EndPrefix
AND
SUBSTRING(@FedExIndex2,1,3) BETWEEN StartPrefix AND EndPrefix

END


EXECUTE spGetZone4 @FedExIndex = A0A, @FedExIndex2 = A9Z   (this prefix is an exact match to field value)    //Returns too many rows.  Should return 1 record with FedExZone 6

EXECUTE spGetZone4 @FedExIndex = A0A, @FedExIndex2 = H9R    (this prefix is between table value)    //  Returns nothing.  Should return 1 record with FedExZone 4
FedExIndex.xlsx
FedExZones.xlsx
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Can you provide the scripts to create tables: FedExIndex and FedExZones ?
and sample data would be good too.

some columns are not available in your Excel files provided above.
Avatar of ExpressMan1

ASKER

I will work on script to create tables, but for now,
Column name correction.
Should be...


SELECT PostalPrefix, PostalPrefix2, FedExIndex, FedExZones.FedExGroundZone FROM FedExIndex
INNER JOIN
FedExZones ON FedExIndex.FedExIndex = FedExZones.FromZone
INNER JOIN
FedExZones A ON FedExIndex.FedExIndex2 = FedExZones.ToZone

WHERE
SUBSTRING(@FedExIndex,1,3) BETWEEN PostalPrefix AND PostalPrefix2
AND
SUBSTRING(@FedExIndex2,1,3) BETWEEN PostalPrefix AND PostalPrefix2
Here are the 2 tables.

CREATE TABLE FedexIndex(

ID INT IDENTITY (1,1),
PostalPrefixFrom VARCHAR (9),
PostalPrefixTo   VARCHAR (9),
FedExIndex       VARCHAR (2),
PRIMARY KEY (ID)
);

INSERT INTO FedexIndex(
PostalPrefixFrom, PostalPrefixTo, FedExIndex)
values ('A0A', 'A9Z', 'DV'), ( 'B0A', 'B0C', 'DY'), ('B0E', 'B0E', 'DX'), ('H0A', 'H9Z', 'DH');


CREATE TABLE FedexZones(
ID INT IDENTITY (1,1),
FedexIndexFrom  VARCHAR(2),
FedexIndexTo    VARCHAR (2),
FedexZone       INT
PRIMARY KEY (ID)
);

INSERT INTO FedexZones(
FedexIndexFrom, FedexIndexTo, FedexZone)
VALUES ('DV', 'DH', 14), ('DV', 'DX', 11), ('DV', 'DY', 12), ('DV', 'DV', 6);
From the data above What is the expected output?
Trying to return the FedExZone from table FedExZones.

Here is the first part of query I am working on which is probably the wrong way to go about it.  This returns the 2 values (FedExIndex) that need to match up with the FedExIndexFrom and FedExIndexTo in table FedEx Zones.

ALTER PROCEDURE [dbo].[spGetZone4]

(
@PostalPrefix1 NVARCHAR(9),
@PostalPrefix2 NVARCHAR(9)
)

AS
BEGIN

SELECT PostalPrefixFrom, PostalPrefixTo, IndexZone AS A FROM FedexIndex
WHERE
SUBSTRING(@PostalPrefix1,1,3) >= PostalPrefixFrom AND SUBSTRING(@PostalPrefix1,1,3) <= PostalPrefixTo
UNION
SELECT PostalPrefixFrom, PostalPrefixTo, IndexZone FROM FedexIndex
WHERE
SUBSTRING(@PostalPrefix2,1,3) >= PostalPrefixFrom AND SUBSTRING(@PostalPrefix2,1,3) <= PostalPrefixTo

END

EXECUTE spGetZone4 @PostalPrefix1 = 'A1A', @PostalPrefix2 = 'B0B'   // Returns the 2 FedExIndex that are needed.
Hi,
Is this what you need?

DECLARE @PostalPrefix1 AS VARCHAR(100) = 'A1A'
DECLARE @PostalPrefix2 AS VARCHAR(100) = 'B0B' 
SELECT PostalPrefixFrom, PostalPrefixTo AS A FROM FedexIndex 
WHERE 
( SUBSTRING(@PostalPrefix1,1,3) BETWEEN PostalPrefixFrom AND PostalPrefixTo ) 
OR 
(SUBSTRING(@PostalPrefix2,1,3) BETWEEN PostalPrefixFrom AND PostalPrefixTo  )

Open in new window


Hope that helps !
@PostalPrefix1 and PostalPrefix2 are Parameters because they represent the first 3 characters of a Canada postal code. This stored procedure will be called from a form where the Postal Codes  (To and From) are entered, so could be any postal code.
@ExpressMan1

From your comment in ID: 41932410

CREATE TABLE FedexIndex(

ID INT IDENTITY (1,1),
PostalPrefixFrom VARCHAR (9),
PostalPrefixTo   VARCHAR (9),
FedExIndex       VARCHAR (2),
PRIMARY KEY (ID)
);

Field: IndexZone is not available in your Table: FedexIndex

can you clarify this? you're querying it in SP: spGetZone4
Hi
See if this works for you.. Columns and their names you can adjust. I am using names what you have provided in the create command.

CREATE PROC GetZone
(
	 @PostalPrefix1 AS VARCHAR(100)
	,@PostalPrefix2 AS VARCHAR(100)
)
AS
BEGIN

	SELECT PostalPrefixFrom, PostalPrefixTo AS A FROM FedexIndex 
	WHERE 
	( SUBSTRING(@PostalPrefix1,1,3) BETWEEN PostalPrefixFrom AND PostalPrefixTo ) 
	OR 
	(SUBSTRING(@PostalPrefix2,1,3) BETWEEN PostalPrefixFrom AND PostalPrefixTo  )

END

Open in new window

I changed the field name FedExIndex to IndexZone  as I should not have field name same as table name.

EXECUTE spGetZone4 @PostalPrefix1 = 'A1A', @PostalPrefix2 = 'B0B'

Your code below, with IndexZone added returns the correct IndexZones, but how can I use the returned values (DV & DY for example, using EXEC above) to get the zone from table FedExZones ?

ALTER PROCEDURE spGetZone4
(
       @PostalPrefix1 AS VARCHAR(100)
      ,@PostalPrefix2 AS VARCHAR(100)
)
AS
BEGIN

      SELECT PostalPrefixFrom, PostalPrefixTo, IndexZone AS A FROM FedexIndex
      WHERE
      ( SUBSTRING(@PostalPrefix1,1,3) BETWEEN PostalPrefixFrom AND PostalPrefixTo )
      OR
      (SUBSTRING(@PostalPrefix2,1,3) BETWEEN PostalPrefixFrom AND PostalPrefixTo  )

END
Hi,
Pls try below-

ALTER PROCEDURE spGetZone4
(
       @PostalPrefix1 AS VARCHAR(100)
      ,@PostalPrefix2 AS VARCHAR(100)
)
AS
BEGIN

	SELECT DISTINCT f.FedexIndex , f.PostalPrefixFrom, f.PostalPrefixTo AS A FROM FedexIndex f
	INNER JOIN FedexZones z ON ( z.FedexIndexFrom = f.FedExIndex OR z.FedexIndexTo = f.FedExIndex )
	WHERE ( SUBSTRING(@PostalPrefix1,1,3) BETWEEN f.PostalPrefixFrom AND f.PostalPrefixTo ) 
	OR (SUBSTRING(@PostalPrefix2,1,3) BETWEEN f.PostalPrefixFrom AND f.PostalPrefixTo  )

END

Open in new window


Hope it helps !
is this what you need?

ALTER PROCEDURE [dbo].[spGetZone4]

(
@PostalPrefix1 NVARCHAR(9),
@PostalPrefix2 NVARCHAR(9)
)

AS
BEGIN

With cte
as
(
SELECT PostalPrefixFrom, PostalPrefixTo, IndexZone AS A 
FROM FedexIndex
WHERE
SUBSTRING(@PostalPrefix1,1,3) >= PostalPrefixFrom AND SUBSTRING(@PostalPrefix1,1,3) <= PostalPrefixTo
UNION
SELECT PostalPrefixFrom, PostalPrefixTo, IndexZone FROM FedexIndex
WHERE
SUBSTRING(@PostalPrefix2,1,3) >= PostalPrefixFrom AND SUBSTRING(@PostalPrefix2,1,3) <= PostalPrefixTo
)
Select a.*, b.FedexZone
from cte a inner join FedexZones b
on a.A >= b.FedexIndexFrom and a.A <= b.FedexIndexTo

END

Open in new window

Ryan,

This is close, but   EXECUTE spGetZone4 @PostalPrefix1 = 'A1A', @PostalPrefix2 = 'B0B'
results in 4 records.  Should be one record showing FedExZone = 12

'A1A'  is between A0A and A9Z in table FedexIndex so is = to DV      (the FedexIndex),  
and   'B0B'  is between B0A and B0C in table FedExIndex so is = to DY     (the FedexIndex)

So in table FedExZones where FedExIndexFrom = DV and FedExIndexTo = DY   FedExZone = 12
SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks to both Pawan and Ryan.    Just had to change FedexIndex to IndexZone in Pawan's code as I changed the field name.
Works great.    Many Thanks to both Pawan and Ryan.