SQL: Sub-intervals which are inside a month
I have a table hirefire. Below there is its simplified structure:
hired date
fired date
firereason smallint
I spent a few hours writing queries for this table to solve a problem, my
problem (simplified) is presented below:
This table describes when an employee starts work and when he goes to a
vacation.
Intervals hired..fired from a row of this table (with hired included and
fired not included into the interval) are called "hire intervals". I
warrant that hire intervals don't override with each other and that
fired>=hired for every row.
I call "fire intervals" all intervals fired1..hired2 where fired1 is the
field fired from a row r1 in this table and hired2 is the field hired from
the next row r2 of this table where rows are sorted by the field hired.
For each such interval it is assigned a "fire reason" (a ref to a primary
ID of the table containing reason to leave the job, such as a vacation,
parental leave, death, etc.) equal to the field firereason from r1.
Let there is given a month (by an SQL DATE variable which contains the
first day of the month).
I need the set of non-empty intersections of fire intervals with the given
month. (That is I need info about these intervals which have at least one
day in common with the given month.)
MySQL+PHP
No comments:
Post a Comment