Group By to count differences - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Group By to count differences

Author

Topic

vmon
Yak Posting Veteran

63 Posts

Posted - 2006-06-07 : 10:56:59

Is there are way to group records from a table and get a count of the records where the group by does not group because of data differences. I want to group order lines by order number to see if any lines on an order has different schedule dates. I am not sure how to get count to do this. Here is what it looks like right now. The count is giving me the number of lines and I only want to know if an order has two different dates. Will I have to use a cursor or is is possible with a select? Thanks for your help.

SELECT OrderNumber, ScheduledDate, LineDueDate, DeliveryDate, COUNT(*) AS Lines
FROM dbo.tblSchedule
GROUP BY OrderNumber, ScheduledDate
ORDER BY OrderNumber

vmon
Yak Posting Veteran

63 Posts

Posted - 2006-06-07 : 11:13:49

Figured out my on problem. Here is what I did. THis gave me the order numbers and the line on the orders where the dates on the lines do not match for the order.

SELECT OrderNumber, COUNT(*) AS Orders
FROM (SELECT OrderNumber, ScheduledDate, LineDueDate, DeliveryDate FROM dbo.tblSchedule GROUP BY OrderNumber, ScheduledDate, LineDueDate, DeliveryDate, OrderNumber) DERIVEDTBL
GROUP BY OrderNumber
HAVING (COUNT(*) > 1)
ORDER BY COUNT(*) DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources