Solved

Select SQL

Posted on 2013-06-11
6
316 Views
Last Modified: 2013-06-11
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
Comment
Question by:Wass_QA
  • 3
  • 2
6 Comments
 
LVL 4

Expert Comment

by:BAKADY
Comment Utility
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
 

Author Comment

by:Wass_QA
Comment Utility
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
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 300 total points
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Closing Comment

by:Wass_QA
Comment Utility
perfect,
thank you
0
 
LVL 4

Expert Comment

by:BAKADY
Comment Utility
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
 
LVL 4

Expert Comment

by:BAKADY
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now