Solved

# Using CASE

Posted on 1998-08-01
Medium Priority
232 Views

CREATE PROCEDURE spselectLastmonthcheque1
As
Declare @bbb datetime
Declare @aaa int
Set @bbb = getdate()
Set @aaa = month(@bbb)
Set @aaa = @aaa -1
Select  * from cheque1 Where [date] between
Case
When @aaa = 0 Then 'Jan 1 1998 12:00AM' and 'Jan 31 1998 12:00AM'
When @aaa = 1 Then 'Feb 1 1998 12:00AM' and 'FEB 29 1998 12:00AM'
WHEN @aaa = 2 Then 'Mar 1 1998 12:00AM' and 'Mar 31 1998 12:00AM'
WHEN @aaa = 3 Then 'Apr 1 1998 12:00AM' and 'Apr 31 1998 12:00AM'
WHEN @aaa = 4 Then 'May 1 1998 12:00AM' and 'May 31 1998 12:00AM'
WHEN @aaa = 5 Then 'Jun 1 1998 12:00AM' and 'Jun 31 1998 12:00AM'
WHEN @aaa = 6 Then 'Jul 1 1998 12:00AM' and 'Jul 31 1998 12:00AM'
WHEN @aaa = 7 Then 'Aug 1 1998 12:00AM' and 'Aug 31 1998 12:00AM'
WHEN @aaa = 8 Then 'Sep 1 1998 12:00AM' and 'Sep 31 1998 12:00AM'
WHEN @aaa = 9 Then 'Oct 1 1998 12:00AM' and 'Oct 31 1998 12:00AM'
WHEN @aaa = 10 Then 'Nov 1 1998 12:00AM' and 'Nov 31 1998 12:00AM'
WHEN @aaa = 11 Then 'Dec 1 1998 12:00AM' and 'Dec 31 1998 12:00AM'

Error incorrect syntah near "and"

0
Question by:mativare

Expert Comment

ID: 1089357
Try this:

CREATE PROCEDURE spselectLastmonthcheque1
As
Declare @bbb datetime
Declare @aaa int
Declare @startdate datetime /* new variable */
Declare @enddate datetime   /* new variable */
Set @bbb = getdate()
Set @aaa = month(@bbb)
Set @aaa = @aaa -1
Case
When @aaa = 0 Then do
@startdate = 'Jan 1 1998 12:00AM'
@enddate= 'Jan 31 1998 12:00AM'
end
When @aaa = 1 Then do
@startdate = 'Feb 1 1998 12:00AM'
@enddate = 'FEB 29 1998 12:00AM'
end

WHEN @aaa = 11 Then do
@startdate = 'Dec 1 1998 12:00AM'
@enddate = 'Dec 31 1998 12:00AM'
end
end case  /* This syntax probably isn't right. I'm at home, */
/* and my TSQL manuals are at work. */

Select  * from cheque1
Where [date] => @startdate
AND [date] <= @enddate
0

LVL 4

Accepted Solution

mitek earned 120 total points
ID: 1089358

One way to do that would be:

CREATE PROCEDURE spselectLastmonthcheque1
As
Declare @bbb datetime
Declare @aaa int
Set @bbb = getdate()
Set @aaa = month(@bbb)
Set @aaa = @aaa -1
Select * from cheque1
Where
(@aaa = 0 And [date] between 'Jan 1 1998 12:00AM' and 'Jan 31 1998 12:00AM')
OR (@aaa = 1 And [date] between 'Feb 1 1998 12:00AM' and 'FEB 29 1998 12:00AM')
OR (@aaa = 2 And [date] between 'Mar 1 1998 12:00AM' and 'Mar 31 1998 12:00AM')
OR (@aaa = 3 And [date] between 'Apr 1 1998 12:00AM' and 'Apr 31 1998 12:00AM')
OR (@aaa = 4 And [date] between 'May 1 1998 12:00AM' and 'May 31 1998 12:00AM')
OR (@aaa = 5 And [date] between 'Jun 1 1998 12:00AM' and 'Jun 31 1998 12:00AM')
OR (@aaa = 6 And [date] between 'Jul 1 1998 12:00AM' and 'Jul 31 1998 12:00AM')
OR (@aaa = 7 And [date] between 'Aug 1 1998 12:00AM' and 'Aug 31 1998 12:00AM')
OR (@aaa = 8 And [date] between 'Sep 1 1998 12:00AM' and 'Sep 31 1998 12:00AM')
OR (@aaa = 9 And [date] between 'Oct 1 1998 12:00AM' and 'Oct 31 1998 12:00AM')
OR (@aaa = 10 And [date] between 'Nov 1 1998 12:00AM' and 'Nov 31 1998 12:00AM')
OR (@aaa = 11 And [date] between 'Dec 1 1998 12:00AM' and 'Dec 31 1998 12:00AM')

not the best way for this particular problem -- just the first thing that came to mind

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
###### Suggested Courses
Course of the Month8 days, 19 hours left to enroll