Sorry this is a terribly vague post, I am rooting around trying to find a way to do this & can't really figure it out at all.

I have data for contracts of work.

ie. Start: January, Finish: December, Description of Work, Budget $XXXX

The above example covers 6 months.

Work is started & then billed monthly. The billing is no problem, as workers bill their time & then generate invoices.

The company would like to schedule/forecast future earnings, as many contracts run forwards to 2014 and beyond.

They would like to generate a forecast earnings report which says what is likely to be earned each month.

For the above example it would be assumed that $XXXX divided by 6 would be earned in each month ... and then added to the monthly portions of earnings from all other contracts to give a reasonably accurate income forecast.

--

I can:

1. Get the CountMonths between the StartDate and EndDate.
2. Budget divided by CountMonths = MonthlyEarnings.
3. At that point I get a bit lost ... I need to JOIN my result-set to a table which lists the Month-Ranges for the forecast, so that data is grouped in correctly.

--

Is anyone else already doing something like this & has a nice neat way to make it work?

Adam,

<< 3. At that point I get a bit lost ... I need to JOIN my result-set to a
table which lists the Month-Ranges for the forecast, so that data is grouped
in correctly. >>

Am I correct in assuming that the table with the month-ranges is separated
out by month ?

In general, the key here is to break everything down by year/month number,
and from there it's a simple matter to match things up by year/month.  The
nice thing is that EDB just so happens to have a year-month interval data
type.

--
Tim Young
Elevate Software
www.elevatesoft.com

Adam


Essentially what you're asking for is to create a matrix of unspecified dimensions using calculations from existing data and then summarise the columns of that data into a vector.

I'm sorry to be the bearer of bad news but regardless of Tim's comment as far as ElevateDB SQL is concerned (I don't know about other flavours) what you want to do is a non-trivial task. Probably not possible.

You may be able to write a script to do it but I believe there are better ways to approach this.

A QAD algorithm would go something as follows:

1. Determine how many months the forecast will run
2. Create a table with as many columns (default 0) as the forecast plus one for the project ID
3. Develop a script that will for each contract:
a) calculate how many months into the forecast that contract will run
b) calculate the average monthly value of the contract
c) loop for the number of months the contract is in the forecast setting the appropriate row/column value
4. Summarise the results in the table
5. Output the information and wait for the user to request changes.

With a fixed number of months in the forecast it sould be possible to come up with a single sql statement but I would hate to have to try and write it, or once written, maintain it!

Roy Lambert [Team Elevate]

ps Now watch John Hay make a monkey out of me

Tim & Roy,

Thanks for wise words. I can see the problem is properly tough.

I can easily do and EXTRACT(Month FROM StartDate) & SUM(Budget) ... but that doesn't spread the budget over all the required dates.

Looking at Roy's suggestion I can see it would be a pig to manage.

... I would still be very interested to hear if others have a better idea on how to do it ... BUT I have had an idea for a simple-ish method to get some "spread" in the forecasting process without going to the granularity of month.

--

If I have a StartMonth, EndMonth and MidMonth (where mid-month - Start + end / 2) I can at least break every project into 3 payments. I can calculate Budget)/3 easily & use this to generate the final cross-tab.

Most contracts are 3 months - 2 years, probably the average is 6 months ... so the above gives a useful level of granularity.

Payments don't come in in a smooth, monthly manner anyway ... so forecasting on the basis of Start, Mid, End is probably as accurate as having monthly figures ...

Lets see whether my co-directors will buy it!

Thanks again, & as I said I'm still curious to hear other's opinions of whether there is a more black-magic SQL way of approaching this problem.

Adam

Adam


The fundamental question is why you're restricting yourself to SQL?

Roy Lambert

Adam

> Thanks again, & as I said I'm still curious to hear other's opinions of whether there is a more black-magic SQL way of
approaching this problem.

I don't know about black magic but one approach would be to use a "numbers" table.  Create a table number with one field
n starting at 0 with the maximum number of months you would want to query (eg 60 rows for 5 years) like
0
1
2
....
60

Assuming that you want the number of months to be all months whch are spanned by the contract eg 31/12/2011 to
01/01/2012 is 2 months you could add a computed column for nummonths like

((extract(year from enddate)-extract(year from startdate))*12)+
extract(month from enddate)-extract(month from startdate)+1

The query for forecast income for say 01/01/2011 to 01/06/2014 would then be

select date'2011-01-01'+cast(n as interval month) as mnth,sum(contracts.value/contracts.nummonths) from number
left outer join contracts on
contracts.startdate < date'2011-01-01'+cast(n+1 as interval month) and
contracts.enddate >= date'2011-01-01'+cast(n as interval month)
where n between 0 and extract(year from date'2014-06-01')*12+extract(month from date'2014-06-01')-
extract(year from date'2011-01-01')*12+extract(month from date'2011-01-01')
group by n
order by n

Apologies for the formatting (I'm just being lazy)

John

John


Forget the formatting - that makes my brain hurt - big time. I just can't get my head round it.

Roy Lambert

Roy

> Forget the formatting - that makes my brain hurt - big time. I just can't get my head round it.



Are you interested in an explanation?

John

John

>> Forget the formatting - that makes my brain hurt - big time. I just can't get my head round it.
>
>
>
>Are you interested in an explanation?

Most assuredly

Roy Lambert

ps - are you on Skype - I have a new toy - Asus Eee Pad and I've loaded Skype

Roy

> ps - are you on Skype - I have a new toy - Asus Eee Pad and I've loaded Skype


I did register ages ago but I have never actually used it.  I will have to try and find the details.

As for the explanation it'sprobably easiest to start with day ranges...

A file of integers is very useful whenever you want to have a result set with a series of contiguous dates (days, months
or years).

If I have a table with one field n with the numbers 0 to 365 in it  I can easily generate a result set with every day
for 1 year viz

SELECT date'2011-01-01'+CAST(n AS INTERVAL DAY) FROM Numbers ORDER BY n

I can then join this result set to any other data which has dates in it to count/sum values by day.  For example if I
have a table of bookings with an arrival and departure date and I want to know how many guests I have on each night
(including 0 if there are none) I can do a query like

SELECT date'2011-01-01'+CAST(n AS INTERVAL DAY) AS BookingDate,SUM(Bookings.Guests) AS Guests, Count(*) AS Bookings FROM
Numbers
LEFT OUTER JOIN Bookings ON
Bookings.Arrival <= date'2011-01-01'+CAST(n AS INTERVAL DAY) AND
Bookings.Depature > date'2011-01-01'+CAST(n AS INTERVAL DAY)
/* you can add a where clause here eg where n < 29 for 4 weeks */
GROUP BY n
ORDER BY n

It works because you are including each booking in the result set as many times as its dates are in the calendar
generated by the first query.  As an example a booking with an arrival date of 2001-01-02 and a departure date of
2001-01-04 will not be included for n=0 (bookings.arrival is not <= 2001-01-01), will be included for n=2 and n=3 as
bookings.arrival is <= to 2001-01-02 and 2001-01-03 and bookings.departure is > 2001-01-02 and 2001-01-03) and will not
be included in any other value of n because bookings.departure is not  > 2001-01-04.

The months query is a bit more convoluted but the principle is the same.  ~The computed column bit is just to get the
number of months in any contract

(EXTRACT(YEAR FROM enddate)-EXTRACT(YEAR FROM startdate))*12)+
EXTRACT(MONTH FROM enddate)-EXTRACT(MONTH FROM startdate)+1

To get a list of months (well first of the month in fact)

SELECT DATE'2011-01-01'+CAST(n AS INTERVAL MONTH) AS MNTH FROM Numbers

The query for forecast income for say 01/01/2011 to 01/06/2014 would then be

SELECT DATE'2011-01-01'+CAST(n AS INTERVAL MONTH) AS MNTH,SUM(contracts.value/contracts.nummonths) FROM Number
LEFTOUTER JOIN Contracts ON
contracts.startdate < DATE'2011-01-01'+CASTt(n+1 AS INTERVAL MONTH) AND
contracts.enddate >= DATE'2011-01-01'+CAST(n AS INTERVAL MONTH)
/* The where clause could be just n between 0 and 35 or whatever - the actual clause is the same as the computed column
ie get the number of months between 2 dates */
WHERE n BETWEEN 0 AND EXTRACT(YEAR FROM DATE'2014-06-01')*12+EXTRACT(MONTH FROM DATE'2014-06-01')-
EXTRACT(YEAR FROM DATE'2011-01-01')*12+EXTRACT(MONTH FROM DATE'2011-01-01')
GROUP BY n
ORDER BY n

It is only a slight change to the "days" query  n that we are now comparing the startdate and enddate of the contracts
against the first day of each month and the day before the first day of the next month (ie last day of this month).

Hope that is as clear as mud!

John