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
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 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.
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)
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!
