Help with Tweaking Group By

[ahmri]

Here is My store Proc. subDetail_has 3 possible values, 0-1-2. 0 being Complete, 1 attempted and 2 completed. I wanna my select to show only
the completed and only show Attempted IF there is no 2 associated to that record. It's basicaly a sequence attribute. 1 is before a action is taken and 2 after the action is taken. This is for An Audit Log. I have no Idea how to tweak the Group By or whatever to show What I want. Can you please help me?



CREATE PROCEDURE spV2KGetAuditLog
@actionID nvarchar(100),
@transactionID nvarchar(100),
@employeeID nvarchar(100),
@stationID nvarchar(100),
@dateFrom nvarchar(100),
@dateTo nvarchar(100),
@reference nvarchar(100)
AS

DECLARE @strQuery AS nvarchar(1500)
DECLARE @strQuery1 AS nvarchar(1000)
DECLARE @strQuery2 AS nvarchar(1000)
DECLARE @strQuery3 AS nvarchar(1000)
DECLARE @strQuery4 AS nvarchar(1000)
DECLARE @strQuery5 AS nvarchar(1000)
DECLARE @strQuery6 AS nvarchar(1000)
DECLARE @strEndOfQryStr AS nvarchar(1000)

SET @strQuery = 'SELECT auditLog.PK_ID AS [Audit ID], Emp.strFirstName + '' '' + '
+ 'Emp.strLastName AS [Employee Name], auditLog.StationID AS [Station], '
+ 'subDetail_1.SubjectName1 AS [Transaction], subDetail.SubjectName1 AS [Action], '
+ 'auditLog.Reference, auditLog.ReferenceID AS [Ref ID], auditLog.ReferenceToID AS [Ref To ID], '
+ 'subDetail_3.SubjectName1 AS [Sequence], auditLog.DateAudit AS [Log Date], subDetail_2.SubjectName1 AS [Modified Field] '
+ 'FROM tblSystemSubjectDetail AS subDetail, tblSystemSubjectDetail AS subDetail_1, tblSystemSubjectDetail AS subDetail_2, '
+ 'tblSystemSubjectDetail AS subDetail_3, tblProcessAuditLog AS auditLog, tblEmployee AS Emp '
+ 'WHERE Emp.EmployeeID = auditLog.EmployeeID '
+ 'AND ((subDetail.SystemSubjectID = 299) '
+ 'AND (subDetail.SystemSubjectID <> 304))'
+ 'AND ((subDetail_1.SystemSubjectID = 304) '
+ 'AND (subDetail_1.SystemSubjectID <> 299)) '
+ 'AND (subDetail_2.SystemSubjectID = 344) '
+ 'AND (subDetail_3.SystemSubjectID = 348) '
+ 'AND subDetail.KeyValue = auditLog.ActionID '
+ 'AND subDetail_1.KeyValue = auditLog.TransactionID '
+ 'AND subDetail_2.KeyValue = auditLog.ModifiedFieldID '
+ 'AND subDetail_3.KeyValue = auditLog.[Sequence] '

IF @actionID <> ''
BEGIN
SET @strQuery1 = ' AND auditLog.ActionID = ' + @actionID
END
ELSE
BEGIN
SET @strQuery1 =''
END
IF @transactionID <> ''
BEGIN
SET @strQuery2 = ' AND auditLog.TransactionID = ' + @transactionID
END
ELSE
BEGIN
SET @strQuery2 =''
END
IF @employeeID <> ''
BEGIN
SET @strQuery3 = ' AND auditLog.EmployeeID = ' + @employeeID
END
ELSE
BEGIN
SET @strQuery3 =''
END
IF @stationID <> ''
BEGIN
SET @strQuery4 = ' AND auditLog.StationID LIKE ''%' + @stationID + '%'''
END
ELSE
BEGIN
SET @strQuery4 =''
END
IF @dateFrom <> '' AND @dateTo <> ''
BEGIN
SET @strQuery5 = ' AND auditLog.DateAudit BETWEEN ''' + @dateFrom + ''' AND ''' + @dateTo + ''''
END
ELSE
BEGIN
SET @strQuery5 = ' AND auditLog.DateAudit BETWEEN ''' + 'Jan 1 1900 12:00AM' + ''' AND ''' + CAST(getDate() AS NVARCHAR(100)) + ''''
END
IF @reference <> ''
BEGIN
SET @strQuery6 = ' AND auditLog.Reference LIKE ''%' + @reference + '%'' '
END
ELSE
BEGIN
SET @strQuery6 =''
END

SET @strEndOfQryStr= ' GROUP BY subDetail.SubjectName1, subDetail_1.SubjectName1, '
+ 'auditLog.PK_ID, Emp.strFirstName + '' '' + Emp.strLastName, '
+ 'auditLog.StationID, auditLog.Reference, auditLog.ReferenceID, '
+ 'auditLog.ReferenceToID, subDetail_3.SubjectName1, auditLog.DateAudit, '
+ 'subDetail_2.SubjectName1 '
+ 'ORDER BY Emp.strFirstName + '' '' + Emp.strLastName, '
+ 'subDetail.SubjectName1, subDetail_1.SubjectName1'

--PRINT (@strQuery + @strQuery1 + @strQuery2 + @strQuery3 + @strQuery4 + @strQuery5 + @strQuery6 + @strEndOfQryStr)
EXECUTE (@strQuery + @strQuery1 + @strQuery2 + @strQuery3 + @strQuery4 + @strQuery5 + @strQuery6 + @strEndOfQryStr)
GO