Hi,
It is easier to find make the SQL statement that find overlapping that
to make the charts.
There is no overlapping at all between ( s1, e1) and (s2, e2) , note I
used s for starting and e for ending, if:
s1>e2 OR e1<s2
There is overlapping in the negation:
NOT ( s1 > e2 OR e1 < s2 )
which is also
s1 <= e2 AND e1>= s2
note that the equality is either a boundary effect, either a full day
overlapping if e2 is a date and the ending data itself is inclusive, rather
that "up to, exclusively. In other words, I leave to you the care to know if
it is >= or just >, and <= or just <, in your situation.
So, we can get intervals with overlapping periods (in part or in full):
SELECT a.ressourceID, a.jobID, a.starting, a.ending, b.JobID, b.starting,
b.ending
FROM myTable As a INNER JOIN my Table As b
ON a.ressourceID = b.ressourceID
AND a.JobID <> b.JobID
AND a.starting <= b.endint AND a.ending >= b.starting
ORDER BY a.ressourceID, a.starting
That does not solve your problem to "show" when resources are in use. To
do that with versatility, you would the possibility to "draw" lines (or
rectangles) on a control. Unfortunately, there is nothing in Access, right
out of the box, that allows you to do that easily. In fact, you may find an
ImageClass that can help you starting in that direction. Since dates are, in
fact, number of days (since the 30th December 1899), you can place a time
scale horizontally, and, vertically, have your resources, a, b, c, on
different "lines". You then draw a line segment accordingly to the starting
date and ending date. Once you have drawn all the lines, you know where the
resources you require are available, at the same time.
Maybe even that is easier to do in SQL?
Assume a table DateOpenToWork with one field, dWork, a (vertical) list of
dates open to work. Assume a table RequiredRessources, one field,
RessourceID, a vertical list of the resource we are looking for
availability. We also have, as previously, a table Jobs, with the JobID, the
ressourceID, the starting and ending time fields (on record per job, per
resource).
Clearly,
SELECT dWork
FROM DateOpenToWork INNER JOIN (Jobs INNER JOIN RequiredRessources ON
Jobs.RessourceID=RequiredRessources) ON DateOpenToWork.dWork BETWEEN
(Jobs.Starting AND Jobs.Ending)
supply the dWork "in use". The days "not in use", for witch some resource
is free, would be:
SELECT dWord
FROM DateOpenToWork LEFT JOIN (Jobs INNER JOIN RequiredRessources ON
Jobs.RessourceID=RequiredRessources) ON DateOpenToWork.dWork BETWEEN
(Jobs.Starting AND Jobs.Ending)
WHERE Jobs.JobsID Is Null
Having the list of days where all the resources are all free, it is just a
matter to find N consecutive days. Up to now, we have assumed that the table
DateOpenToWork has only one field, now, assume there is a second field, the
"rank" of the day, ie, due to weekend, and holidays, the days are not
necessary consecutive, so, we have something like:
dWork, rank
January 2 1
January 5 2
January 6 3
....
assuming the second of January is not an holiday, and that the third and
fourth of Jan, that year, were a Saturday and an Sunday. The "rank" is just
a convenient "continuous number". Back to our query, add that field in the
selected list, and remove dWork:
SELECT rank
FROM DateOpenToWork LEFT JOIN (Jobs INNER JOIN RequiredRessources ON
Jobs.RessourceID=RequiredRessources) ON DateOpenToWork.dWork BETWEEN
(Jobs.Starting AND Jobs.Ending)
WHERE Jobs.JobsID Is Null
Save that query, say under the name Q1.
To get the sequences of N consecutive days, in Q1, it is then a matter to
do:
SELECT MIN( a.rank)
FROM q1 As a
GROUP BY a.rank-(SELECT COUNT(*) FROM q1 As b WHERE b.rank<= a.rank)
HAVING COUNT(*) >= [ N ]
It may be hard to figure out, logically, so let me supply a numerical
example.
Assume:
a.rank
1
2
3
5
6
8
9
10
11
12
22
then
a.rank, a.rank-(SELECT COUNT(*) FROM q1 As b WHERE b.rank<= a.rank)
1 1 - 1 = 0
2 2 - 2 = 0
3 3 - 3 = 0
5 5 - 4 = 1
6 6 - 5 = 1
8 8 - 6 = 2
9 9 - 7 = 2
10 10 - 8 = 2
11 11 - 9 = 2
12 12 - 10 = 2
22 22 - 11 = 11
If we GROUP BY the computed value, and count the number of lines in each
group, we got:
Group COUNT(*)
0 3 records
1 2
2 5
11 1
so, in clear, the group supplying the value 2 has 5 "consecutive" values
in the original "data" we grouped on.
SELECT MIN( a.rank) as possibleStart
FROM q1 As a
GROUP BY a.rank-(SELECT COUNT(*) FROM q1 As b WHERE b.rank<= a.rank)
HAVING COUNT(*) >= [ N ]
supply then the possible a.rank values where a start is possible. Save that
as Q2.
SELECT dwork
FROM q2 INNER JOIN DateOpenToWork ON q2.possibleStart = DateOpenToWork.rank
will supply the list of those possible starts.
Now, if you look only at the SQL statement, rather than to the lengthily
explanations I wrote, you have to admit that this solution implies REALLY
very few lines of "code", and is, in conclusion, quite simple!
Note that I didn't run-checked any syntax, I just hope I have no
irrecoverable "typos".
Hoping it may help,
Vanderghast, Access MVP
