Determine shift from date/time stamp?

[Guest]

Hello all,

I need to run an automated query based on shift. There is nothing in the
table showing shift, so I will have to use the time stamp. The query will
also have a date range selection.

What is the best way to determine what shift it is based on the date and
time stamp? Would it be best to have a table setup with the Shift, Start
Time, End Time, and Day then compare the timestamp to this table?

Example:

Date/time stamp. How could I find what shift this was associated with?
07/04/06 06:04:01

If I have a table with the shift, Start Time, End Time, Day how would I
determine if the previous time stamp falls within its range since I have 2
field, one with start and one with end?

Table
Shift Start Time End Time Day
A 06:00 17:59 Monday
A 06:00 17:59 Tuesday


Would it be better to have a table like this and just use the hour and day
part of time?

Shift time Day
A 06 Monday
A 07 Monday
A 08 Monday
A 09 Monday
A 10 Monday
A 11 Monday
A 12 Monday
A 13 Monday
A 14 Monday
A 15 Monday

Or, is there a better way?

Thanks!

 

[Stefan Hoffmann]

hi Mark,

Date/time stamp. How could I find what shift this was associated with?
07/04/06 06:04:01

Using Time(TimeStamp) as a symbolic function for extracting the time:

Using explicit ranges:

Table
Shift Start Time End Time Day
A 06:00 17:59 Monday
A 06:00 17:59 Tuesday

SELECT *
FROM Table
WHERE Time(TimeStamp) BETWEEN StartTime AND EndTime

Pro: Ranges defined in your table.
Con: Checks needed to avoid overlapping ranges.

Using implicit ranges:

Would it be better to have a table like this and just use the hour and day
part of time?
Shift time Day
A 06 Monday
A 18 Monday

SELECT *
FROM Table
WHERE Time = (
SELECT Max(Time)
FROM Table
WHERE Time > Time(TimeStamp)
)

Pro: No overlapping ranges possible..
Con: Ranges must be extracted manually (SQL), if needed.


mfG
--> stefan <--

 

[Douglas J. Steele]

It's the TimeValue() function you want, not just Time().

 

[Stefan Hoffmann]

hi,

Using implicit ranges:
SELECT *
FROM Table
WHERE Time = (
SELECT Max(Time)
FROM Table
WHERE Time > Time(TimeStamp)
)

Must be:

SELECT *
FROM Table
WHERE Time = (
SELECT Max(Time)
FROM Table
WHERE Time < Time(TimeStamp)
)

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Douglas,

It's the TimeValue() function you want, not just Time().

Hence i called Time(TimeStamp) symbolic.

mfG
--> stefan <--

 

[Douglas J. Steele]

hi Douglas,

Hence i called Time(TimeStamp) symbolic.

I don't know the point in putting an incomplete sample.

To me, seeing the following

SELECT *
FROM Table
WHERE Time = (
SELECT Max(Time)
FROM Table
WHERE Time < Time(TimeStamp)
)

implies I should use the Time function on a field or value named TimeStamp.

It should be

SELECT *
FROM Table
WHERE Time = (
SELECT Max(Time)
FROM Table
WHERE Time < TimeValue(TimeStamp)
)

Of course, since Time should never be used as a field name, that should
really be:

SELECT *
FROM Table
WHERE [Time] = (
SELECT Max([Time])
FROM Table
WHERE [Time] < TimeValue(TimeStamp)
)

or, less confusing, something like

SELECT *
FROM MyTable
WHERE MyTime = (
SELECT Max(MyTime)
FROM Table
WHERE MyTime < TimeValue(TimeStamp)
)

 

[Stefan Hoffmann]

hi Douglas,

implies I should use the Time function on a field or value named TimeStamp.

Does

"Using Time(TimeStamp) as a symbolic function for extracting the time"

make no sense for you?

I'd like really to know why you don't understand such a sentence to
improve my english.

Is there a major mistake in grammar, style or vocabulary i don't see?

Is the term "symbolic function" not common to you?


mfG
--> stefan <--

 

[Douglas J. Steele]

My concern was that since there is a Time function which cannot be used in
the manner in which you did, I felt that your response could be confusing to
others.

Yes, "symbolic function" is probably the correct way to refer to it, but I
would suggest having a far more abstract representation of that symbolic
function, rather than something that looks as though it's correct.

 

[Stefan Hoffmann]

hi Douglas,

My concern was that since there is a Time function which cannot be used in
the manner in which you did, I felt that your response could be confusing to
others.

Okay. Thats true.

Yes, "symbolic function" is probably the correct way to refer to it, but I
would suggest having a far more abstract representation of that symbolic
function, rather than something that looks as though it's correct.

I have to admit that using an existing function name as a symbolic name
was not that clever.


mfG
--> stefan <--