Count number of hours

[jemz]

here is my sample data

it gives me 16 hours, if I run this sql

SELECT SUM(HOUR(TIMEDIFF(end_time, start_time))) AS 'hour'
  FROM activelogs
where user_id = 24
HAVING MIN(start_time) BETWEEN '2015-09-07 06:00:00' AND '2015-09-11 22:00:00'
   AND MAX(end_time) BETWEEN '2015-09-07 06:00:00' AND '2015-09-11 22:00:00'

but it should not count the last row with an id 32 because the time is beyond to 22:00:00

[DaveMaxwell]

@jemz

but it should not count the last row with an id 32 because the time is beyond to 22:00:00

Then I misunderstood the requirement. If you need to filter out before you get the date range, then you’ll need to remove the HAVING, and add a WHERE clause checking start_time and end_time instead of MIN(start_time) and MAX(end_time)

[jemz]

Is this correct ?

SELECT Sum(Hour(Timediff(end_time, start_time))) AS 'hour'
 FROM activelogs
 WHERE  start_time BETWEEN '2015-09-07 06:00:00' AND '2015-09-11 22:00:00'
       AND Time(start_time) BETWEEN '06:00:00' AND '22:00:00'
       AND end_time BETWEEN '2015-09-07 06:00:00' AND '2015-09-11 22:00:00'
       AND Time(end_time) BETWEEN '06:00:00' AND '22:00:00'
       AND user_id = 24

[jemz]

@r937,@DaveMaxwell,

I apologize my post, there is some changes.

date range ‘2015-09-07 06:00:00’ to ‘2015-09-11 22:00:00’

Just to clarify, I want to count the total hours inside that datetime range and also I want to count the total hours outside that datetime range before and after. I want to get two result 1 for the total hours inside that range then the other the total hours outside that range.

Thank you in advance.

[DaveMaxwell]

OK perhaps I’m just being testy, but I think you REALLY need to stop and think about what exactly you need, and what results you’re trying to get back. At best, they are hazy and incomplete, and the target keeps moving - at least from my perspective.

First you talked about max and min time, then you wanted to limit the starting and ending time per day, and now you’re looking to count hours per day within the date range, and not only a date range, but an hour range per day.

From my perspective, you’re getting into a situation where it would just be easier to handle this in code instead of trying to jury rig a sql query.

So if I’m understanding THIS iteration, you want to count all of the hours. You want to have three counts: One counting hours outside of the date range, one counting hours within the date range, but within a certain range of hours (like a normal working day) and one counting hours within the date range but outside of that certain range of hours (like overtime). Then you have to account for someone who works from within one day into another day (i.e. works from 2015-09-09 21:00:00 to 2015-09-10 11:00:00)

If this understanding is correct, then I would just select from the table, ordered by user_id, then start and end datetimes. I would then follow this pseudo code in the Server side language of your choice (I’m ignoring the last issue…my brain just hasn’t had enough caffeine sent to it…)

For this example, date range is your range in post #24

if ending date is prior to 2015-09-07 0600 or starting date is after 2015-09-11 2200
    add entire hours to the out of range counter
else if starting date is before 2015-09-07 0600, but ending date after
    add hours from starting date up to 2015-09-07 0559 to out of range counter
    add the rest to the normal hours counters (see all hours below)
else if ending date is after 2015-09-11 2200, but starting date is before
    add hours from 2015-09-11 2200 to ending date to out of range counter
    add the rest to the normal hours counters (see all hours below)
else
    -- all hours
   if starting time prior to normal hours (say 0900)
      add from starting time to 0900 to overtime
      if ending time after normal hours (say 2200)
          add from 2200 to ending time to overtime
          add rest to normal time
      else 
          add from 0900 up to ending time to normal time
   else if ending time after 2200
      add from 2200 to ending time to overtime
      if starting time before 0900
          add from starting time to 0900 to overtime
          add rest to normal time
      else 
          add from starting time to 2200 to normal time
   else
       -- all hours are within normal time
          add from starting time to ending time to normal time

rinse and repeat with each log record for that user_Id

[jemz]

@DaveMaxwell,

Thank you for the reply…Ok I will try this one.

[r937]

[quote=“DaveMaxwell, post:25, topic:205017, full:true”]
… hazy and incomplete, and the target keeps moving[/quote]
i’m glad i decided to put off trying to solve the initial problem