从特定日期获取记录

Question

我现在在做工资。我已经设法计算了员工在同一天的时间和超时时间。这是我要取的记录的样本表。我的问题是，如果雇员在晚上10点到早上6点的轮班或类似的轮班工作，需要他们工作到第二天，我不知道该使用什么条件。我在查询中使用distinct，因为hr提供的数据包含许多重复的条目。

----------------------------------------------------
| empID     |   Date     |    Type    |   RecTime  |
----------------------------------------------------
|   1      | 2012-11-01  | 1-TIME IN  |    21:45   |
|   1      | 2012-11-02  | 1-TIME OUT |    06:05   |
|   1      | 2012-11-02  | 1-TIME IN  |    21:33   |
|   1      | 2012-11-03  | 1-TIME OUT |    06:08   |
|   1      | 2012-11-04  | 1-TIME IN  |    11:49   |
|   1      | 2012-11-04  | 1-TIME OUT |    21:39   |
|   1      | 2012-11-05  | 1-TIME IN  |    14:25   |
|   1      | 2012-11-05  | 1-TIME OUT |    20:07   |
----------------------------------------------------

以下是我的PHP代码：

<?php

$c = 1;
$query = mysql_query("SELECT DISTINCT EmpID, Date, Type from paymaster2c.tx_tito_pm ORDER BY EmpID, Date");
while ($row = mysql_fetch_array($query)){
$EID = $row['EmpID'];
$D = $row['Date'];
$T = $row['Type'];

$RTi = 'no time in';
$RTo = 'no time out';
$RBo = 'no break out';
$RBi = 'no break in';

$query1 = mysql_query("SELECT RecTime from tx_tito_pm WHERE Date='$D' AND EmpID='$EID' AND Type='1-TIME IN' LIMIT 1");
while ($row1 = mysql_fetch_array($query1)){
$RTi = date('H:i:s',strtotime($row1['RecTime']));

    if (date('H:i:s',strtotime('$RTi')) > date('H:i:s',strtotime('03:00 PM')) && date('H:i:s',strtotime('$RTi')) <= date('H:i:s',strtotime('11:59 PM'))) {
    $date = new DateTime('$D');
        $date->add(new DateInterval('P1D'));
        $NewDate = $date->format('Y-m-d');

        //$NewD = date('Y-m-d', strtotime('$D + 1day'));
        $query3 = mysql_query("SELECT RecTime from tx_tito_pm WHERE Date='$date' AND EmpID='$EID' AND Type='4-TIME OUT' LIMIT 1");
            while ($row3 = mysql_fetch_array($query3)){
                $RTo = date('H:i:s',strtotime($row3['RecTime']));

                $RTii = date('H:i:s',strtotime('23:59:00')) - date('H:i:s',strtotime($RTi));

            $start_date = new DateTime(date('H:i:s',strtotime('23:59:00')));
            $since_start = $start_date->diff(new DateTime(date('H:i:s',strtotime('$RTi'))));
            $start_date1 = new DateTime(date('H:i:s',strtotime('00:00:00')));
            $since_start1 = $start_date1->diff(new DateTime(date('H:i:s',strtotime('$RTo'))));

            $M1 = $since_start1->i ;
            $H1 = $since_start1->h ;
            $M = $since_start->i ;
            $H = $since_start->h ;
            $a = $H + $H1;
            $b = $M + $M1;


            $RTD = $RTo-$RTi;

            echo "#" .$c. "   ". $EID . " == " . $D . " == " . date('D', strtotime($D)) ." == " . $T ." ==>>" .$RTo. "-". $RTi. "==" .$RTD. "//" .$a. "hrs and " .$b. "minutes. <br /> \n";
            $c++;
            }

    }
    else {

    $query2 = mysql_query("SELECT RecTime from tx_tito_pm WHERE Date='$D' AND EmpID='$EID' AND Type='4-TIME OUT' LIMIT 1");
        while ($row2 = mysql_fetch_array($query2)){
        $RTo = date('H:i:s',strtotime($row2['RecTime']));

            $start_date = new DateTime(date('H:i:s',strtotime($RTo)));
            $since_start = $start_date->diff(new DateTime(date('H:i:s',strtotime($RTi))));

            $M = $since_start->i ;
            $H = $since_start->h ;


            $RTD = $RTo-$RTi;

            echo "#" .$c. "   ". $EID . " == " . $D . " == " . date('D', strtotime($D)) ." == " . $T ." ==>>" .$RTo. "-". $RTi. "==" .$RTD. "//" .$H. "hrs and " .$M. "minutes. <br /> \n";
            $c++;

        }
    }
}


}
echo $c;
?>

希望有人能帮我这个忙。提前谢谢你。

Answer 1

我认为您最终需要开发新的或附加的表来帮助您解决这个问题(如果您想要以MySQL为中心的解决方案)。下面是一个蛮力查询，可能需要优化/个性化以包括：

• 确保在所需字段上有索引。
• (IMHO)创建SQL视图来封装这些派生表(即括号内的重复选择)
• 我不知道你的字段类型。所以我以为时间是一个角色字段。这可能需要改变
• 您可能希望运行此查询的变体，以填充临时表或工作表。确定转换结束的自连接将随着数据集的增长而变得密集。
• 最后，当数据有不匹配/成对的值时，这并不会被考虑在内(也就是说，没有签出的两个签入-反之亦然)。

有关演示，请参阅以下内容：http://sqlfiddle.com/#!2/558fb/22

样本输出

| EMPID |                         CHECKIN |                        CHECKOUT | WORKED |
|-------|---------------------------------|---------------------------------|--------|
|     1 | November, 01 2012 21:45:00+0000 | November, 02 2012 06:05:00+0000 | 8.3333 |
|     1 | November, 02 2012 21:33:00+0000 | November, 03 2012 06:08:00+0000 | 8.5833 |
|     1 | November, 04 2012 11:49:00+0000 | November, 04 2012 21:39:00+0000 | 9.8333 |
|     1 | November, 05 2012 14:25:00+0000 | November, 05 2012 20:07:00+0000 |    5.7 |

SQL代码：

SELECT TimeIn.empID, TimeIn.BetterDate AS CheckIn, TimeOut.BetterDate AS CheckOut
, (TO_SECONDS(TimeOut.BetterDate) - TO_SECONDS(TimeIn.BetterDate))/(60*60) AS Worked
FROM (
SELECT empID, Type, Date, RecTime, DATE_ADD(Date, Interval RecTime HOUR_MINUTE) as BetterDate
FROM timedata) AS TimeIn
LEFT JOIN (
  SELECT empID, Type, Date, RecTime, DATE_ADD(Date, Interval RecTime HOUR_MINUTE) as BetterDate
  FROM timedata) AS TimeOut
ON TimeIn.EmpID = TimeOut.EmpID
  AND TimeIn.BetterDate < TimeOut.BetterDate
  AND TimeOut.Type = '1-TIME OUT'
  AND NOT EXISTS 
    (SELECT *
    FROM TimeData
    WHERE TimeData.Type = '1-TIME OUT'
      AND DATE_ADD(Date, Interval RecTime HOUR_MINUTE) < TimeOut.BetterDate
      AND DATE_ADD(Date, Interval RecTime HOUR_MINUTE) > TimeIn.BetterDate)
WHERE TimeIn.Type = '1-TIME IN'
ORDER BY CheckIn, CheckOut