I have a table that is used to log the hours a person works. Amongst other fields, it contains a datetime field and an hours (tinyint) field.
I am trying to ascertain the hours a person works between two given dates which is (should be) simple. My problem is when the hours worked overlap days.
For instance; Jim starts work on the 1st August at 23:00 for 5 hours. This means that Jim worked for 1 hour on the 1st and 4 hours on the second. If I run the report
Select hoursWorked from timeLog where startDate = 2010/08/01, I get 5 returned.
If I run
Select hoursWorked from timeLog where startDate = 2010/08/02, I get nothing, as expected.
I have struggled with this for days now and cannot workout how I can get a query that returns 4 when run using the date parameter as 2010/08/02. Can anyone please advise or point me in the right direction?
View Complete Post