Solved

Select SQL

Posted on 2013-06-11
6
317 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
using t-sql EXISTS 8 39
SQL Server Error Log - logging period 1 32
Excel conversion issue with Sql server 14 46
Merge Statement 3 39
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

914 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

21 Experts available now in Live!

Get 1:1 Help Now