October 15, 2009 at 1:27 pm
Hello,
I have a problem I can't seem to wrap my head around. I have a table that covers geographical locations and assigns products to those locations. I want to make a view that would show all products in this table that are assigned to certain geographical locations.
So for example if I look up a specific county, I could see what products were covered based on their zip.
So it would look like:
Zipcodecountynameproduct1product2
12345Dude County10
12346Duder County11
12347Dudes County11
The results I am getting ARE LIKE this
Zipcodecountynameproduct1product2
12345Dude County10
12346Duder County10
12346Duder County01
12347Dudes County10
12347Dudes County01
Here is the basic table structure and what I have tried so far. Any ideas?
DECLARE @Zips TABLE
(
zipcodeid INT,
Zipcode INT,
countyname VARCHAR (50),
countyid INT
)
DECLARE @Coverage TABLE
(
coverageid INT,
customerid INT,
zipcodeid INT,
productid INT
)
SELECT DISTINCT z.ZipCode,z.CountyName,
CASE WHEN productid = 1 THEN customerid end AS 'product1',
CASE WHEN productid = 2 THEN customerid end AS 'product2'
from @Coverage c
INNER JOIN @Zips z ON
October 17, 2009 at 9:09 am
You're almost there...
SELECT z.ZipCode, z.CountyName,
MAX(CASE WHEN productid = 1 THEN customerid end) AS 'product1',
MAX(CASE WHEN productid = 2 THEN customerid end) AS 'product2'
FROM @Coverage c
INNER JOIN @Zips z ON
GROUP BY z.ZipCode, z.CountyName
When you say SELECT DISTINCT... it returns the distinct combinations of all columns, including the case statements. Using GROUP BY with the MAX() aggregation functions, you get only 1 row for each zip/county combination and the highest customerid for that combination.
By the way, I'm not quite sure what customerID has to do with anything. I'm taking you at your word that it produces the results you want. Usually, I take counts with queries like this using this format for the case statements:
sum(CASE WHEN productid = 1 then 1 else 0 end) as product1,
sum(CASE WHEN productid = 2 then 1 else 0 end) as product2,
-- etc
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 19, 2009 at 8:40 am
This worked perfect. Thanks a lot. I was able to get the results I wanted by using a temp table but this is better.
The reason I am displaying the customerID is because I want to know which customer is assigned what area. We sell our products to only one customer per zip code. So I was trying to build a list of area codes and which of them were covered by which customer. It's a little more complicated than that but you get the idea (I hope).
Again thanks a lot.
October 20, 2009 at 11:37 am
Beware of combining zip codes and county codes the boundaries are different. 19010 is Bryn Mawr Pa. It is located in Delaware (FIPS 42045) and Montgomery (FIPS 42091) counties.
October 20, 2009 at 11:40 am
Yes, I know 😀
GROUP BY z.ZipCode, z.CountyName
Zip codes are just convenient distribution areas for the federal postal service. Counties are governmental boundaries defined by the states. Zip codes are volatile, county boundaries are fixed. I believe zip codes can even extend across state lines.
However, when grouping properties together, we often select properties by both zip and county so that we are getting properties with similar attributes. If nothing else, the properties in another county may be subject to different local laws and different taxes.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply