ExpressMan1
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
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
INNER JOIN
FedExZones ON FedExIndex.FedExIndex = FedExZones.FromZone
INNER JOIN
FedExZones A ON FedExIndex.FedExIndex2 = FedExZones.ToZone
WHERE
SUBSTRING(@FedExIndex,1,3)
AND
SUBSTRING(@FedExIndex2,1,3
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
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
Column name correction.
Should be...
SELECT PostalPrefix, PostalPrefix2, FedExIndex, FedExZones.FedExGroundZone
INNER JOIN
FedExZones ON FedExIndex.FedExIndex = FedExZones.FromZone
INNER JOIN
FedExZones A ON FedExIndex.FedExIndex2 = FedExZones.ToZone
WHERE
SUBSTRING(@FedExIndex,1,3)
AND
SUBSTRING(@FedExIndex2,1,3
ASKER
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);
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?
ASKER
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.
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
UNION
SELECT PostalPrefixFrom, PostalPrefixTo, IndexZone FROM FedexIndex
WHERE
SUBSTRING(@PostalPrefix2,1
END
EXECUTE spGetZone4 @PostalPrefix1 = 'A1A', @PostalPrefix2 = 'B0B' // Returns the 2 FedExIndex that are needed.
Hi,
Is this what you need?
Hope that helps !
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 )
Hope that helps !
ASKER
@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
Field: IndexZone is not available in your Table: FedexIndex
can you clarify this? you're querying it in SP: spGetZone4
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.
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
ASKER
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
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
OR
(SUBSTRING(@PostalPrefix2,
END
Hi,
Pls try below-
Hope it helps !
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
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
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to both Pawan and Ryan. Just had to change FedexIndex to IndexZone in Pawan's code as I changed the field name.
ASKER
Works great. Many Thanks to both Pawan and Ryan.
and sample data would be good too.
some columns are not available in your Excel files provided above.