Very complicated look-up - shift patterns from a date and time.

  • Thread starter Thread starter Chris Strug
  • Start date Start date
C

Chris Strug

Hi,

I'm having a problem thinking this one through and was wondering if someone
could provide me with some inspiration.

I have a table which includes a user entered date and time, for example
'22-march-2004 08:36'.

From this I would like to be able to determine the shift that the datetime
belongs to. However the shifts can occur over two days (for example, from
18:00 Monday to 05:59 Tuesday) so I can't simply look at the time and deduce
it that way.

Over the course of a week there are 12 possible shifts that a datetime can
occur in.

Could this be structured in a table and obtained through lookups? How would
I structure the table to get the shift?

What about an enumerated datatype? Again, how do I structure it

I'm looking to implement this on an ADP linked to SQL server 2000 so there
are opportunitities there, its just sorting out the logic first.

As I said, I'm quite stuck at this. Any and all suggestions are gratefully
received.

Kind regards

Chris Strug.
 
I would create a table with three columns:

start_time char(5),
end_time char(5)
shift_name char(whatever)

Populate the two 'time' fields with a five digit string containing 'whhnn'
where w is the day of the week, h the hour and n the minute.
For example, your quoted shift 18:00 Monday to 05:59 Tuesday would be :

start_time 21800
end_time 30559
shift_name Monday Night Shift

You can then select the shift from the table as :

SELECT shift_name
FROM lookup_table
WHERE Format$(entered_date, 'whhnn') BETWEEN start_time AND end_time ;
 
John Smith said:
I would create a table with three columns:

start_time char(5),
end_time char(5)
shift_name char(whatever)

Populate the two 'time' fields with a five digit string containing 'whhnn'
where w is the day of the week, h the hour and n the minute.
For example, your quoted shift 18:00 Monday to 05:59 Tuesday would be :

start_time 21800
end_time 30559
shift_name Monday Night Shift

You can then select the shift from the table as :

SELECT shift_name
FROM lookup_table
WHERE Format$(entered_date, 'whhnn') BETWEEN start_time AND end_time ;
Click to expand...

John,

Many thanks for the insightful idea. I'll give it a go and let you know how
I get on!

Chris.
 
Chris said:
I'm having a problem thinking this one through and was wondering if someone
could provide me with some inspiration.

I have a table which includes a user entered date and time, for example
'22-march-2004 08:36'.

From this I would like to be able to determine the shift that the datetime
belongs to. However the shifts can occur over two days (for example, from
18:00 Monday to 05:59 Tuesday) so I can't simply look at the time and deduce
it that way.

Over the course of a week there are 12 possible shifts that a datetime can
occur in.

Could this be structured in a table and obtained through lookups? How would
I structure the table to get the shift?

What about an enumerated datatype? Again, how do I structure it

I'm looking to implement this on an ADP linked to SQL server 2000 so there
are opportunitities there, its just sorting out the logic first.
Click to expand...


Can it be done algorithmically? Maybe something like:

2 * (DatePart("w", dtfield) - 1) - (DatePart("h", dtfield)
= 6 And DatePart("h", dtfield) < 18)
Click to expand...

You probably can also do it through a lookup if you had a
table that contained fields for the weekday and the shift
start/end times, but I don't know what your rules for that
would be.
 
Back
Top