[SOLVED] Identify time slots used to perform a piece of work

[yetirider]

Hi

I have a number of time slots that cover a 24Hr period.
I need to identify which time slots a piece of work occupies and how much slot time it would use.

So the slots are:-
Name Start End SLOT_MINS
Early 00:01 01:59 118
Morning 02:00 06:59 299
Day 07:00 18:59 599
Evening 19:00 21:59 179
Late 22:00 00:00 120

So if the work starts at 01/09/2016 00:01 and ends 02/09/2016 23:00

This would cover all slots so I need to show in columns
Early Morning Day Evening Late
Plus calculate how many minutes of each slot it takes.

I have calculated the start time slot and the end time slot.

I've not managed to identify a method of using the data to identify the slots that may be used in-between and the minutes used.

I would imagine this has been done before but I can't figure this out...

Many Thanks Phil

[plog]

I've never seen code like this, but its a slow week at work and it seemed like an interesting problem, so I made some. Attached is a database with a module to calculate how much of a work span falls within a time slot. Open the query and it shows the example you provided along with one of mine that I used to test various cases.

Few notes:

--You didn't provide table names, so I used my own.

--I don't like your field names, so I used my own.

--You used reserved words for a few field names (Name & End). You shouldn't do that. Field names need to be less than generic than that--prefix them with what they are the Start and End of and what they are the name of.

--You shouldn't store calculated fields, that means SLOT_MINS shouldn't be a field in your table. You can deduce that by other data already in that record.

--In my example a day starts at 00:00 and ends at 23:59. You had a day ending at 00:00, so that would account for any small differences.

--Your SLOT_MINS is incorrect in your example for Name=Day. There are 719 minutes in that time slot, not 599.


Let me know if you find any issues with my query or want any explanations of how I did it. I noted the function so you should be able to walk yourself through what its doing.

[yetirider]

Many Thanks

Will download review the code.

[yetirider]

That worked perfectly thanks, just had to reformat the data and convert the minutes into hh:nn

Many thanks for your help its really appreciated .