Calculating Quarters for a Forcasting Report

I am writting a query for a forcasting report and I am having a problem
figuring out how to calculate the amount of quarters that a certain
contract spans. I need to calculate the total quarters and then divide
the total contract by that quarter total to get the a single quarter
value. Then calculate the prior total and future total for the quarters
which do not fall within the fiscal year. Here's an example:
I have a contract that is worth $12,000
It's start date is 11.1.2005
It's end date is 8.31.2007
First I need find out how many quarters are in the time span from
11.1.2005 to
07.31.2007
Then I have to calculate the pre forcasted year totals, the forcasted
totals, and the post forcasted totals
(There are 4 quarters in the forecasted year + 1 prior quarter + 3
future quarters)
So, in the sample I provided there are 8 quarters that fall within the
data range.
Each quarters value would be 12,000/8 or $1500. So my report should
look something like this.
Prior Q1 Q2 Q3 Q4 Future
---
$1500 $1500 $1500 $1500 $1500 $4500
Any ideas? Thanks in advance for any help on this matter.
"Claude" wrote:

> I am writting a query for a forcasting report and I am having a problem
> figuring out how to calculate the amount of quarters that a certain
> contract spans. I need to calculate the total quarters and then divide
> the total contract by that quarter total to get the a single quarter
> value. Then calculate the prior total and future total for the quarters
> which do not fall within the fiscal year. Here's an example:
> I have a contract that is worth $12,000
> It's start date is 11.1.2005
> It's end date is 8.31.2007
> First I need find out how many quarters are in the time span from
> 11.1.2005 to
> 07.31.2007
> Then I have to calculate the pre forcasted year totals, the forcasted
> totals, and the post forcasted totals
> (There are 4 quarters in the forecasted year + 1 prior quarter + 3
> future quarters)
> So, in the sample I provided there are 8 quarters that fall within the
> data range.
> Each quarters value would be 12,000/8 or $1500. So my report should
> look something like this.
>
> Prior Q1 Q2 Q3 Q4 Future
> ---
> $1500 $1500 $1500 $1500 $1500 $4500
> Any ideas? Thanks in advance for any help on this matter.
>
Build calendar type table that houses Begin_Qtr_Date, End_Qtr_Date, YYYY_Q
i.e. 19800101 , 19800331 , 1980-1
19800401 , 19800630 , 1980-2
from earlier (make sure you go back far enough to cover all past contracts),
then estimate length of system life, then add 20-30 years?.(and for good
measure, write s_proc to notify dba when calendar is about to run out - or
have s_proc insert data 10 more years every 10 years ?).
Count rows in the table that have dates that fall into qtrs (as long as you
do not have to calculate partial qtrs ?) qtrly_amt = contract_amt/qtr_count.
display headers can be as easy as 1980-1 , 1980-2, 1980-3 etc or repplace
headers comparing dates again for past and future. calc prior and future by
qtrly_amt * qtrs.
Hope this helps.|||I was actually looking for a way to do this without a calendar
table...but thanks for your help. This is a report to be used in
Microsoft CRM 3.0|||If you have 2005, you can do a bunch of quarter stuff with my TDateRange
UDT. I don't fully understand your requirements, but here is some sample:
-- This is assuming your first fiscal month is 11. Change as needed.
declare @.dr TDateRange
set @.dr = '11/1/2005-07/31/2007'
select @.dr.NumberOfQuarters(11) as NumQuarters, @.dr.QuarterNumber(8)
set @.dr = TDateRange::GetQuarterByDate('11/1/2005', 11)
declare @.end datetime
set @.end = '7/31/2007'
while 1 = 1
begin
select @.dr.ToString()
if ( @.dr.EndDate >= @.end )
break
set @.dr = TDateRange::GetQuarterByDate(@.dr.AddMonths(3).StartDate, 11)
end
--Output
7 2
11/1/2005 12:00:00 AM - 1/31/2006 11:59:59 PM
2/1/2006 12:00:00 AM - 4/30/2006 11:59:59 PM
5/1/2006 12:00:00 AM - 7/31/2006 11:59:59 PM
8/1/2006 12:00:00 AM - 10/31/2006 11:59:59 PM
11/1/2006 12:00:00 AM - 1/31/2007 11:59:59 PM
Here is the lib url:
http://channel9.msdn.com/ShowPost.aspx?PostID=147390
--
William Stacey [MVP]
"Claude" <cg1970_1@.yahoo.com> wrote in message
news:1138146409.222812.312890@.g44g2000cwa.googlegroups.com...
|I was actually looking for a way to do this without a calendar
| table...but thanks for your help. This is a report to be used in
| Microsoft CRM 3.0
||||
"Claude" wrote:

> I was actually looking for a way to do this without a calendar
> table...but thanks for your help. This is a report to be used in
> Microsoft CRM 3.0
>
These calcs should be done outside of sql, but here you go. hopefully this
makes sense.
declare @.stdate datetime
declare @.eddate datetime
set @.stdate = '01/01/05 12:05 AM'
set @.eddate = '10/05/06 13:04 PM'
declare @.totalmonths int
set @.totalmonths = datediff(mm,@.stdate,@.eddate)
select @.totalmonths
declare @.stmonth int
declare @.edmonth int
set @.stmonth = month(@.stdate)
set @.edmonth = month(@.eddate)
declare @.stmonthvar int
declare @.edmonthvar int
set @.stmonthvar = CASE WHEN (@.stmonth in (1,4,7,10)) THEN '2'
WHEN (@.stmonth in (2,5,8,11)) THEN '1'
ELSE '0'
END
set @.edmonthvar = CASE WHEN (@.edmonth in (3,6,9,12)) THEN '2'
WHEN (@.edmonth in (2,5,8,11)) THEN '1'
ELSE '0'
END
set @.totalmonths = (@.totalmonths - @.stmonthvar - @.edmonthvar)
declare @.totalqs int
set @.totalqs = ((@.totalmonths/3)+2)
select @.totalqs|||>> was actually looking for a way to do this without a calendar table.<<
Why? SQL is based on tables, not procedural code.