查询上个月的假期/病假

Question

我正在使用laravel和sql srv有些工人有时会休假或请病假，这些假期存储在“假期”表中，而休假的工人详细信息存储在“用户”表中。所以实际上假期表属于Worker_id指向用户表的“id”的用户。有两个表：“ users”和“ vacations”" users" 有列: id,name" vacations" 有列： vacation_id, worker_id, begin_date, end_date,descriptiondescription 只能有两个值1- vacation，2-iilness我想做一个查询，vacation/iilness分别给我每个用户上个月的总天数这样输出表将是Name  | Type    | Total days | David | iilness |     2      |David | vacation|     3      |Sara  | iilness |     1      |Sara  | Vacation|     5      |我发现这个问题可能有助于回答： Retrive all rows from last month (Laravel + Eloquent)但只是其中的一部分。

Answer 1

您可以使用JOIN、 聚合和DATEDIFF().

棘手的部分是vacation表中是否有跨越多个月（并且可能在未来）的期间。要处理这个问题，您可以使用计算当前月份的第一个月和月底的子查询，然后使用它来：

使用 a过滤vacation表JOIN：您要选择开始日期或结束日期属于当前月份的记录

调整计算SELECT：如果开始日期在月初之前，您只想从月初开始计算天数；如果结束日期在月底之后，您只想计算到月底

考虑：

SELECT

    u.name,

    v.description,

    SUM(DATEDIFF(

        LEAST(v.end_date, m.end_date), 

        GREATEST(v.begin_date, m.begin_date)

    )) total_days

FROM 

    user u

    INNER JOIN vacation v ON u.id = v.woker_id

    INNER JOIN (

        SELECT 

            LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY begin_date, 

            LAST_DAY(NOW()) end_date

    ) m

        ON v.begin_date BETWEEN m.begin_date AND m.end_date

        OR v.end_date   BETWEEN m.begin_date AND m.end_date

GROUP BY u.id, u.name, v.description

以上是针对 MySQL 的。SQL Server 中日期函数不同的等价物是：

SELECT

    u.name,

    v.description,

    SUM(DATEDIFF(

        'day',

        CASE WHEN v.begin_date < m.begin_date THEN m.begin_date ELSE v.begin_date END,

        CASE WHEN v.end_date > m.end_date     THEN m.end_date   ELSE v.end_date   END

    )) total_days

FROM 

    user u

    INNER JOIN vacation v ON u.id = v.woker_id

    INNER JOIN (

        SELECT 

            DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) begin_date, 

            EOMONTH(GETDATE()) end_date

    ) m

        ON v.begin_date BETWEEN m.begin_date AND m.end_date

        OR v.end_date   BETWEEN m.begin_date AND m.end_date

GROUP BY u.id, u.name, v.description

Answer 2

最后我找到了以下在 Laravel 上实现的方法

$query = "

        SELECT

    u.name,

    v.description,

    SUM(DATEDIFF(

        day,

        CASE WHEN v.begin_date < m.begin_date THEN m.begin_date ELSE v.begin_date END,

        CASE WHEN v.end_date > m.end_date     THEN m.end_date   ELSE v.end_date   END

    )+1) total_days

FROM 

    [turtle].[dbo].[users] u

    INNER JOIN [turtle].[dbo].[vacations] v ON u.id = v.Worker_id

    INNER JOIN (

        SELECT 

            DATEADD(month, DATEDIFF(month, 0, DATEADD(month,-1,GETDATE())), 0) begin_date, 

            EOMONTH(DATEADD(month,-1,GETDATE())) end_date

    ) m

        ON v.begin_date BETWEEN m.begin_date AND m.end_date

        OR v.end_date   BETWEEN m.begin_date AND m.end_date

GROUP BY u.id, u.name, v.description

ORDER BY u.name, v.description

        ";

        $results = DB::select(DB::raw($query));

        dd($results);

Answer 3

对于 sql 服务器：

尚未对其进行测试，但是像这样的东西可以完成这项工作。

select name,type,sum(datediff(d,begin_date,end_date)) as Total_days  from vacations v

    inner join users u

    on u.id = v.worker_id

    where begin_date> select dateadd(d,-DATEPART(DD,GETDATE()),GETDATE())

    group by name,type

正如您所描述的，这仅适用于上个月的叶子。