• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Select SQL

Hello,
Can you please help,

I need to find the ccOrderNo that has same ccOrderNo but with different ShippingAddress for the same RepAccountNo

Please see sample attached.

Select WayBillNumber, ShipmentDate,RepAccountNo,RepName,ShippingAddress,City,
Province,PostalCode,CCOrderNo
From scanitem
WHERE RepAccountNo .....

Your help is appreciated.
thanks
Sample.xlsx
0
W.E.B
Asked:
W.E.B
  • 3
  • 2
1 Solution
 
BAKADYCommented:
I need to find the ccOrderNo that has same ccOrderNo but with different ShippingAddress for the same RepAccountNo
?????

Do you need to find all records which have under ccOrderNo the same value but different ShippingAddresses...??? i see the example and i find out that a ccOrderNo is own just by one RepAcountNo...

what about the records with just one shipping adress???

is this correct??? or i misunderstood your question??
0
 
W.E.BAuthor Commented:
Hello,
Do you need to find all records which have under ccOrderNo the same value but different ShippingAddresses...???

correct, but also, has the same RepAccountNo

In the sample I sent, this is results I needed

RepAccountNo            Address      ccOrderNo
000000000000852      123                 5728147
000000000000852      555                 5728147

000000000000887      14444         5725917
000000000000887      23232         5725917

000000000001407      444                 5728150
000000000001407      444           5728150
000000000001407      444         5728150
000000000001407      55555         5728150

Same CCOrderno, Same RepAccount , but different Address


what about the records with just one shipping adress???
No, I dont need those.


thanks,
0
 
Christopher GordonSenior Developer AnalystCommented:
select      *
from      scanitem
where      CCOrderNo in
(
      --set of order numbers that have more than one location
      select      CCOrderNo
      from      
      (
            --set of distinct Orders & locations
            select      distinct CCOrderNo, ShippingAddress, City, Province, PostalCode
            from scanitem
            group by CCOrderNo, ShippingAddress, City, Province, PostalCode

      ) Distinct_Location_Orders

      group by CCOrderNo

      having count(*) > 1
)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
W.E.BAuthor Commented:
perfect,
thank you
0
 
BAKADYCommented:
If this doesn't work let me know...
With ScanItemWithShippingLocationUniqueID (WayBillNumber, ShipmentDate, RepAccountNo, RepName, ShippingAddress, City, Province, PostalCode, ccOrderNo, ShippingLocationUniqueID) As (
    Select WayBillNumber
          , ShipmentDate
          , RepAccountNo
          , RepName
          , ShippingAddress
          , City
          , Province
          , PostalCode
          , ccOrderNo
          , CHECKSUM ( ShippingAddress, City, Province, PostalCode )
    From ScanItem
    -- If you require this, add this line, in other case comment it (12345 mean to be a RepAccountNo that you are searching for...)
    Where RepAccountNo = 12345
), ClearDuplicatedShippingLocationUniqueID (ccOrderNo, RepAccountNo, ShippingLocationUniqueID) As (
     Select Distinct ccOrderNo, RepAccountNo, ShippingLocationUniqueID
     From ScanItemWithShippingLocationUniqueID
), GetOrdersWithMoreThanOneShippingLocationUniqueID (ccOrderNo, RepAccountNo) As (
    Select ccOrderNo, RepAccountNo
    From ClearDuplicatedShippingLocationUniqueID
    Group By ccOrderNo, RepAccountNo
    Having Count(ShippingLocationUniqueID) > 1
)
Select WayBillNumber, ShipmentDate, RepAccountNo, RepName, ShippingAddress, City, Province, PostalCode, ccOrderNo
From ScanItemWithShippingLocationUniqueID Source, GetOrdersWithMoreThanOneShippingLocationUniqueID Filtered
Where Source.ccOrderNo = Filtered.ccOrderNo
  And Source.RepAccountNo = Filtered.RepAccountNo

Open in new window

Regards
0
 
BAKADYCommented:
select      *
from      scanitem
where      CCOrderNo in
(
      --set of order numbers that have more than one location
      select      CCOrderNo
      from      
      (
            --set of distinct Orders & locations
            select      distinct CCOrderNo, ShippingAddress, City, Province, PostalCode
            from scanitem
            group by CCOrderNo, ShippingAddress, City, Province, PostalCode

      ) Distinct_Location_Orders

      group by CCOrderNo

      having count(*) > 1
)

is only grouped by CCOrderNo... it ignores you want to compare RepAccountNo too...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now