Narrow down dataset

Try this.

SELECT  bcncl.customer_id, order_number, customer_name, phone_number, 
        bcncl.ship_date, estimated_delivery_date, date_created, 
        date_contacted, status, service_type, country_code, client, 
        is_backorder, bcncl.hu_id, wh_id
 FROM   bcustomer_notification_call_list bcncl
 join (select customer_id,ship_date,min(hu_id) hu_id from bcustomer_notification_call_list bncl_IN group by customer_id, ship_date) BNCL
on bcncl.customer_id = bncl.customer_id
and bcncl.ship_date = bncl.ship_date
and bcncl.hu_id = bncl.hu_id
 WHERE  (status = 'New' OR status = 'Sent' OR status = 'Time Zone Skip')
 AND    time_zone in ('E','M','P')
 AND    client = 'OB'

Open in new window

ASKER

it gives me the same results as the query i pasted. were you doing a self join in our query?

It should only show the min(hu_id) for each customer for each day.

Give this a try:

SELECT
            DISTINCT
            customer_id, order_number, customer_name, phone_number,
CAST(CONVERT(VARCHAR(10), ship_date, 101) AS SMALLDATETIME) AS ship_date,
            CAST(CONVERT(VARCHAR(10), estimated_delivery_date, 101) AS SMALLDATETIME) AS estimated_delivery_date,
            CAST(CONVERT(VARCHAR(10), date_created, 101) AS SMALLDATETIME) AS date_created,
CAST(CONVERT(VARCHAR(10), date_contacted, 101) AS SMALLDATETIME) AS date_contacted,
            status, service_type, country_code, client,
is_backorder, hu_id, wh_id
FROM bcustomer_notification_call_list
WHERE (status = 'New' OR status = 'Sent' OR status = 'Time Zone Skip')
AND time_zone in ('E','M','P')
AND client = 'OB'

I wasn't accounting for the time in the date. This corrects it.

SELECT  bcncl.customer_id, order_number, customer_name, phone_number, 
        bcncl.ship_date, estimated_delivery_date, date_created, 
        date_contacted, status, service_type, country_code, client, 
        is_backorder, bcncl.hu_id, wh_id
 FROM   bcustomer_notification_call_list bcncl
 join (select customer_id,convert(varchar(12),ship_date, 101) ship_date ,min(hu_id) hu_id from bcustomer_notification_call_list bncl_IN group by customer_id, ship_date) BNCL
on bcncl.customer_id = bncl.customer_id
and bcncl.convert(varchar(12),ship_date, 101) = bncl.ship_date
and bcncl.hu_id = bncl.hu_id
 WHERE  (status = 'New' OR status = 'Sent' OR status = 'Time Zone Skip')
 AND    time_zone in ('E','M','P')
 AND    client = 'OB'

Open in new window

ASKER

hello everyone,

I have tried each one of the queries, and i still get all huids(boxes) for an order where all boxes were shipped on the same date. i need only to show the individual boxes pertaining to the same order, only if the ship date is different amongs them.

attached there is a file with test data i am looking at, i have pasted the table structure, may be this helps a little bit?

anyhow, thank you very much for your kind help.

https://filedb.experts-exchange.com/incoming/ee-stuff/6802-test_data.txt
test-data.txt

ASKER

if you look at the data, you will see that i.e campaign id 673 and 674 have all the same such as cust number, order number, ut different hu id, same ship date.

i should only see one row not two, because this order has 2 boxes that were shipped on the same day. i should see two rows only if the boxes were shipped on different day.

thanks,

Ok, try this:

SELECT
            DISTINCT
            customer_id,
            order_number,
            customer_name,
            phone_number,
CAST(CONVERT(VARCHAR(10), ship_date, 101) AS SMALLDATETIME) AS ship_date,
            CAST(CONVERT(VARCHAR(10), estimated_delivery_date, 101) AS SMALLDATETIME) AS estimated_delivery_date,
            CAST(CONVERT(VARCHAR(10), date_created, 101) AS SMALLDATETIME) AS date_created,
CAST(CONVERT(VARCHAR(10), date_contacted, 101) AS SMALLDATETIME) AS date_contacted,
            status,
            service_type,
            country_code,
            client,
is_backorder,
            (SELECT TOP 1 hu_id FROM bcustomer_notification_call_list WHERE order_number = T.order_number) AS hu_id,
            wh_id
FROM bcustomer_notification_call_list T
WHERE (status = 'New' OR status = 'Sent' OR status = 'Time Zone Skip')
AND time_zone in ('E','M','P')
AND client = 'OB'

ASKER

will this query work, in case there are orders that have multiple hu ids but these have a different ship date.

the problem was the ship date being the same.

let's say i want to call a customer to let them know their order is going to arrive on 02/14/2007

i don't want to call them 2 times for the same order (different hu ids still) if they are shipped on the same date, because most likely they will arrive together.

i would only call a customer twice for the same order, if the hu ids(boxes) that are part of the same order were shipped on diff. dates.

sorry for being redundant.

thank you,

ASKER

wouldn't the last solution proposed be retrieving the top hu id for an order, leaving out all other hu ids that may be part of the same order but with a different ship date?

Yes as long as all of the other columns are the same, with the hu_id it is just selecting the top one so it would be the same.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

digital_thoughts

Your solution works but if I add the campaign id column to the select query, then I do get the duplicates.

BrandonGalderisi

Your solution works still when I add the campaign id column.

The only concern I have is that this query will return also all the orders that are duplicated in the table but that their hu ids have different dates? or is this query just going to exclude repeated orders without looking at the ship date?

thank you very much you help has been awesome.

ASKER

i am attaching a second file with more test data and some comments so you can better understand what i am talking about. thank you for your patience.

test-data-ii.txt

ASKER

i am checking the results and seems like Brandon's query works correctly.
let me do some more checks, Thanks!

itortu:

I have solved this problem numerous times with this method. It should do exactly what you described. Now if there is more information that was left out... it may not.

ASKER

no you are right, i just needed to do some testing and verify the results.

your query has done the job i was looking for, i very much thank you for that as well digital thoughts.

i am going to close this question now.

once again, thank you!

ASKER

Bradon, would you please offer some explanation if the query you help me with?

Thank you,

itortu.