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
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:ewMNyExBGHA.1288@.TK2MSFTNGP09.phx.gbl...
> 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...
>|||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 crea
te
an interfact to the cal table and make adjustments.
TIA
JeffP....
"Gary Gibbs" <ggibbs@.aahs.org> wrote in message
news:1135271277.811769.38450@.g49g2000cwa.googlegroups.com...
> Oops - don't forget the END to the Case statement ;-)
>|||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
William Stacey [MVP]
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:%23bXcyTyBGHA.3984@.TK2MSFTNGP14.phx.gbl...
> 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....
> "Gary Gibbs" <ggibbs@.aahs.org> wrote in message
> news:1135271277.811769.38450@.g49g2000cwa.googlegroups.com...
>|||Thanks to all who posted...
This is nearly perfect... All the links were helpful as I have other client
s
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)+2
1 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....
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:ud17lO0BGHA.3600@.TK2MSFTNGP10.phx.gbl...
> Here is another way. Probably a bit faster then using table lookups if yo
u
> 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
> --
> William Stacey [MVP]
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:%23bXcyTyBGHA.3984@.TK2MSFTNGP14.phx.gbl...
>
