Thursday, 12 September 2013

Simplify sql query logic

Simplify sql query logic

I have the following query that is used in a stored procedure. I am trying
to simplify the logic in the CASE statement. Specifically I would like to
nest the Product inside the UserGroup
Thanks, Brad
SELECT
DCMNumber,
SUM(CONVERT(INT, CurrentlyAssigned)) AS PriorAssigned
FROM
dbo.cauAssignedClaim WITH(NOLOCK)
WHERE
RecordType = 'A' AND ([Status] <> 'DE' OR [Status] IS NULL)
AND DATEADD(dd, 0, DATEDIFF(dd, 0, EntryDate)) BETWEEN
CASE
WHEN Product IN('LTD', 'LTDCP') AND @UserGroup = '' OR @UserGroup =
'NONE'
THEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-30))
WHEN Product IN('STD', 'STDCP', 'SALCN', 'STAT', 'OFFOV') AND
@UserGroup = '' OR @UserGroup = 'NONE'
THEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-14))
WHEN Product IN('LTD', 'LTDCP') AND @UserGroup IN('SSAT', 'TCMS')
THEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-7))
WHEN Product IN('STD', 'STDCP', 'SALCN', 'STAT', 'OFFOV') AND
@UserGroup IN('Appeals', 'DMS', 'Life', 'WOP', 'IWOP')
THEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-7))
END
AND DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-1))
GROUP BY [Status], DCMNumber

No comments:

Post a Comment