DTwined
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
Regards
Raj
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
)
Regards
Raj
Is it possible to have more than one date that is different from the others?
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!
Raj
Open in new window