获取连续日期的集合 [英] Getting collections of consecutive dates

查看：79
本文介绍了获取连续日期的集合的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个存储员工休假的数据库.员工休假的每一天，都会将新记录输入数据库.我要实现的是让某人输入员工ID和日期范围，并针对每个缺勤时间段返回记录，说明日期，日期至期限，持续时间以及是否是上午或下午(半天)./p>

其外观应类似于(对于9999号员工，日期为2011-08-08至2011-09-01):

employee_id | Start      | start_am_pm | End        | end_am_pm | Duration
9999        | 2011-08-10 | PM          | 2011-08-12 | AM        | 2
9999        | 2011-09-01 |             | 2011-09-01 |           | 1

注意:上面的第一个持续时间是2，因为10号和12号都是半天，而11号是完整日.

无论如何.如果发件人"日期不是员工休假的日期，则我所执行的查询将完全符合我的期望.例如，在上面的示例中，如果我将起始日期设置为10、11或12，则会删除该行.它应该计算指定日期之间的天数.

当前显示方式(员工9999，日期为2011-08-11至2011-09-01):

employee_id | Start      | start_am_pm | End        | end_am_pm | Duration
9999        | 2011-09-01 |             | 2011-09-01 |           | 1

与"To"日期相似，但我已解决该问题.对于发件人"日期，类似的方法不起作用.下面是我的存储过程.

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `GetLeaveDates`$$

CREATE DEFINER=`root`@`%` PROCEDURE `GetLeaveDates`(pEmpID INT, pDateFrom DATETIME, pDateTo DATETIME)
BEGIN

SELECT 
    a.start_date,
CASE WHEN a.am_pm = 1 THEN "AM"
     WHEN a.am_pm = 2 THEN "PM"
     ELSE "" END AS start_am_pm,
    CASE WHEN pDateTo > MIN(c.start_date) THEN
        MIN(c.start_date)
    ELSE
        pDateTo
    END AS End,
CASE WHEN c.am_pm = 1 THEN "AM"
     WHEN c.am_pm = 2 THEN "PM"
     ELSE "" END AS start_am_pm,
    CASE WHEN a.am_pm = 0 AND c.am_pm = 0 THEN
        DATEDIFF(MIN(c.start_date),a.start_date)+1
         WHEN (a.am_pm = 0 AND c.am_pm <> 0) OR (c.am_pm = 0 AND a.am_pm <> 0) THEN
        DATEDIFF(MIN(c.start_date),a.start_date)+0.5
         WHEN a.am_pm <> 0 AND c.am_pm <> 0 THEN
        DATEDIFF(MIN(c.start_date),a.start_date)
    END
     AS Duration
FROM t AS a
LEFT JOIN t AS b ON a.employee_id=b.employee_id AND a.start_date = ADDDATE(b.start_date,1)
LEFT JOIN t AS c ON a.employee_id=c.employee_id AND a.start_date <= c.start_date
LEFT JOIN t AS d ON c.employee_id=d.employee_id AND c.start_date = ADDDATE(d.start_date,-1)
WHERE b.start_date IS NULL AND c.start_date IS NOT NULL AND d.start_date IS NULL
AND a.EMPLOYEE_ID = pEmpID
AND a.START_DATE BETWEEN pDateFrom AND pDateTo
GROUP BY a.employee_id, a.start_date   
; END$$

DELIMITER ;

解决方案

好吧，我弄清楚了，这很简单，基本上，对于每个LEFT JOIN，我都必须通过传入的参数筛选出开始日期

我还必须过滤休假是否得到批准，如果批准了，则填写一个Approved_DateTime和Approved_By字段.在某些情况下，持续时间的计算也有些偏离.所以我的存储过程现在看起来像:

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `GetLeaveDates`$$

CREATE DEFINER=`root`@`%` PROCEDURE `GetLeaveDates`(pEmpID INT, pDateFrom DATETIME, pDateTo DATETIME, pApproved BOOLEAN)
BEGIN

SELECT 
    DATE_FORMAT(a.start_date,'%d/%m/%y') AS start,
CASE WHEN a.am_pm = 1 THEN "AM"
     WHEN a.am_pm = 2 THEN "PM"
     ELSE "" END AS start_am_pm,
    DATE_FORMAT(CASE WHEN pDateTo > MIN(c.start_date) THEN
        MIN(c.start_date)
    ELSE
        pDateTo
    END, '%d/%m/%y') AS end,
CASE WHEN c.am_pm = 1 THEN "AM"
     WHEN c.am_pm = 2 THEN "PM"
     ELSE "" END AS end_am_pm,
  CASE WHEN a.am_pm = 0 THEN
    CASE WHEN c.am_pm = 0 OR c.am_pm = 2 THEN
        DATEDIFF(MIN(c.start_date),a.start_date)+1
    WHEN c.am_pm = 1 THEN
        DATEDIFF(MIN(c.start_date),a.start_date)+0.5
    END
WHEN a.am_pm = 1 THEN
    CASE WHEN c.am_pm = 0 OR c.am_pm = 2 THEN
        DATEDIFF(MIN(c.start_date),a.start_date)+1
    WHEN c.am_pm = 1 THEN
        DATEDIFF(MIN(c.start_date),a.start_date)+0.5
    END
WHEN a.am_pm = 2 THEN
    CASE WHEN c.am_pm = 0 OR c.am_pm = 2 THEN
        DATEDIFF(MIN(c.start_date),a.start_date)+0.5
    WHEN c.am_pm = 1 THEN
        DATEDIFF(MIN(c.start_date),a.start_date)
    END 
END AS Duration

FROM t AS a
LEFT JOIN t AS b ON a.employee_id=b.employee_id AND a.start_date = ADDDATE(b.start_date,1) AND ISNULL(b.approved_datetime) <> pApproved AND b.start_date BETWEEN pDateFrom AND pDateTo
LEFT JOIN t AS c ON a.employee_id=c.employee_id AND a.start_date <= c.start_date AND ISNULL(c.approved_datetime) <> pApproved AND c.start_date BETWEEN pDateFrom AND pDateTo
LEFT JOIN t AS d ON c.employee_id=d.employee_id AND c.start_date = ADDDATE(d.start_date,-1) AND ISNULL(d.approved_datetime) <> pApproved AND d.start_date BETWEEN pDateFrom AND pDateTo
WHERE b.start_date IS NULL AND c.start_date IS NOT NULL AND d.start_date IS NULL
AND a.EMPLOYEE_ID = pEmpID
AND a.START_DATE BETWEEN pDateFrom AND pDateTo
AND ISNULL(a.approved_datetime) <> pApproved
AND a.start_date BETWEEN pDateFrom AND pDateTo
GROUP BY a.employee_id, a.start_date
; END$$

DELIMITER ;

I have a database which stores employees leave. For each day an employee is on leave a new record is entered to the database. What I want to achieve is for someone to enter an employee id and a date range and for each period of absence a record is returned stating date from, date to, duration and also whether it was AM or PM (for half days).

It should look something like (for employee 9999 and dates 2011-08-08 to 2011-09-01):

employee_id | Start      | start_am_pm | End        | end_am_pm | Duration
9999        | 2011-08-10 | PM          | 2011-08-12 | AM        | 2
9999        | 2011-09-01 |             | 2011-09-01 |           | 1

Note: first duration above is 2 because 10th and 12th are both half days and 11th is a full.

Anyway. The query I have works exactly as I would expect, if the From date is not a date that the employee has leave on. Eg, in the above example if I set the from date to 10th, 11th or 12th, it removes that line. It should count the days between the dates specified.

How it currently shows (for employee 9999 and dates 2011-08-11 to 2011-09-01):

employee_id | Start      | start_am_pm | End        | end_am_pm | Duration
9999        | 2011-09-01 |             | 2011-09-01 |           | 1

Similar was happening with the To date but I got that fixed. A similar approach didn't work for the From date. Below is my stored procedure.

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `GetLeaveDates`$$

CREATE DEFINER=`root`@`%` PROCEDURE `GetLeaveDates`(pEmpID INT, pDateFrom DATETIME, pDateTo DATETIME)
BEGIN

SELECT 
    a.start_date,
CASE WHEN a.am_pm = 1 THEN "AM"
     WHEN a.am_pm = 2 THEN "PM"
     ELSE "" END AS start_am_pm,
    CASE WHEN pDateTo > MIN(c.start_date) THEN
        MIN(c.start_date)
    ELSE
        pDateTo
    END AS End,
CASE WHEN c.am_pm = 1 THEN "AM"
     WHEN c.am_pm = 2 THEN "PM"
     ELSE "" END AS start_am_pm,
    CASE WHEN a.am_pm = 0 AND c.am_pm = 0 THEN
        DATEDIFF(MIN(c.start_date),a.start_date)+1
         WHEN (a.am_pm = 0 AND c.am_pm <> 0) OR (c.am_pm = 0 AND a.am_pm <> 0) THEN
        DATEDIFF(MIN(c.start_date),a.start_date)+0.5
         WHEN a.am_pm <> 0 AND c.am_pm <> 0 THEN
        DATEDIFF(MIN(c.start_date),a.start_date)
    END
     AS Duration
FROM t AS a
LEFT JOIN t AS b ON a.employee_id=b.employee_id AND a.start_date = ADDDATE(b.start_date,1)
LEFT JOIN t AS c ON a.employee_id=c.employee_id AND a.start_date <= c.start_date
LEFT JOIN t AS d ON c.employee_id=d.employee_id AND c.start_date = ADDDATE(d.start_date,-1)
WHERE b.start_date IS NULL AND c.start_date IS NOT NULL AND d.start_date IS NULL
AND a.EMPLOYEE_ID = pEmpID
AND a.START_DATE BETWEEN pDateFrom AND pDateTo
GROUP BY a.employee_id, a.start_date   
; END$$

DELIMITER ;

解决方案

Ok I figured it out and it was quite simple, basically on each of the LEFT JOIN I had to filter by out the start date by the parameters passed in.

I also had to filter by whether the leave was approved, an Approved_DateTime and Approved_By field where filled in if it was approved. Also the calculation of the duration was a bit off in some circumstances. So my stored procedure now looks like:

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `GetLeaveDates`$$

CREATE DEFINER=`root`@`%` PROCEDURE `GetLeaveDates`(pEmpID INT, pDateFrom DATETIME, pDateTo DATETIME, pApproved BOOLEAN)
BEGIN

SELECT 
    DATE_FORMAT(a.start_date,'%d/%m/%y') AS start,
CASE WHEN a.am_pm = 1 THEN "AM"
     WHEN a.am_pm = 2 THEN "PM"
     ELSE "" END AS start_am_pm,
    DATE_FORMAT(CASE WHEN pDateTo > MIN(c.start_date) THEN
        MIN(c.start_date)
    ELSE
        pDateTo
    END, '%d/%m/%y') AS end,
CASE WHEN c.am_pm = 1 THEN "AM"
     WHEN c.am_pm = 2 THEN "PM"
     ELSE "" END AS end_am_pm,
  CASE WHEN a.am_pm = 0 THEN
    CASE WHEN c.am_pm = 0 OR c.am_pm = 2 THEN
        DATEDIFF(MIN(c.start_date),a.start_date)+1
    WHEN c.am_pm = 1 THEN
        DATEDIFF(MIN(c.start_date),a.start_date)+0.5
    END
WHEN a.am_pm = 1 THEN
    CASE WHEN c.am_pm = 0 OR c.am_pm = 2 THEN
        DATEDIFF(MIN(c.start_date),a.start_date)+1
    WHEN c.am_pm = 1 THEN
        DATEDIFF(MIN(c.start_date),a.start_date)+0.5
    END
WHEN a.am_pm = 2 THEN
    CASE WHEN c.am_pm = 0 OR c.am_pm = 2 THEN
        DATEDIFF(MIN(c.start_date),a.start_date)+0.5
    WHEN c.am_pm = 1 THEN
        DATEDIFF(MIN(c.start_date),a.start_date)
    END 
END AS Duration

FROM t AS a
LEFT JOIN t AS b ON a.employee_id=b.employee_id AND a.start_date = ADDDATE(b.start_date,1) AND ISNULL(b.approved_datetime) <> pApproved AND b.start_date BETWEEN pDateFrom AND pDateTo
LEFT JOIN t AS c ON a.employee_id=c.employee_id AND a.start_date <= c.start_date AND ISNULL(c.approved_datetime) <> pApproved AND c.start_date BETWEEN pDateFrom AND pDateTo
LEFT JOIN t AS d ON c.employee_id=d.employee_id AND c.start_date = ADDDATE(d.start_date,-1) AND ISNULL(d.approved_datetime) <> pApproved AND d.start_date BETWEEN pDateFrom AND pDateTo
WHERE b.start_date IS NULL AND c.start_date IS NOT NULL AND d.start_date IS NULL
AND a.EMPLOYEE_ID = pEmpID
AND a.START_DATE BETWEEN pDateFrom AND pDateTo
AND ISNULL(a.approved_datetime) <> pApproved
AND a.start_date BETWEEN pDateFrom AND pDateTo
GROUP BY a.employee_id, a.start_date
; END$$

DELIMITER ;

这篇关于获取连续日期的集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆