Narrow down dataset

Hello all,

I have a table that stores data such as id, customer number, order number, box number, ship date, etc

an order number and a customer number can be duplicated in the table as long as the box number is different.

when i select records that match the criteria of the query pasted down below, I am getting these results:

674      21608490             6176716      AMY BANNER      999-999-9999      2008-02-08 11:07:36.000      2008-02-14 11:07:36.000      2008-02-08 13:03:51.173      2008-02-11 15:36:41.267      Sent      Ground      US      OB      N      LP1279298      1      4

673      21608490             6176716      AMY BANNER      999-999-9999      2008-02-08 11:07:01.000      2008-02-14 11:07:01.000      2008-02-08 13:03:50.893      2008-02-11 15:36:41.343      Sent      Ground      US      OB      N      LP1279299      1      4

705      21608957             6178738      LIUDMYLA STOROZHUK      999-999-9999      2008-02-08 11:20:07.000      2008-02-14 11:20:07.000      2008-02-08 13:04:27.830      2008-02-11 15:37:21.093      Sent      Ground      US      OB      N      LP1279352      1      4

704      21608957             6178738      LIUDMYLA STOROZHUK      999-999-9999      2008-02-08 11:22:51.000      2008-02-14 11:22:51.000      2008-02-08 13:04:27.517      2008-02-11 15:37:21.170      Sent      Ground      US      OB      N      LP1279353      1      4

the results look the same but the difference in the rows is that campaign id and the box number (hu_id)

these results could be acceptable if each box was shipped on a different date (date portion only) but they were shipped the same day. what i need is to be able to tell if multiple boxes (hu_id) pertaining to an order have a same ship date, if they do, then i only care for the whole order (1 row retunred), if they have the same order number but different ship dates, then i need to retrieve them separately.

is this possible to achieve?

any help is greatly appreciated it, i ahve been working on this for a long time and i can't see the solution to my dilemma.

thank you very much.

CREATE TABLE [dbo].[bcustomer_notification_call_list](
	[campaign_id] [int] IDENTITY(1,1) NOT NULL,
	[customer_id] [char](15) NOT NULL,
	[order_number] [varchar](30) NOT NULL,
	[customer_name] [varchar](50) NOT NULL,
	[time_zone] [char](1) NOT NULL,
	[phone_number] [varchar](30) NOT NULL,
	[language] [char](2) NOT NULL,
	[ship_date] [datetime] NOT NULL,
	[estimated_delivery_date] [datetime] NULL,
	[date_created] [datetime] NOT NULL,
	[date_contacted] [datetime] NULL,
	[status] [varchar](30) NOT NULL,
	[service_type] [varchar](30) NULL,
	[country_code] [char](2) NULL,
	[client] [char](2) NOT NULL,
	[is_backorder] [char](1) NULL,
	[hu_id] [varchar](22) NOT NULL,
	[wh_id] [char](1) NOT NULL)
 
 
this is the select:
 
SELECT  customer_id, order_number, customer_name, phone_number, 
        ship_date, estimated_delivery_date, date_created, 
        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'

Open in new window

itortuAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
BrandonGalderisiConnect With a Mentor Commented:
Ok.  I tried reading through the comments..  Is the problem that multiple orders are not showing up?

Because my previous posts would not have worked correctly with that.

Try this.
SELECT  bcncl.customer_id, bcncl.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,order_number,convert(varchar(12),ship_date, 101) ship_date ,min(hu_id) hu_id from bcustomer_notification_call_list bncl_IN group by customer_id, order_number,convert(varchar(12),ship_date, 101)) BNCL
on bcncl.customer_id = bncl.customer_id
and bcncl.order_number = bncl.order_number
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

0
 
BrandonGalderisiCommented:
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

0
 
itortuAuthor Commented:
it gives me the same results as the query i pasted. were you doing a self join in our query?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
BrandonGalderisiCommented:
It should only show the min(hu_id) for each customer for each day.
0
 
digital_thoughtsCommented:
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'
0
 
BrandonGalderisiCommented:
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

0
 
itortuAuthor Commented:
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
0
 
itortuAuthor Commented:
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,
0
 
digital_thoughtsCommented:
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'
0
 
itortuAuthor Commented:
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,
0
 
itortuAuthor Commented:
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?
0
 
digital_thoughtsCommented:
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.
0
 
digital_thoughtsConnect With a Mentor Commented:
Actually, you're right it wouldn't select the correct hi_ids, try this instead:

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 customer_id = T. customer_id AND order_number = T.order_number AND CAST(CONVERT(VARCHAR(10), ship_date, 101) AS SMALLDATETIME) = CAST(CONVERT(VARCHAR(10), T.ship_date, 101) AS SMALLDATETIME) AND status = T.status) 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'
0
 
itortuAuthor Commented:
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.



0
 
itortuAuthor Commented:
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
0
 
itortuAuthor Commented:
i am checking the results and seems like Brandon's query works correctly.
let me do some more checks, Thanks!
0
 
BrandonGalderisiCommented:
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.
0
 
itortuAuthor Commented:
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!
0
 
itortuAuthor Commented:
Bradon, would you please offer some explanation if the query you help me with?

Thank you,

itortu.
0
 
BrandonGalderisiCommented:
The subquery:
(select customer_id,order_number,convert(varchar(12),ship_date, 101) ship_date ,min(hu_id) hu_id from bcustomer_notification_call_list bncl_IN group by customer_id, order_number,convert(varchar(12),ship_date, 101)) BNCL

Returns the first hu_id for each customer_id, order_number and formatted ship_date.  
This query is then joined to the main table so that only the first hu_id is shown.
0
All Courses

From novice to tech pro — start learning today.