I'm looking for a script to do the following?

I need to have a date range for each month of the year from the 22nd of each
month to the 21st of the next.

This doesn't work... as it starts at the first, I need it to start at the 22 of
the previous month.

declare @date datetime
set @date = '1/1/2005'
select case when datepart(mm,@date) = 1 and datepart(dd,@date) between 1 and 21
then 1  else 0 end


TIA

JeffP....

Considered a calendar table to mark fiscal months?
http://www.aspfaq.com/2519





Show quoteHide quote

The calendar table is an excellent solution.  If you do not want to do
that, you could use a Case statement for your solution.  This example
assumes your fiscal year starts on December 22.

select <all other columns>,
case when datepart(day,datefield) <= 21
     then cast(year(datefield) as varchar(4)) + ' ' + right('0' +
cast(month(datefield) as varchar(2)),2)
     when datepart(day,datefield > 21 and month(datefield) = 12 then
cast(year(datefield) + 1 as varchar(4)) + ' 01'
     else cast(year(datefield) as varchar(4)) + ' ' + right('0' +
cast(month(datefield) + 1 as varchar(2)),2)




else date(mm,datefield) + 1 end)
as FiscalMonth

Oops - don't forget the END to the Case statement ;-)

All, Thanks....

Thanks, one the one hand the case stmt works well for a quick & dirty query, but
I'm like'ing the table, that way if anything changes in the rules I can create
an interfact to the cal table and make adjustments.

TIA

JeffP.....

Show quoteHide quote

Here is another way.  Probably a bit faster then using table lookups if you
call it a lot.

declare @mthStart datetime
declare @mthEnd datetime
declare @i int
set @mthStart = '1/22/2005'
set @mthEnd = dateadd(day, 30, @mthStart)
set @i = 1;

while (@i <= 12)
begin
select @mthStart, @mthEnd
set @mthStart = dateadd(month, 1, @mthStart)
set @mthEnd = dateadd(month, 1, @mthEnd)
set @i = @i + 1
end

Show quoteHide quote

Thanks to all who posted....

This is nearly perfect...  All the links were helpful as I have other clients
with different date range issues.

create table #view_paid (datepaid varchar(10) ,polchoice varchar(2))
insert #view_paid
select '01/03/2005' ,10
insert #view_paid
select '01/13/2005' ,10
insert #view_paid
select '01/23/2005' ,10
insert #view_paid
select '01/31/2005' ,10
insert #view_paid
select '02/03/2005' ,10
insert #view_paid
select '02/13/2005' ,10
insert #view_paid
select '02/23/2005' ,10
declare @mthStart datetime ,@mthEnd datetime
set @mthStart = '1/22/2005'
set @mthEnd = dateadd(day, 30, @mthStart)

select datepaid ,polchoice
,dateadd(ms,-3,dateadd(mm,datediff(mm,0,dateadd(mm,-1,datepaid)),0)+22)
Period_Start
,dateadd(mm,datediff(mm,0,datepaid),0)+21 Period_End
,Jan = case when datepaid between dateadd(mm,-1,@mthStart) and
dateadd(mm,-1,@mthEnd) and (cast(PolChoice as varchar(1)) = 1) then 1 else 0 end
,Feb = case when datepaid between dateadd(mm,0,@mthStart) and
dateadd(mm,0,@mthEnd) and (cast(PolChoice as varchar(1)) = 1) then 1 else 0 end
,Mar = case when datepaid between dateadd(mm,1,@mthStart) and
dateadd(mm,1,@mthEnd) and (cast(PolChoice as varchar(1)) = 1) then 1 else 0 end
from #view_paid with(nolock)
where cast(datepaid as datetime) between '01/01/2005' and '03/27/2005'
order by datepaid

select [Year] = datepart(yy,datepaid)
,JanTotals =sum( case when datepaid between dateadd(mm,-1,@mthStart) and
dateadd(mm,-1,@mthEnd) and (cast(PolChoice as varchar(1)) = 1) then 1 else 0
end)
,FebTotals = sum(case when datepaid between dateadd(mm,0,@mthStart) and
dateadd(mm,0,@mthEnd) and (cast(PolChoice as varchar(1)) = 1) then 1 else 0 end)
,MarTotals = sum(case when datepaid between dateadd(mm,1,@mthStart) and
dateadd(mm,1,@mthEnd) and (cast(PolChoice as varchar(1)) = 1) then 1 else 0 end)
from #view_paid with(nolock)
where cast(datepaid as datetime) between '01/01/2005' and '03/27/2005'
group by datepart(yy,datepaid)

drop table #view_paid

JeffP.....

Show quoteHide quote

Popuate field with value from last row
Why query optimizer does not select the index?
Finding the right record
CHARINDEX in CASE Within SELECT Statement
Default Value Calculation