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
WHERE (status = 'New' OR status = 'Sent' OR status = 'Time Zone Skip')
AND time_zone in ('E','M','P')
AND client = 'OB'