Thanks for answering!
Your solution would be right if I can modify the database (and I have no control on software that populate tables).
Unfortunately I can do only queries on that db; so the due date has to be recalculated every time I want to perform the main report (that use the ending date).
An alternative way is to created a snapshot of main db with the added fields, but the report may contains not so updated information (e.g. if the snapshot is synch one time at day in theory the report may display info older up to a day). This violates the requirements of my boss who wants a very updated data ... :'-|
So the only possible ways are:
* do that via high-level language, like C, Perl, Java; I've done this but is very slow ... especially the client running the code is remote (with respect to DB server) ... so in addition to the load generated by the number of queries there's the load of network communication: (<Send Queries> + <Receive Results>) * N (where N >> 1 in general);
* do that via SQL+UDF; the advantage of this solution is the load of computation is on DB server ... the load due to the network communication a very minimal: <Send Query One Time> + <Receive Result>
When I tried UDF I'm said .... WOW this could be the solution to my problems!!!!
Unfortunaly, I found out there's no way (at least for me) to access to current DBMS thread or connection; i.e.:
SELECT foobar( ... ) FROM ...;
I believed from foobar FUNCTION there would have been a way (through parameters passed by MySQL, like UDF_INIT* initid) to access to current DBMS connection object.
I this is true ... what a pity! .... I hope MySQL staff will think to extend UDF to include this feature unless there're hidden trickly I don't see.
-- Marco
From: [EMAIL PROTECTED] To: "sguazt sguazt" <[EMAIL PROTECTED]> CC: mysql@lists.mysql.com,[EMAIL PROTECTED] Subject: RE: Queries inside UDF Date: Wed, 16 Mar 2005 15:20:09 -0500
Marco,
I think I understand why you might want the end date of your projects to be dynamically calculated (assuming that's why you want this calculation to be a UDF?). You would be able to change a starting date, the project's duration, or add or delete a holiday and your ending dates would automatically adjust. However, as you have found out, it is not a simple calculation to perform.
Based on the starting date and some initial duration, any number of non-working days (which includes both holidays and weekends) may occur within that time span. So you adjust your ending date which may now also include more non-working days, so you repeat the process until you achieve the duration you wanted and have accurately accounted for all non-working days since the startdate of the project. I think your pseudo code fits that model of calculating the finishdate rather well.
I agree with your assessment to do the finish date calculation in code, not SQL. However I disagree with the design choice to make the finish date a dynamic value (if that's what you did, sorry if you didn't). You should only need to compute the finish date's value under the following conditions: a) initial record creation b) if you change the start date c) if you change the duration d) if a non-workday was added to or removed from the calendar and that day fell within the computed time span of the project.
It makes better sense to me to detect one of those 4 conditions and UPDATE a field that holds the ending date by using your algorithm while performing one of those actions. That way a query like
SELECT name, startdate, enddate FROM projects WHERE startdate <= '2005-07-01' AND enddate > '2005-07-01';
doesn't need to compute the value for enddate twice (once for the result and once for the WHERE test) for every project just to detect any projects that will be active on July 1, 2005. In fact, that query may even be able to use an index if you stored the value in a field while if you kept it only as a calculated value, that would not be possible.
So... have we answered your original question or did we just go WAAY off track?
Shawn Green Database Administrator Unimin Corporation - Spruce Pine
"sguazt sguazt" <[EMAIL PROTECTED]> wrote on 03/16/2005 01:25:09 PM:
> > > >From: [EMAIL PROTECTED] > >To: "sguazt sguazt" <[EMAIL PROTECTED]> > >CC: mysql@lists.mysql.com,[EMAIL PROTECTED] > >Subject: RE: Queries inside UDF > >Date: Wed, 16 Mar 2005 12:37:59 -0500 > > > > > >I can almost follow the logic of your pseudocode. Can you explain "what" > >it is you are trying to compute? It seems as though you are looking for > >(or computing) the ending value to some sort of time span but I just can't > >quite understand how you are getting there. I can see that you are using > >some kind of varying-width (you change your increment value), sliding time > >window (you move your start and stop times) but I just don't understand > >why you stop when you DO get a result (the else clause of (rs==null)) when > >you needed results for the previous loops (I think you meant to stop when > >you didn't get a result)... And the significance of the data you finally > >found escapes me... > > yes you got it ... I wrote an erroneous stopping condition: > > The real stopping condition is (as you told me): > > if ( ts != null ) { > } > else { > break; // STOP CONDITION > } > > Well I'm trying to explain what I want to do ... > I have a starting date of an event (see variable start) and it's virtual
> duration (see variable duration); the word "virtual" will be clearer later. > For instance you have a table of research projects with the starting date > and their related duration. > In the simple vanilla world, the due date (see variable stop) would be: > start + duration (consider start as a UNIX timestamp and duration expressed > in seconds). > > But in real world you've to take care about a calendar. Each project has a > project manager associated. > Each project manager is assigned a calendar because can follow different
> holydays (in fact project manager for project1 could be American; project > manager for project2 could be European; and so on ...) > > So for each project you've to calculate the real due date according to the > calendar associated to the project manager. > The calendar I have is a event-based calendar (that is, the calendar has
> associated a series of calendar event representing the holydays). > > So the pseudo-code: > > int calculateDueDate( projectManager, wTsStart, duration ) > { > wTsStop = 0; // Working Day stop timestamp > increment = 0; // increment of duration > > // calculate the Working Day stop timestamp according to the > patching duration > while ( true ) do > nwTsStart = 0; // Non-Working Day start timestamp > nwTsStop = 0; // Non-Working Day stop timestamp > > wTsStop = wTsStart + (duration increment); > // retrieve the intersecting interval for the interval > [wTsStart, wTsStop] > rs = doQuery( > "SELECT CE.tsstart,CE.tsstop " > + "FROM calendar C INNER JOIN calendarevent CE ON > C.id=CE.calendar " > + "WHERE C.owner=" + projectManager + " AND ((" + > wTsStart + "<=CE.tsstart AND " + wTsStop + ">CE.tsstart) OR (" + wTsStart + > ">CE.tsstart AND " + wTsStart + "<CE.tsstop)) " > + "ORDER BY CE.tsstart, CE.tsstop" > ); > if ( rs != null ) then > delta = 0; // offset between the start of intervals > nwTsStart = rs[0]['tsstart']; > nwTsStop = rs[0]['tsstop']; > if ( wTsStart < nwTsStart ) then > delta += nwTsStart - wTsStart; > endif > increment += delta; > wTsStart = nwTsStop; > else > break; > endif > endwhile > > return wTsStop; > } > > the returned value is just the real due date. > The above code have to be repeated for each project manager: > > SELECT ...., calculateDueDate( P.manager, P.startDate, P.duration ) FROM
> project P INNER JOIN ...; > > bye!! > > -- Marco > > > > >Maybe if I understood the nature of your data better I wouldn't be so > >confused. And, if you would be so kind, can you explain why you are > >scrolling through the data with a shifting time window to reach some kind > >of non-record (I assume)? Is there no other way to reach the same > >information? Can you not just use the last record and work from there? > > > >It's hard to get help for a complex data issue like yours unless you give > >us enough information so that we understand your both your data and your > >needs. Any one of us may see a path to your solution that you haven't > >thought of but we can't do that unless we understand both where you are > >starting from (your base data structures) and where you need to be (the > >data you require). It's going to be very hard to confuse all of us. The > >more information you provide, the more complete the help will be. > > > >Shawn Green > >Database Administrator > >Unimin Corporation - Spruce Pine > > > > > > > > > > > > > > > > _________________________________________________________________ > Scopri il nuovo MSN Htomail - 10MB di allegati > http://www.msn.it/hotmail/minisite_10 >
_________________________________________________________________ Scarica gratuitamente MSN Toolbar! http://toolbar.msn.it/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
