RE: Queries inside UDF

[sguazt sguazt]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]