SQLTeam.com | Weblogs | Forums

Display zipcodes into Zones from package ID

Hello!
I want to display different zipcodes into zones for packages from orders.

Lets give an example
Order 1 with 1 package with zipcode 11111 = Display Zone 1
Order 2 with 2 packages Zipcode 22222 = Display Zone 2

I already have an sql that displays the zipcodes for for the specific package. But dont really know how to also give a specific zone for the different zip codes

kindly post the table structure with sample data.

Table1 Package
Package ID Customer Number
123456 54874565
Table 2 CustomerID
Customer ID ShortID
54874565 17676
Table 3 Adress
AdressID Zipcode
17676 88888

you can create a seperate table called Zip_Zone

now do the SQL joins to get the output as you want
if you need help with this please let me know

image

if you would like to help with the joins that would be great...im a noob

While others may be busy, let me give it a try.

Your existing sql may look like this:

Select ZipCode from Address join Package on Address.AddressID = Package.AddressID
where packageID = 123456

Noticed, your example data did not include the relationship between address and package tables.
I am guessing your package table has a field called addressID. If that is not the case, for instance, the relationship is through customerID table, then my above sql has to be modified.

With the additional table harishgg1 has created, the new sql will look like this:

Select Address.ZipCode, Zip_Zone.Zone from Address join Package on Address.AddressID = Package.AddressID
join Zip_Zone on Zip_Zone.ZipCode = Address.ZipCode
where packageID = 123456

You will get:

88888, Zone 8

create sample data script

create table #Package (Package_ID int, Customer_Number int )
insert into #Package select 123456, 54874565
insert into #Package select 33333 , 45678
insert into #Package select 212121, 123123123

create table #CustomerID (Customer_ID int , ShortID int )
insert into #CustomerID select 54874565, 17676
insert into #CustomerID select 45678, 32324
insert into #CustomerID select 123123123, 888899

create table #Address (AddressID int, Zipcode int)
insert into #Address select 17676 , 88888
insert into #Address select 32324 , 88888
insert into #Address select 888899 , 555555

create table #Zip_zone (Zipcode int, Zone varchar(20))
insert into #Zip_zone select 88888,'Display Zone 8'
insert into #Zip_zone select 555555,'Display Zone 5'

select * from #Package
select * from #CustomerID
select * from #Address
select * from #Zip_zone

; with cte as 
(
select    Zipcode,count(*) as cnt from    #Address group by    zipcode
)
select 
    distinct 'ZipCode '+cast(a.zipcode as varchar)+' has '+ cast( a.cnt  as varchar)+ ' packages ' + ' = ' + g.Zone
from 
   cte a join #Address b    on a.Zipcode= b.Zipcode 
         join #CustomerID c on a.Zipcode = b.Zipcode
         join #CustomerID d on d.ShortID = b.AddressID 
		 join #Package f    on f.Customer_Number = d.Customer_ID
         join #Zip_zone g   on b.Zipcode = g.Zipcode

Thanks for the quick answers! Still trying to figure out how to put all this together.

So this sql is a part of different sql's that we use in a software that put together information that are being printed on labels for different packages from orders. When we prreview the labels in the software we just put in the specific package ID and then it fetches all the sql and shows the info on the label.

This is the sql thats already written by someone else.

  1. This Sql counts the packages on specific orders. O04T2 = table for all the packages
    SELECT COUNT(0) antalKollin FROM O40T2
    WHERE shortr08 IN
    (SELECT shortr08
    FROM O40T2
    WHERE ocarrno = @:ocarrno)

  2. Then there's also this sql, the last line on this sql also contains (where O40T2.ocarrno = @:ocarrno) thats included in the sql above. So I guess they have some kind of connection

select O40T2., R08T1., R04T1., O04T1.custno o04_custno, O04T1., SHIPTO., ASTRO_VIEW_TBL_OEPARTY.
from O40T2

left join R08T1 on R08T1.shortr08 = O40T2.shortr08

left join R04T1 on R04T1.routeno = R08T1.routeno

left join O06T1 on O06T1.shorto06 = O40T2.shorto06

left join O04T1 on O04T1.shorto04 = O06T1.shorto04

left join (select d13obj td13obj, sysshort tsysshort, roletype troletype,

  d10name1 tname1, d50adrs1 taddressline1, d50adrs2 taddressline2, d50adrs3 taddressline3, d50zipc tpostal, d50city tcity, d50cntry tcountry

  from D13T1

  join D10T1 on D10T1.shortd10=D13T1.shortd10

  join D50T1 on D50T1.adrid= D10T1.adrid )  SHIPTO

on SHIPTO.tsysshort=O40T2.ocarrno and SHIPTO.td13obj=170 and SHIPTO.troletype=5
left join ASTRO_VIEW_TBL_OEPARTY ON ASTRO_VIEW_TBL_OEPARTY.d13obj = 20 and ASTRO_VIEW_TBL_OEPARTY.sysshort = O06T1.shorto04

where O40T2.ocarrno = @:ocarrno

the last sql shows all the values including zipcodes

would it be possible in some way to add something like

CASE WHEN d50zipc = 11733 THEN 'zon 2'
END AS Zone

FROM D50T1

yes its possible to add case statements

any suggestions how to add this to the main sql? Only gets error

select O40T2., R08T1., R04T1., O04T1.custno o04_custno, O04T1., SHIPTO., ASTRO_VIEW_TBL_OEPARTY.
from O40T2

left join R08T1 on R08T1.shortr08 = O40T2.shortr08

left join R04T1 on R04T1.routeno = R08T1.routeno

left join O06T1 on O06T1.shorto06 = O40T2.shorto06

left join O04T1 on O04T1.shorto04 = O06T1.shorto04

left join (select d13obj td13obj, sysshort tsysshort, roletype troletype,

  d10name1 tname1, d50adrs1 taddressline1, d50adrs2 taddressline2, d50adrs3 taddressline3, d50zipc tpostal, d50city tcity, d50cntry tcountry

  from D13T1

  join D10T1 on D10T1.shortd10=D13T1.shortd10

  join D50T1 on D50T1.adrid= D10T1.adrid )  SHIPTO

on SHIPTO.tsysshort=O40T2.ocarrno and SHIPTO.td13obj=170 and SHIPTO.troletype=5
left join ASTRO_VIEW_TBL_OEPARTY ON ASTRO_VIEW_TBL_OEPARTY.d13obj = 20 and ASTRO_VIEW_TBL_OEPARTY.sysshort = O06T1.shorto04

where O40T2.ocarrno = @:ocarrno

SELECT
adrid,
d50zipc as ZIPCODE,
CASE
WHEN d50zipc = 11858 THEN 'Zon 1'
WHEN d50zipc = 11734 THEN 'Zon 2'

END Zon

FROM
D50T1
D50T1

ORDER BY Zon DESC;

This seperate sql (sql1) counts the amount of packages on orders. And is somehow connected to the main sql (sql0). SO maybe do something similar with zipcodes?


problem solved!