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 2005 Forums  Transact-SQL (2005)  A better query than the one I have working?

Author  Topic  txDavid Starting Member 3 Posts Posted - 2007-09-13 : 22:04:06 I may be way off, but am able to pull the data, but not able to assemble it how I need it. This is a help desk type application and I'm trying to send mail updates when a case hasn't been touched in x number of days depending on the status of the case. tblocMain ocID customer caseNo engID tblEng engID engAlias tblocNotes ocNotesID ocID ocTimestamp tblocPriority ocPriID ocPriority SELECT tblocMain.Customer, tblocMain.caseNo, tblocPriority.ocPriority, tblEng.engAlias, datediff(dd, max(tblocNotes.ocTimestamp), getDate()) AS ignoreDays FROM tblocMain INNER JOIN tblEng ON tblocMain.engID = tblEng.engID INNER JOIN tblocNotes ON tblocMain.ocID = tblocNotes.ocID INNER JOIN tblocPriority ON tblocMain.ocPriID = tblocPriority.ocPriID WHERE tblocMain.ocDateClosed IS NULL GROUP BY tblocPriority.ocPriority, tblEng.engAlias, tblocMain.tacCase, tblocMain.Customer HAVING MAX(tblocNotes.ocTimestamp) <= '#getP1Date#' AND tblocPriority.ocPriority = 'P1' OR MAX(tblocNotes.ocTimestamp) <= '#getP2Date#' AND tblocPriority.ocPriority = 'P2' OR MAX(tblocNotes.ocTimestamp) <= '#getP3Date#' AND tblocPriority.ocPriority = 'P3' This gives me the appropriate data, however I really need to group on just the engAlias, so I can package all of that persons cases in a single email. Is there a better way to query this database in order to package the information how I need it? Thanks for any help you can provide!! Cheers