• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

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
0
DTwined
Asked:
DTwined
1 Solution
 
Bhavesh ShahLead AnalysistCommented:
Hi,

check this

select OrderID,count(distinct shipdate)shipdate
from tablename
group by orderid
having count(distinct shipdate) = 1
0
 
Rajkumar GsSoftware EngineerCommented:
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

0
 
rmm2001Commented:
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
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.

 
Rajkumar GsSoftware EngineerCommented:
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
0
 
awking00Commented:
Is it possible to have more than one date that is different from the others?
0
 
DTwinedAuthor Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now