Tuesday, January 13, 2009

Complicated WHERE Clause

Goal:
The filter is as followed.

_All Releases
Overflow
Release 1
Release 2
Release 3
Release 4
Release 5

When user selects _All Releases, the filter should only filter out Overflow. When user selects Overflow, the filter should only take Overflow. When user selects Release 1, the filter should take Iteration 1 through 9. When user selects other releases, the filter should take the selected release.

Solution:
((@Release='_All Releases' AND (i.[Iteration Path] <> '\Cobalt Product Backlog\Overflow'))OR (@Release = 'Overflow' AND i.[Iteration Path] = '\Cobalt Product Backlog\Overflow')OR (@Release <> '_All Releases' AND @Release <> 'Overflow' AND (i.[Iteration Path] LIKE '\Cobalt Product Backlog'+ CASE @Release WHEN 'Release 1' THEN '\Iteration%' ELSE '\'+@Release+'%' END)))

Thanks to the following post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54046

No comments: