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
>
