Re: SQL/PL-SQL Question

Dave Mausner (dma956_at_lulu.acns.nwu.edu) wrote:
: In article <3tuhjo$dpc_at_knot.queensu.ca>,
: Frampton Steve R <frampton_at_admin.flarc.edu.on.ca> wrote:
: >
: >Suppose I am given a start date and an end date of an employee absence.
: >
: >For example:
: >
: > Start: 950101 End: 950331
: >
: > [...]
: This is hard to solve relationally because SQL wants to join tables on
: key matches and because you have specified ranges, the key values you want
: are often implied, within each range. a relational restatement would be
: to have a table of this year's dates (365 rows) with each day's absence
: credit rating (0 or 1); and your employee data would be entered daily
: one date per row, instead of with ranges. Then you can sum the absence
: credit values by joining the employee absence date table, using date as
: the join key. Each day of absence is worth either 0 or 1, hence the sums
: grouped by employee would be your solution.
: --
: Dave

--
I agree with Dave that it is difficult but not impossible.  A cursor that
detects the intersection of the table dates could be written then all you need
to do is subtract those dates that intersect from the total date range.

The result would be an array of begin and end dates that were "outside" the
apporved absences. 

If you can change the data model to what Dave suggests you would be better off, 
but if you can't the solution should still be workable.
---
Ken Niedermeyer			| The views expressed above do not represent
Computing Systems Analyst	| those of my employer.... (but when do they?)
                                | 
                                | NOTICE:
Internet:                       | Lack of planning on your part
kan9691_at_eeidf002.ca.boeing.com  | does not constitute an emergency on my part.