Incorrect syntax near the keyword 'BETWEEN'

sharon2011
sharon2011 used Ask the Experts™
on
I could not figure out what is wrong with my syntax:

declare @StartDate DateTime,
@EndDate DateTime,
@BranchName varchar (20),
@ReportType varchar(50)
set @StartDate='02/1/2012'
set @EndDate='02/29/2012'

SELECT [Branch Name], [Stage Number],
[Not Signed Application Date],
[Fund Date],
[UW Approve Date]
FROM          tablename
WHERE        
 [Branch Name] in(@BranchName)AND ([Stage Number] >= 20)  AND
case
 WHEN @ReportType=a'
    THEN [Not Signed Application Date] BETWEEN @StartDate AND @EndDate
WHEN @ReportType='b'
  THEN [Fund Date] BETWEEN @StartDate AND @EndDate
WHEN @ReportType='c'
   THEN [UW Approve Date] BETWEEN @StartDate AND @EndDate
END


Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
The THEN and ELSE results must be a single *value* (or an expression that ultimately yields a single value), NOT a keyword or other multi-word result.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Therefore, change it to this:


case
 WHEN @ReportType=a' AND [Not Signed Application Date] BETWEEN @StartDate AND @EndDate THEN 1
WHEN @ReportType='b' AND [Fund Date] BETWEEN @StartDate AND @EndDate THEN 1
WHEN @ReportType='c' AND [UW Approve Date] BETWEEN @StartDate AND @EndDate THEN 1
ELSE 0
END = 1

Author

Commented:
It works. Thank you very very much.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial