dynamic date range

[dandanearl]

I have a crystal report that I will be running using business objects on the 2nd and 17th of every month. I'm using sql commands in the crystal and need some help with a dynamic date query. Here's what I'm looking to do; if current day is between 1st day of month and 15th then capture date range of 16th of previous month to last day of previous month. If current day is between 16th and last day of month then capture date range of 1st to 15th. Here's my attempt; stuck on the CASE statement.
Thanks in advance!

CASE WHEN ?Day between 1 and 15?
THEN
logdate between (DateAdd(month, DateDiff(month, 0,getdate()), 0)) and DateAdd(Day,15,DateAdd(month, DateDiff(month, 0,getdate()), 0)
Else
logdate between DateAdd(Day,16,DateAdd(month, DateDiff(month, 0,getdate())-1, 0)) and DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

 

[gmmastros]

Case When Day(GetDate()) Between 1 and 15



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PeterDimitrov]

My guess is that you are trying to place the logic directly I the WHERE clause (it will help if you post your current command)
instead of doing that declare DateFrom and DateTo variables and set the correct values. Something like this:

Declare @DateFrom datetime, @DateTo datetime

if ?Day between 1 and 15
SELECT @DateFrom = formula ..., @DateTo = formula ...,
else
SELECT @DateFrom = formula ..., , @DateTo = formula ...,


then modify your where clause
WHERE ... logdate between @DateFrom and @DateTo

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.