Solved

Select SQL

Posted on 2013-06-11
6
322 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:W.E.B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 4

Expert Comment

by:BAKADY
ID: 39238919
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:W.E.B
ID: 39238975
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
ID: 39238979
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Closing Comment

by:W.E.B
ID: 39238996
perfect,
thank you
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39239004
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
ID: 39239033
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

691 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