December 10, 2002 at 11:50 am
Table Name = Available Loads
Columns = Company# (PK), Agency Code, Load Number (PK), Origin City, Origin State, Dest City, Dest State
From the table above I need to find an Agency that has multiple loads, coming from the same Orgin City and State but going to different Destinations. Below is a sample of the data that I should show up in the result set:
Company# Agency CodeLoad NumberOrigin CityOrigin StateDest CityDest State
LDWY XYZ11010 ONEILL NECLEVELAND OH
LDWY XYZ11011 ONEILL NEHICKORY NC
LDWY XYZ11012 ONEILL NELEWISVILLE TX
This is an example of what should NOT show up in the result set since the Dest City and State are the same:
Company# Agency CodeLoad NumberOrigin CityOrigin StateDest CityDest State
LDWY XYZ11010 ONEILL NECLEVELAND OH
LDWY XYZ11011 ONEILL NECLEVELANDOH
LDWY XYZ11012 ONEILL NECLEVELANDOH
I've tried a variety of queries to no avail. If anyone could help me out, I'd greatly appreciate it.
Thank you,
Darias
December 10, 2002 at 12:41 pm
Possibly something like this might work for you.
create table LoadInfo(
Company# char(4),
AgencyCode char(3),
LoadNumber int,
OriginCity varchar(25),
OriginState char(2),
DestCity varchar(25),
DestState char(2))
insert into LoadInfo values ('LDWY', 'XYZ', 11010, 'ONEILL', 'NE', 'CLEVELAND', 'OH')
insert into LoadInfo values ('LDWY', 'XYZ', 11011, 'ONEILL', 'NE', 'HICKORY', 'NC')
insert into LoadInfo values ('LDWY', 'XYZ', 11012, 'ONEILL', 'NE', 'LEWISVILLE', 'TX')
insert into LoadInfo values ('LDWY', 'ABC', 11010, 'ONEILL', 'NE', 'CLEVELAND', 'OH')
insert into LoadInfo values ('LDWY', 'ABC', 11011, 'ONEILL', 'NE', 'CLEVELAND','OH')
insert into LoadInfo values ('LDWY', 'ABC', 11012, 'ONEILL', 'NE', 'CLEVELAND', 'OH')
select * from LoadInfo a
where AgencyCode in(select AgencyCode from LoadInfo where a.AgencyCode=AgencyCode and a.OriginCity=OriginCity and a.OriginState=OriginState and (a.DestCity <> DestCity or a.DestState <> DestState))
drop table LoadInfo
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 10, 2002 at 3:13 pm
Based on the example Greg put together also try this and compare the execution plans and runtimes to see if there is a difference. Had no idea what you indexes are like other than PK index.
select a.*
from loadinfo a
left join
(SELECT agencycode, DestCity, DestState from loadinfo
group by AgencyCode, DestCity, DestState
having count(OriginCity) > 1 and Count(OriginState) > 1) b
on a.AgencyCode = b.AgencyCode
WHERE b.DestCity IS NULL AND b.DestState IS NULL
December 10, 2002 at 3:46 pm
Don't think Antares686 example works if a giving load, picked up in a single origin city, has multiple destinations in different cities and states, where at least one destination is the same city and state. Below you will find an example. I agree that performance might suffer with my example due to the a sub query running for each loadinfo record. Possible there might be a easy way to fix Antares686 example. Also maybe you will never have a single load having what I described above.
Here is an example:
create table LoadInfo(
Company# char(4),
AgencyCode char(3),
LoadNumber int,
OriginCity varchar(25),
OriginState char(2),
DestCity varchar(25),
DestState char(2))
insert into LoadInfo values ('LDWY', 'XYZ', 11010, 'ONEILL', 'NE', 'CLEVELAND', 'OH')
insert into LoadInfo values ('LDWY', 'XYZ', 11011, 'ONEILL', 'NE', 'HICKORY', 'NC')
insert into LoadInfo values ('LDWY', 'XYZ', 11012, 'ONEILL', 'NE', 'LEWISVILLE', 'TX')
insert into LoadInfo values ('LDWY', 'ABC', 11010, 'ONEILL', 'NE', 'CLEVELAND', 'OH')
insert into LoadInfo values ('LDWY', 'ABC', 11011, 'ONEILL', 'NE', 'TROY','OH')
insert into LoadInfo values ('LDWY', 'ABC', 11012, 'ONEILL', 'NE', 'CLEVELAND', 'OH')
insert into LoadInfo values ('LDWY', 'LMK', 11010, 'ONEILL', 'NE', 'CLEVELAND', 'OH')
insert into LoadInfo values ('LDWY', 'LMK', 11011, 'ONEILL', 'NE', 'CLEVELAND','OH')
insert into LoadInfo values ('LDWY', 'LMK', 11012, 'ONEILL', 'NE', 'CLEVELAND', 'OH')
select a.*
from loadinfo a
left join
(SELECT agencycode, DestCity, DestState from loadinfo
group by AgencyCode, DestCity, DestState
having count(OriginCity) > 1 and Count(OriginState) > 1) b
on a.AgencyCode = b.AgencyCode
WHERE b.DestCity IS NULL AND b.DestState IS NULL
drop table loadinfo
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 10, 2002 at 4:05 pm
Try this hack:
select a.*
from loadinfo a
left join (
select distinct b.agencycode from
(SELECT count(*) as count,agencycode, OriginCity+DestCity x, OriginState+DestState y from loadinfo
group by AgencyCode, OriginCity+DestCity, OriginState+DestState ) b
join (select count(*) as count , agencycode from LoadInfo group by agencycode) c
on b.count = c.count)e
on a.AgencyCode =e.AgencyCode
WHERE e.agencycode IS NULL
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 10, 2002 at 4:36 pm
Ahh, I see my error. Try this instead.
select a.*
from loadinfo a
inner join
(SELECT agencycode from loadinfo
group by AgencyCode
having count(DISTINCT DestState) + count(DISTINCT DestCity) > 2) b
on a.AgencyCode = b.AgencyCode
December 11, 2002 at 7:31 am
I like this one! Much better than my hack.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 11, 2002 at 8:15 am
Thank you and thanks for pointing out my error on the first one. Helps to have more than one set of eyes on a task always.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply