Avoid duplicate orders returned

Question

Avoid duplicate orders returned

itortu

SSCommitted

Points: 1914
More actions
February 11, 2008 at 5:53 pm

#179060

i am working on a query that is returning duplicate order and i would like to know how can i avoid the duplicate orders to be returned let me show my current query:
SELECT distinct a.[campaign_id]
,a.[customer_id]
,a.[order_number]
,a.[customer_name]
,a.[phone_number]
,CONVERT(DATETIME,CONVERT(VARCHAR,a.[ship_date] ,101)) as ship_date, b.code_value1
FROM [bcustomer_notification_call_list] a
JOIN bcode_table b
ON a.[service_type] = b.[code_typekey]
WHERE (a.[status] = 'New' OR a.[status] = 'Sent' OR a.[status] = 'Time Zone Skip')
AND a.[time_zone] in ('E','M','P')
AND a.client = 'OB'
ORDER BY b.[code_value1], a.[campaign_id]
,a.[customer_id]
,a.[order_number]
,a.[customer_name]
,a.[phone_number]
,CONVERT(DATETIME,CONVERT(VARCHAR,a.[ship_date] ,101))
the output of this query is:
597 21607045 6170296 LP1279468 LESLIE SUMNER 999-999-9999 2008-02-08 00:00:00.000 4
598 21607045 6170296 LP1279469 LESLIE SUMNER 999-999-9999 2008-02-08 00:00:00.000 4
673 21608490 6176716 LP1279299 AMY BANNER 999-999-9999 2008-02-08 00:00:00.000 4
674 21608490 6176716 LP1279298 AMY BANNER 999-999-9999 2008-02-08 00:00:00.000 4
704 21608957 6178738 LP1279353 LIUDMYLA STOROZHUK 999-999-9999 2008-02-08 00:00:00.000 4
705 21608957 6178738 LP1279352 LIUDMYLA STOROZHUK 999-999-9999 2008-02-08 00:00:00.000 4
these records are duplicates, all the order numbers, customer numbers are the same, the only unique column is hu_id.
i would like to only return one row per order if the ship date for each hu id is the same, if the ship date of each hu id is different, then i do need all the rows even if the order and customer numbers are the same.
i hope this makes sense. i have been stuck for a while trying to make my query work, any ideas, help recommendations are appreciated it.
thank you very much.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

First, since you don't have HU_ID in any part of your query, you need to tell us which table it's in.

Second, I think you also have dupes in the bcustomer_notification_call_list as witnessed by the campaign_id. It that's true, which dupe would you like to keep?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 2

Hi,

First of there is no problem with your order by clause. The results are sorted based on the Order by Clause. In your query you have "campaign_id" which is unique. So to satisfy your requirement you need to remove this column.

Try to avoid DISTINCT column to get the results. if you are using DISTINCT then the tables are not normalized properly.

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

itortu SSCommitted Points: 1914 More actions · Answer 3

i do have hu_id on the result dataset i pasted above.

campaign id customer_number order_number hu_id customer_name phone_number ship_date

597 21607045 6170296 LP1279468 LESLIE SUMNER 999-999-9999 2008-02-08 00:00:00.000

code_value1

4

i need to keep duplicates for all orders that hu_id (items) shipped on a different date.

i.e.

these 2 rows:

597 21607045 6170296 LESLIE SUMNER 999-999-9999 2008-02-08 00:00:00.000 4

598 21607045 6170296 LESLIE SUMNER 999-999-9999 2008-02-08 00:00:00.000 4

could be include in the results if the ship dates were:

597 21607045 6170296 LESLIE SUMNER 999-999-9999 2008-02-08 00:00:00.000 4

598 21607045 6170296 LESLIE SUMNER 999-999-9999 2008-02-10 00:00:00.000 4

thank you very much for your help.:)

itortu SSCommitted Points: 1914 More actions · Answer 4

i do need to retrieve campaign_id, i did try to run the query without it and i was able to get rid of the duplicates, but i need to find a different way to achieve the same including campaign_id.

thank you.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

itortu (2/11/2008)
i do have hu_id on the result dataset i pasted above.

Yes, you do... but not in the code you posted... we can't tell which table hu_id is in.

Also, you didn't answer my other question... I'll ask it in a slightly different manner... do you have the same customer in the bcustomer_notification_call_list table more than once? If you do, which row do you want to keep for each customer by campaignID?

Slow down, read my questions thoroughly... answer them all... I'm trying to help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

itortu SSCommitted Points: 1914 More actions · Answer 6

Thank you for your help and an apology for not providing all the necessary details.

Answering your question

Do you have the same customer in the bcustomer_notification_call_list table more than once?

yes, i do have the same customer more than once in this table.

Which row do you want to keep for each customer by campaignID?

Yes i want to keep campaign_id.

hu_id is another column in the bcustomer_notification_call_list table.

Once again, thank you much for your help.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Which row do you want to keep for each customer by campaignID?
Yes i want to keep campaign_id.

Well.... that's why you're getting the dupes in the results... You have dupes in the table. 😉

If you only want to keep one row for each customer in the results, you'll need to tell use which row you want to keep. In other words, do you want to keep the highest Campaign_ID, the lowest Campaign_ID, or something else?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

itortu SSCommitted Points: 1914 More actions · Answer 8

an order can have multiple hu_ids (boxes)

each box belonging to an order is shipped separately.

let's say an order is conformed of 3 boxes, 2 boxes are shipped on 2/10/2008

but the 3rd one is shipped on 2/11/2008

if i insert records from bcustomer_notification_call_list (table a)

to orders_shipped (table b) on 2/10/2008 i would insert only one row.

when i insert records on 2/11/2008 then i will insert one row again for the same order, because this is the 3rd box with a different shipping date.

right now i am selecting two rows from table a and inserting two rows in table b even though these two rows are for the same order with the same shipping date.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

So, what is it that you want to do? You've identified everything but that...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

itortu SSCommitted Points: 1914 More actions · Answer 10

i want to select the rows that can share same order number, customer number, and have campaign id, different hu id and different ship date.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

You had all of that in your first post... obviously, you want some specific format and I'm tired of guessing...

Would you please type up what you want the output to look like... do be precise in the accompanying explanation...

I strongly suggest you read the following article before you make another post...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)