• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Using Case in a where clause (with Between)

Hi folks,

I'm having some trouble using case in a select statement:  The query provides a rolling view of client activity, from the 21st of a month to the 20th of the next month.  I figured out the code to "roll over" to the next month when the month changes, but I'd like to cause the rollover to occur at anytime after the 22nd day of the current month.  This is what I've come up with, but I get a syntax error.

WHERE
dbo.ClientTrips2.FirstTrip BETWEEN CASE
       WHEN (datepart(dd, getdate())<22 THEN convert(datetime,dateadd(month,-2,dateadd(day, 21-datepart(dd,getdate()),getdate())),102) AND convert(datetime,dateadd(month,-1,dateadd(day, 20-datepart(dd,getdate()),getdate())),102)
       WHEN (datepart(dd, getdate())>21 THEN convert(datetime,dateadd(month,-1,dateadd(day, 21-datepart(dd,getdate()),getdate())),102) AND convert(datetime,dateadd(month,0,dateadd(day, 20-datepart(dd,getdate()),getdate())),102)
END

Before I added the Case, when, then and end statements, everything else worked (& works) fine.

Help?

Thanks in advance,

Charlie T.
0
charlietou
Asked:
charlietou
  • 2
2 Solutions
 
HuyBDCommented:
missing parentheses.
Try to seperate to exp of between clause.

WHERE
dbo.ClientTrips2.FirstTrip BETWEEN
CASE
      WHEN (datepart(dd, getdate()))<22 THEN convert(datetime,dateadd(month,-2,dateadd(day, 21-datepart(dd,getdate()),getdate())),102)
      WHEN (datepart(dd, getdate()))>21 THEN convert(datetime,dateadd(month,-1,dateadd(day, 21-datepart(dd,getdate()),getdate())),102)
END
AND
CASE
      WHEN (datepart(dd, getdate()))<22 THEN convert(datetime,dateadd(month,-1,dateadd(day, 20-datepart(dd,getdate()),getdate())),102)
      WHEN (datepart(dd, getdate()))>21 THEN convert(datetime,dateadd(month,0,dateadd(day, 20-datepart(dd,getdate()),getdate())),102)
END
0
 
Anthony PerkinsCommented:
Try it this way:

WHERE      (Day(GETDATE()) < 22
      And dbo.ClientTrips2.FirstTrip BETWEEN convert(datetime,dateadd(month,-2,dateadd(day, 21-datepart(dd,getdate()),getdate())),102) AND convert(datetime,dateadd(month,-1,dateadd(day, 20-datepart(dd,getdate()),getdate())),102))
      Or (Day(GETDATE()) > 21
      And dbo.ClientTrips2.FirstTrip BETWEEN convert(datetime,dateadd(month,-1,dateadd(day, 21-datepart(dd,getdate()),getdate())),102) AND convert(datetime,dateadd(month,0,dateadd(day, 20-datepart(dd,getdate()),getdate())),102))
0
 
HuyBDCommented:
I see the convert fuction in you code, It may be unnessesary!

WHERE
dbo.ClientTrips2.FirstTrip BETWEEN
CASE
      WHEN (datepart(dd, getdate()))<22 THEN dateadd(month,-2,dateadd(day, 21-datepart(dd,getdate()),getdate()))
      WHEN (datepart(dd, getdate()))>21 THEN dateadd(month,-1,dateadd(day, 21-datepart(dd,getdate()),getdate()))
END
AND
CASE
      WHEN (datepart(dd, getdate()))<22 THEN dateadd(month,-1,dateadd(day, 20-datepart(dd,getdate()),getdate()))
      WHEN (datepart(dd, getdate()))>21 THEN dateadd(month,0,dateadd(day, 20-datepart(dd,getdate()),getdate()))
END
0
 
charlietouAuthor Commented:
Thanks HuyBD,

I understand a little better now how case works.  I like how much cleaner it looks without the convert, too!

acPerkins,  I tried yours and it worked as well, so thanks also.


Charlie T.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now