![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 14 total |
![]() |
Wed, Nov 30 2011 2:15 PM | Permanent Link |
Adam Brett Orixa Systems | 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? |
Wed, Nov 30 2011 5:20 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Thu, Dec 1 2011 3:54 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 ![]() |
Thu, Dec 1 2011 4:05 AM | Permanent Link |
Adam Brett Orixa Systems | 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 |
Thu, Dec 1 2011 4:29 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Adam
The fundamental question is why you're restricting yourself to SQL? Roy Lambert |
Thu, Dec 1 2011 10:41 AM | Permanent Link |
John Hay | 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 |
Thu, Dec 1 2011 11:12 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | John
Forget the formatting - that makes my brain hurt - big time. I just can't get my head round it. Roy Lambert |
Thu, Dec 1 2011 11:46 AM | Permanent Link |
John Hay | 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 |
Thu, Dec 1 2011 12:09 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Thu, Dec 1 2011 1:25 PM | Permanent Link |
John Hay | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |