CODEWITHSUNDEEP
mysqlsqldatabase
francisco.preller
francisco.preller

Reputation: 6639

MySQL querying rostered shifts over a number of days - simpler way?

I have some reports to do with MySQL and the below code is my current solution to the problem.

It seems quite straight forward until I start considering when staff shifts go over midnight so the query has to be adjusted. Right now I have php generate different queries depending on the date/time span as follows:

If the work shift falls whithin the same day (ie: 8am-8pm across 2 days):

SELECT <select statements>
FROM <from statements>
WHERE
(
    (Date = '2012-04-16' AND Time BETWEEN '08:00:00' AND '20:00:00')
    OR
    (Date = '2012-04-02' AND Time BETWEEN '08:00:00' AND '20:00:00')
);

If the shift goes over midnight it gets complex (ie: 8pm-8am across 2 days):

SELECT <select statements>
FROM <from statements>
WHERE
(
    (
        (Date = '2012-04-16' AND Time >= '20:00:00')
        OR
        (Date = '2012-04-17' AND Time <= '08:00:00')
    )
    OR
    (
        (Date = '2012-04-17' AND Time >= '20:00:00')
        OR
        (Date = '2012-04-18' AND Time <= '08:00:00')
    )
);

As you can imagine, these queries get really long and heavy with every single day I add to the report. There must be a smarter way to do this - could anyone offer any insight?

Upvotes: 1

Views: 1198

Answers (2)

Sreeram G
Sreeram G

Reputation: 1

I've recently came across such issue, where I've to write a code for three shifts including night shift, which involves current day and next day as well.

here is the code:

Select *, Case When Shifts = 'Night' and 
(DATEPART(HOUR,DATEADD(day,1,SystemDate_PST))>='00'               //next day data// 
and DATEPART(HOUR,DATEADD(day,1,SystemDate_PST)) <='06')
THEN DATEADD(DAY,-1, SystemDate_PST) else SystemDate_PST               // to show next day data as current day data//
end as Timings from 
(
Select *,
case 
when DATEPART(HOUR, SystemDate_PST)*60+DATEPART(MINUTE, SystemDate_PST) between
06*60+30 and 14*60+30 then 'Morning'
When DATEPART(HOUR, SystemDate_PST)*60+DATEPART(MINUTE, SystemDate_PST) between
23*60-30 and  23*60+60 then 'Night' 
when datepart(HOUR,dateadd(day,1,SystemDate_PST)) >='00' 
and datepart(HOUR,dateadd(day,1,SystemDate_PST))*60 + DATEPART(MINUTE, SystemDate_PST
<=07*60-30 then 'Night'
else 'Noon' 
end as Shifts from (Select * from Table) a )b

My shift timings are: Morning: 06:30AM - 14:29PM Noon: 14:30PM - 22:29PM Night : 22:30PM- 06:29AM

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115620

If you want to have more elegant code, this part:

    (Date = '2012-04-16' AND Time >= '20:00:00')
    OR
    (Date = '2012-04-17' AND Time <= '08:00:00')

could be changed into:

    (Date, Time) >= (DATE('2012-04-16'), TIME('20:00:00'))
    AND 
    (Date, Time) <= (DATE('2012-04-17'), TIME('08:00:00'))

Compound index on (Date, Time) would help in both yours and the above version.

If you have several of those conditions, like your example:

SELECT <select statements>
FROM <from statements>
WHERE
(
    (
        (Date = '2012-04-16' AND Time >= '20:00:00')
        OR
        (Date = '2012-04-17' AND Time <= '08:00:00')
    )
    OR
    .....
    OR 
    (
        (Date = '2012-05-27' AND Time >= '20:00:00')
        OR
        (Date = '2012-05-28' AND Time <= '08:00:00')
    )
)

you could turn it into:

SELECT <select statements>
FROM <from statements>
  CROSS JOIN
      ( SELECT TIME('20:00:00') AS start_time
             , TIME('08:00:00') AS end_time
      ) AS cc
  JOIN
      ( SELECT d                   AS this_day
             , d + INTERVAL 1 DAY  AS next_day
        FROM
          ( SELECT DATE('2012-04-16') AS d
          UNION ALL
            ...
          UNION ALL
            SELECT '2012-05-27'
          ) AS s 
      ) AS selected
     ON  (Date, Time) >= (selected.this_day, cc.start_time)
     AND (Date, Time) <= (selected.next_day, cc.end_time  )

Upvotes: 1

Related Questions