asked on

SQL Query to detect different value

I have a table that contains a separate row for each item in an order. Each row for an order should have the same date. Unfortunately, some don't. I need to detect which orders has a row with a date that is different from the other rows for the same order.

For example:
In table 'Orders' I am searching for row like this. Row 111 has a date different from the other rows. How do I detect this in a SQL Query? I can't specify the OrderID. I need to search a large table in one query.

ID OrderID       ShipDate
111 1001        2009-05-12 00:00:00.000
112 1001        2010-01-26 00:00:00.000
113 1001        2010-01-26 00:00:00.000
114 1001        2010-01-26 00:00:00.000

Thanks,
DT

SQL Server 2008

ASKER CERTIFIED SOLUTION

Bhavesh Shah

membership

This solution is only available to members.

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

Start Free Trial

Rajkumar Gs

Please check the attached script.

Raj

create table #orders
(
	ID			int,
	OrderID     int,
	ShipDate	datetime
)

insert into #orders
select 111,      1001,              '2009-05-12 00:00:00.000' union all
select 112,      1001,              '2010-01-26 00:00:00.000' union all
select 113,      1001,              '2010-01-26 00:00:00.000' union all
select 114,      1001,               '2010-01-26 00:00:00.000' union all
select 115,      1002,              '2010-01-27 00:00:00.000' union all
select 116,      1002,               '2010-01-27 00:00:00.000' 

select * from #orders


-- OrderIDs that are in different dates
select OrderID
from #orders
group by OrderID
having count(distinct ShipDate) > 1

drop table #orders

Open in new window

rmm2001

Will you ever run into the scenario:

ID OrderID ShipDate
111 1001 2009-05-12 00:00:00.000
112 1001 2010-01-26 00:00:00.000
113 1001 2010-01-26 00:00:00.000
114 1001 2011-05-12 00:00:00.000
115 1001 2012-01-26 00:00:00.000
116 1001 2012-01-26 00:00:00.000
117 1001 2013-01-26 00:00:00.000

Where you have multiple different dates for the same order number and those multiple different dates have more than one entry? If so the query above would be a little stretched to work. If not the it'll work just fine

Rajkumar Gs

Cont: of my comment http:#34155350

If you want to return all the rows related to those OrderIDs that are scattered in different dates, try this query.

select * from #orders where OrderID in
(
	select OrderID
	from #orders
	group by OrderID
	having count(distinct ShipDate) > 1
)

Open in new window

Regards
Raj

awking00

Is it possible to have more than one date that is different from the others?

DTwined

ASKER

First one I tried and it did the trick. I changed the '=1' to '>1' to get it to do what I needed it to do. Thanks for all the feedback!