SQL

[subs]

ocity ost ozip dcity dstate dzip consignee shipdate
a b c d e f cat 1/1/2008
h f g d e f cat 1/2/2008
p k l o z t cat 1/2/2008
l st mn d e f cat1 1/2/2008
otp ltp mtp dsc sto ppp cat2 2/3/2008
olm tma ots otaa iiiip ddddds sss 2/3/2008
ls st1 mn2 d e f cat1 1/2/2008

hi

i want to write a SQL query which can extract records which have same
dcity, dstate, dzip, shipdate and consignee but have different ocity,
ostate and ozip. In the above example, the first, second , fourth and
last record need to be extracted. The final result has to be sorted
such that you could see the pattern clearly. Can you please help with
SQL query

 

[Dale Fye]

Could you have the same consignee with different dCity,dState, and dZip
values. If not, I wouldn't store all of that information in the table, I'd
have a consignee table that contains all of their address information.

However, given the structure you have provided, I think I would do this by
starting out with a aggregate query that identifies those record consignees
that have multiple shipments. Something like:

SELECT dCity, dState, dZip, consignee
FROM yourTable
GROUP BY dCity, dState, dZip, consignee
HAVING Count(*) > 1

If this is what you are looking for, then, I would join this query to your
table on all three fields and sort by consignee and shipdate.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.