时间戳

首页 > 解决方案 > 如何计算超过 17 周的工作时间

sql - 如何计算超过 17 周的工作时间

问题描述

我正在尝试计算有多少现场工程师在 17 周内工作超过 48 小时。（根据法律，您不能在 17 周内工作超过 48 小时）我设法为 1 名工程师运行查询，但是当我在没有工程师过滤器的情况下运行它时，我的查询非常慢。我需要计算工作时间超过 48 小时和少于 48 小时的工程师数量，然后计算每周平均工作时间。

注意：我在 SPICEMEISTER 和 SMARTMEISTER 上做一个联合，因为它们是我们的新旧数据库。

• 48 小时内有多少现场工程师工作 • 48 小时内有多少现场工程师工作 • 工程师每周平均工作时间是多少

    SELECT DS_Date  
        ,TechPersNo
    FROM
    (
        SELECT DISTINCT
            SMDS.EPL_DAT as DS_Date
           ,EN.pers_no as TechPersNo
        FROM    
            [SpiceMeister].[FS_OTBE].[EngPayrollNumbers] EN
        INNER JOIN
            [SmartMeister].[Main].[PlusDailyKopf] SMDS
                ON RIGHT(CAST(EN.[TechnicianID] AS CHAR(10)),5) = SMDS.PRPO_TECHNIKERNR
        WHERE     
        SMDS.EPL_DAT >= '2017-01-01'
        and
                  SMDS.EPL_DAT < '2018-03-01'

UNION ALL
        SELECT DISTINCT
             SPDS.DailySummaryDate as DS_Date
            ,EN.pers_no as TechPersNo

        FROM
            [SpiceMeister].[FS_OTBE].[EngPayrollNumbers] EN
        INNER JOIN 
            [SpiceMeister].[FS_DS_BO].[DailySummaryHeader] SPDS
                ON EN.TechnicianID = SPDS.TechnicianID
        WHERE 
                  SPDS.DailySummaryDate >= '2018-03-01'


    ) as Techa
    where TechPersNo = 850009
) Tech
cross APPLY

快速的结果

标签： sqlsql-server

解决方案

速度缓慢肯定是由于使用交叉应用和相关子查询。这将强制以每行为基础进行计算，并阻止 SQL Server 优化任何内容。

这似乎更像是一个“分组依据”查询，但我可以理解为什么你在复杂的累积计算中遇到了麻烦，因为你需要按人和日期输出，但平均值不涉及日期有问题，但日期范围在有问题的日期结束。

我首先要做的是进行一个通用查询来捕获两个数据集之间的基础数据。这就是我在下面的“dailySummaries”公用表表达式中所做的。然后我会将dailySummaries加入到员工匹配的自身并选择所需的日期范围。从那时起，我将按员工和日期分组，按日期范围汇总。

with

    dailySummaries as (

        select      techPersNo = en.pers_no,
                    ds_date = smds.epl_dat,
                    dtDif = datediff(minute, smds.abfahrt_zeit, smds.rueck_zeit)
        from        smartMeister.main.plusDailyKopf 
        join        smartMeister.main.plusDailyKopf smds
                        on right(cast(en.technicianid as char(10)),5) = smds.prpo_technikernr
        where       smds.epl_dat < '2018-03-01'

        union all
        select      techPersNo = en.pers_no,
                    dailySummaryDate,
                    datediff(minute,
                        iif(spds.leaveHome < spds.workStart, spds.leaveHome, spds.workStart),
                        iif(spds.arrivehome > spds.workEnd, spds.arrivehome, spds.workEnd)
                    )
        from        spiceMeister.fs_ds_bo.dailySummaryHeader spds 
        join        spiceMeister.fs_ds_bo.dailySummaryHeader spds 
                        on en.TechnicianID = spds.TechnicianID
        where       spds.DailySummaryDate >= '2018-03-01'

    )

    select      ds.ds_date,
                ds.techPersNo,
                AvgHr = convert(real, sum(dsPrev.dtDif)) / (60*17)
    from        dailySummaries ds
    left join   dailySummaries dsPrev 
                    on ds.techPersNo = dsPrev.techPersNo 
                    and dsPrev.ds_date between dateadd(day,-118, ds.ds_date) and ds.ds_date
    where       ds.ds_date >= '2017-01-01'
    group by    ds_date,
                techPersNo 
    order by    ds_date

我的翻译可能有一两处错误，但你明白了。

将来，发布一个更简单的示例。来自不同数据库的两个数据集的联合并不是您试图询问的问题的核心。许多日期过滤不是问题的核心。连接匹配逻辑中的特殊转换并不重要。这些事情掩盖了真正的问题。

推荐阅读