Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

2 Answers
2

You want another table, in almost identical format to the former, where each row in the original table is duplicated many times in the new table, as many times as there are days between start_date and end_date.

Here's a way to do so:

First, create a table with all possible dates (actually you can use an integer tally table as described above, but I will simplify for purpose of this demonstration):

CREATE TABLE all_dates(dt DATETIME PRIMARY KEY);

INSERT into the above table all dates possible in your system (e.g. 1970-01-01 and up to 2199-12-31, whatever). I mentioned my assumption is that this is a one time job, so this table can be later dropped.

This assumes you have a tally table tally with the numbers in a column id. If you don't have one, getchew you one.

N.B.: I have not tested this. I'll be happy to correct any syntax errors discovered, but I have a tough time reading the tiny type on the SSH client on my phone :-) Meanwhile, standard disclaimers of warranty are implied.

I have assumed that staff_leave.end_date represents the last day of leave rather than the first day back to work. I have seen it used both ways in the wild. If staff_leave.end_date is actually a day of work rather than a day on leave, you will want to remove the + 1 from the WHERE clause, as other_table is only storing actual days on leave, as I read your question..

I'm sure there is a better way to deal with this than using a cursor, but I couldn't think of a proper join off the top of my head. Assuming this is a one-time operation, it shouldn't be all that costly to use a cursor. You know, just this once. :)