Date arithmetic with a twist...

SQL Wizards:

I need help on date arithmetic, with a funny twist.

In table RESHOSP we enter data for all the hospital rotations of all our residents. The residents have a 7 day per week work week. Even their vacation and sick time is entered into this table. Therefore, each resident
*should* have data that accounts for *every* day in a year.

This data is entered using a span of dates. For example:

Name         SS#       Site     Start      End
SMITH, JOE   123456789 MFH       01-JAN-91   30-JUN-91
                       MFH     * 02-JUL-91   27-OCT-91
                       VAMC      28-OCT-91   10-NOV-91
                       MFH     * 12-NOV-91   31-DEC-91

*Data is missing for 11-NOV-91 and 02-JUL-91. I need a query that will find
this, and any other missing date(s) during the year 1991.

Since the data is entered using timespans, as opposed to an entry for each specific day, I have been unable to figure out how to compare these timespans with 01-JAN-91 through 31-DEC-91, in order to determine missing dates.

Any SQL Wizards out there with suggestions?

Thanks!

• Peggy - oispeggy_at_ubvms.cc.buffalo.edu