Solved

Select SQL

Posted on 2013-06-11
6
318 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
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server stored proc 2 20
Can I convert a numeric string into referenced values with an SQL query? 10 41
This query failed in sql 2014 5 32
Query Syntax 17 36
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

803 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