How to compare time in query?

[volcano]

Hello, I have a query problem which has made me headache for a long time..hope you have some ideas, thanks!

I am writing a ASP2.0 plus MS Access application. For the sake of simplicity, I have a table, WORKTIME, which is like below:

UserGroup StartWorkTime EndWorkTime
A 08:00 AM 17:00 PM
B 17:01 PM 01:00 AM
C 01:01 AM 07:59 AM

The datatype of StartWorkTime and EndWorkTime are Date/Time with short time format (no date needed). The period from StartWorkTime and EndWorkTime is the relevant user group's working hours

Now suppose the current time is 00:30 AM, how should I write a SQL statement to query this table so that I can retrieve the user group B?

Thanks so much!

 

[Steve101]

I think that the trick here is to make the lookup table contain 4 rows (based on your sample data), to ensure that no row spans different days; ie.

UserGroup StartWorkTime EndWorkTime
A 08:00 AM 17:00 PM
B 17:01 PM 12:00 PM
B 12:01 AM 01:00 AM
C 01:01 AM 07:59 AM

Note that Group B now repeats itself across two different time spans. You can now simply query the above table to retrieve the UserGroup field where the TimeNow is between the StartWorkTime and the EndWorkTime.

Note that with this technique, you can have as many timespans as you like. The "shift" which goes through midnight will contain the repeated entry.

Hope this helps,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[volcano]

This seems applicable. Let me try this solution. Thanks Steve!

 

[danvlas]

Just a query with some well baked conditions will do the trick:

SELECT UserGroup, StartWorkTime, EndWorkTime
FROM WorkTime
WHERE (((StartWorkTime)>=[YourTime] And (StartWorkTime)>[EndWorkTime]) AND ((EndWorkTime)<[StartWorkTime] And (EndWorkTime)>[YourTime])) OR (((StartWorkTime)<=[YourTime] And (StartWorkTime)<[EndWorkTime]) AND ((EndWorkTime)>=[YourTime] And (EndWorkTime)>[StartWorkTime])) OR (((StartWorkTime)<=[YourTime] And (StartWorkTime)>[TimeOut]) AND ((EndWorkTime)<[YourTime]));

[YourTime] is a parameter. Change it to what you need...


Good luck


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Steve101]

Dan,

The idea behind the extra row of data to ensure that the end time is always > the start time, is that it very much simplifies the query required; for example, a query which would provide the desired result could be:

SELECT UserGroup
FROM WorkTime
WHERE [YourTime] BETWEEN StartWorkTime
AND EndWorkTime

and is conceptually much easier to understand.

Regards,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[danvlas]

&quot;and is conceptually much easier to understand.&quot;

User shouldn't understand the concepts...user only works through the interface provided by the programmer.

The extra row flaws the table normalization...It can cure the immediate flu, but might cause long-term cancer...

In this particular case it's almost OK, (what happens if you count the number of &quot;shifts&quot; an employee had?) but if you have a timesheet - for order details as an example -, you would enter two rows for just one order detail - so you'll have to create a multi-field primary key...

My motto is: never store what you can calculate... maybe not so easy, but it saves you a lot of trouble in the future.


Regards,


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Steve101]

Wow Dan, we will (ie. me and Mr Edward de Bono; hope I dont use his name in vain) just have to disagree on most of that.

Many programs / systems have been radically simplified by simple rationalisation of the data. In this case, we force one row to follow the same rules as the rest of the rows, and by so doing, radically simplify the processing rules.

There is a &quot;penalty&quot; which is the extra row of data, but for me, the benefits outweighs the cost. You are quite right; the user will need to recognise the reason for doing this, but since its probably a none volatile table, maintained by an administrator type, I think that this is perfectly acceptable.

As for normalisation; if you normalise based on the &quot;Group&quot; in this case, then consider the table marginally non normalised; if you base the Primary key on the time ranges, then its perfectly normalised. In this case the name of the table is &quot;WorkTime&quot;, so I think my interpretation is legitimate.

All the best,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[danvlas]

Steve, what can I say? Disagreement is the base of progress... And if everyone had the same opinion, life would really be boring...

I know what you mean and I admit you have a point in your theory. Moreover, I've seen applications made in this way working quite well.
But speaking for myself, I'll stick to my 'religion'...I've been burnt once, won't let that happen again...

I said this particular case was almost OK. The exception is counting the relevant records - would you care to share how this will be done, as it beats me? But not through an intermediate query to group and filter the records first...something simpler to back up the 'application simplification' theory.

The main point with splitting a row (not necessarily for this case, but generally speaking) is creating a multi field primary key (and yes, in this case the table would be normalized), which has a dramatic impact on the length and speed of other queries based on such joins...
Another thing with splitting the row: you have to do it in code -this does not simplify the application-if you want good results. If you let the user do it by hand, you'll be in trouble.

Meanwhile, the SQL statement I provided works nice and smoothly and there is no need to split the rows.




Cheers,



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Steve101]

Dan,

You are both a scholar and a gentleman, and its a pleasure to debate / discuss with you

It would be interesting to see what others have to offer on these alternatives.

Meanwhile, a couple of final points of mine in response to your last post:

You said: &quot;The exception is counting the relevant records - would you care to share how this will be done, as it beats me?&quot;

I say: Where does the user mention anything about counting the relevant records, and what is there about your query which accomodates this requirement, or about my additional record which prevents this:

At any rate, if I was to put together a schematic on this application, I would probably have two tables; one called tblGroup, which just had data on the Group, Name etc - containing three records; another called tblGroupTime, which had four records, but could go on to have more if it was allowable to have non contiguous shifts; probably an overkill; you could argue out of scope, too complex, whatever, but this &quot;comes at the same price&quot;, and with a carefully designed interface, is still simple to understand, cheap to implement.

More to the point, if I was to count the number of shifts that an employee has, well, presumably on the timesheet, on this model, the employee just enters A, B or C, and the times are derived from the Shift definition table (or whatever its called). So I'm not sure I really see the relevance of the question.

If the employee enters start and end times (as on a timesheet), and these dates happen to cross shifts, then we both have the same problem recognising this. In either event, I would write a simple function where the Start, End times were provided as parameters to the function, and the function returned A, B or C, or indeed combinations of these if the operator crossed shifts; Again very simple; the function is of course callable directly from a single simple select, or an aggregate query.

Anyway mate, enough of my ramblings. I could go on for ages, commenting on some of the other things, but its getting late, and its time for my beauty sleep (haha the wife would say).

Be well, and till we meet again,

Regards,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[danvlas]

Beauty sleep (for men)? LOL this is the best one I've heard for a long time...


Can I quote you???

Sweet dreams (perfect databases, nice clients, disciplined users and patient bosses) LOL






Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Steve101]

Yes, feel free to quote me. I'm too ugly to care. Gee this thread has deteriorated rapidly. Better sign off before we're busted,

CU later,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[KornGeek]

[2cents]
Both of the ideas mentioned have merit, and I think both are acceptable approaches. Steve's approach appears to be easier to code and maintain, while Daniel's has minimal impact on the data. I myself would probably use Daniel's method, but then later regret it when I'm trying to figure out where to put my missing &quot&quot; character. That's just my style though.

In Steve's approach, he uses 12:00 PM as midnight, but 12:00 AM is actually midnight. A minor issue, but still something to look out for. I would find it best (from a UI perspective) to have a form where the user enters the start and end times, and then the database splits it into two records if necessary. The danger here is when a user goes back and edits the times, you need to redetermine if it should be one or two records.

In Daniel's approach, I noticed that some of the query criteria were repeated (such as (StartWorkTime >[EndWorkTime]) and ([EndWorkTime] < StartWorkTime)). I'm not sure if these were intentional to make it more readable or not. They can be removed which would probably make a small improvement in speed (not sure that it matters), but it would then impact the readability of the code. Also, this approach seems to neglect the circumstance where either the [Your Time] parameter equals StartWorkTime or EndWorkTime or when StartWorkTime equals EndWorkTime.

Clearly, both approaches have their advantages and disadvantages. I personally would probably use Daniel's approach only because of the way my brain works.

Thank you both for your insight.
[/2cents]

 

[Steve101]

CornGeek,

Thanks for the input; as you say, there is more than one way to skin a cat, and its refreshing when we can agree on this.

I am intrigued by your statement &quot;In Steve's approach, he uses 12:00 PM as midnight, but 12:00 AM is actually midnight&quot;. You may be right here (I've heard this argument somewhere before). In my little mind, 11:59pm is one minute before midnight, so add one minute and you get 12:00pm. I guess it all depends on where you &quot;flip&quot; the am/pm bit. Is midnight the start of a new day, or the end of an old day, or both; Does it really matter? More importantly, does it reflect the way in which we develop systems? Is there life after birth?

Onwards and Regards,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[KornGeek]

Steve,
The only reason I see this as an issue is because of how Access will compare the times. I may be mistaken (or it may depend on regional settings), but I believe it uses 12:00 pm for noon and 12:00 am for midnight. I agree that this is less than intuitive. If it were up to me, 12:00 would have neither am or pm. We would have 12:00 Noon and 12:00 Midnight. Unfortunately, the universe does not yet bow to my wishes.

 

[BoffoSteve]

Volcano, et al: I'm curious about those one-minute gaps in the shift times. For sake of simplicity, I'd skip the table and create a function for the purpose (assuming you can take advantage of the Access environment):

Public Function strUserGroup(dtmClockTime As Date) As String
'Call with time of day returns User Group (A, B, or C): 
   Dim dtmStartA As Date, dtmStartB As Date, dtmStartC As Date

   dtmStartA = #8:00:00 AM#   'start of 9 hr dayshift
   dtmStartB = #5:00:00 PM#   'start of 8 hr swingshift
   dtmStartC = #1:00:00 AM#   'start of 7 hr graveyard
      
   Select Case dtmClockTime
   
      Case Is >= dtmStartB
         strUserGroup = "B"
   
      Case Is >= dtmStartA
         strUserGroup = "A"
   
      Case Is < dtmStartC     'cause C starts after midnight
         strUserGroup = &quot;B&quot;
   
      Case Else
         strUserGroup = &quot;C&quot;
      
   End Select

End Function

This function could be used in your queries, of course. You should add error handling and test thoroughly. (Perhaps I missed the mark? Feedback, please!) Cheers!
Steve Peterson

 

[Steve101]

Steve,

Welcome to the discussion. A couple of points in response.

(a) I agree; a function is a good idea; callable from queries (see my earlier post). However; I would not hard code the times in it; I'd get the function to search a table. You could of course argue this point on grounds of performance, especially if the times are unlikely to change, but my argument her is purely from a &quot;purist&quot; point of view.

(b) The one minute gaps are easily removed by slightly altering the lookup logic that works off the data.

(c) That being said, your solution is simple and elegant (I havn't checked out the logic exactly); I like the way you only use a start date instead of a range, and the way you order your select case tests (ie. latest one first, etc.) to make sure that the appropriate selection always happens.

Cheers,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[volcano]

Dear all web friends,

Many many thanks for all of your constructive advice to my question here. May I give some feedback to this thread here. To be frank, when I first post this question here, I post this in another forum (microsoft.public.inetserver.asp.general) for any solution too. At that time I received a response that I would like to share with you:

********************************************
I think it would simpler to just determine the UserGroup with script then pull whatever data you need for that group from the database. Something like this should work:

<%
'Get the minutes of the current time
iMins = DatePart(&quot;h&quot;, Now) * 60 + DatePart(&quot;n&quot;, Now)

Function CurrGroup(CurrMin)
If CurrMin > 479 AND CurrMin < 1021 Then
CurrGroup = &quot;A&quot;
ElseIf CurrMin > 1020 OR CurrMin < 59 Then
CurrGroup = &quot;B&quot;
ElseIf CurrMin > 59 AND CurrMin < 480 Then
CurrGroup = &quot;C&quot;
End If
End Function

Response.Write &quot;Current UserGroup: &quot; & CurrGroup(iMins) %>

If the start and end times for each group might change, pull them from the database, create variables with the times converted to minutes and plug those variables into the script instead of hard coding the values.
***********************************************

So coincident that this solution is quite similar to the one BoffoSteve suggested. But as my question here is for sake of simplicity, my actual story is that each user group's working hours may not be so &quot;clear-cut&quot;. I mean the EndWorkTime of User group A may be modified (yes, they are modifiable) to be &quot;18:00 pm&quot;. So given the current time is 17:30pm, my query should be able to retrieve user group A & B. Therefore the function-based solution might not fully suit all my needs.

Then I tried Danvlas and Steve101's solutions. I tried the whole query suggested by Danvlas. In fact Danvlas's one-query solution is what I want most coz it won't need me to change the table's structure. But I had difficulties when I tried to embed the query into my ASP page and replace all variables with my own variables. It seems I can't handle the parenthesis very well...I was always warned with missing parenthesis. But I am not saying that Danvlas's query is wrong. Then I tried the solution of Steve101. I change the table's data according to Steve101's suggestion. Finally I can use a small query to acheive my need although I have to do some data massage. Just as you said, all solutions have adv. and disadv.. The function-based is a simpler one, but might not be flexible to suit all my needs; Danvlas's solution should be the one I want most. But maybe I can't handle the long query very well, for saving time I tried Steve101's one.

Anyway, thanks all of your valuable inputs!!

 

[danvlas]

This is an improved SQL as the previous version missed the record for the exact EndWorkTime...

SELECT UserGroup, StartWorkTime, EndWorkTime
FROM WORKTIME
WHERE ((StartWorkTime>=[YourTime] And StartWorkTime>[EndWorkTime]) AND (EndWorkTime<[StartWorkTime] And EndWorkTime>=[YourTime])) OR ((StartWorkTime<=[YourTime] And StartWorkTime<[EndWorkTime]) AND (EndWorkTime>=[YourTime] And EndWorkTime>=[StartWorkTime])) OR ((StartWorkTime<=[YourTime] And StartWorkTime>[EndWorkTime]) AND (EndWorkTime<[YourTime]));

KornGeek:
Also, this approach seems to neglect the circumstance where either the [Your Time] parameter equals StartWorkTime or EndWorkTime Not anymore

or when StartWorkTime equals EndWorkTime. You're right, but why would the start time equal to the end time???

some of the query criteria were repeated (such as (StartWorkTime >[EndWorkTime]) and ([EndWorkTime] < StartWorkTime)). I'm not sure if these were intentional to make it more readable or not. They can be removed which would probably make a small improvement in speed (not sure that it matters), but it would then impact the readability of the code.
Wrong...They are not repeated, they are combined structures as you can now see in the coloured scheme.

For those who have difficulties in reading the SQL Where Clause, here is how to build it in the query grid:

For StartWorkTime:
Condition row 1:
>=[YourTime] And >[EndWorkTime]

Condition row 2:
<=[YourTime] And <[EndWorkTime]

Condition row 3:
<=[YourTime] And >[EndWorkTime]


For EndWorkTime:
Condition row 1:
<[StartWorkTime] And >=[YourTime]

Condition row 2:
>=[YourTime] And >=[StartWorkTime]

Condition row 3:
<[YourTime]

As for using just StartWorkTime instead of a range, it really simplifies the query (but in this case it needs an additional row with the StartTime = 00:00, a strong point for Steve's solution)

SELECT TOP 1 UserGroup, StartWorkTime
FROM WORKTIME
WHERE StartWorkTime<=[YourTime]
ORDER BY StartWorkTime DESC;

Functions in queries...they are sometimes programmer's escape from SQL...
Have to admit though that I myself use functions when I (often) want to simplify my work. But I can't say for sure it improves performance. It definitely speeds up development.


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[KornGeek]

Dan,
to answer your question:
why would the start time equal to the end time???
In a perfect (or even reasonably good) world, this would never happen. However, I think we all know how unpredictable end users can be at times. I try to prepare for the unexpected.

As far as reducing the criteria, it's possible to do, but with these simple comparisons, we've probably spent more time discussing it already than it would save.

However (just because I love logic puzzles), here is how it can be reduced.
For StartWorkTime:
Condition row 1:
>=[YourTime] And >[EndWorkTime]

Condition row 2:
<=[YourTime] And <[EndWorkTime]

Condition row 3:
<=[YourTime] And >[EndWorkTime]


For EndWorkTime:
Condition row 1:
>=[YourTime]

Condition row 2:
>=[YourTime]

Condition row 3:
<[YourTime]

You don't need to repeat the comparison in the EndWorkTime column because
A) if StartWorkTime>[EndWorkTime] evaluates to False, then the record is already excluded
B) if StartWorkTime>[EndWorkTime] evaluates to True, then EndWorkTime<[StartWorkTime] will necessarily evaluate to True also.

As I said, this is trivial. I just wanted to explain my reasoning.