Sql-Server

如何計算工作時間?

  • March 20, 2012

我正在處理商業情報報告,我的任務之一是創建用於時間跟踪的報告。

我必須在工作週(週一至週五)內報告工作時間(每天 10 小時)。

我最初的解決方案是使用標量函式。它不適合假期,但它工作。因此,當我嘗試通過以下方式使用我的函式時,它沒有被數據庫引擎優化:

Select 
  [dbo].[fn_GetWorkMinutesInRange](CreatedOn,ResolvedOn,1, 6, 7)
From
  Incidents

這不是優化的數據庫引擎,在我取消它之前它需要 5 多分鐘才能執行。Incidents 表中有大約 20,000 個事件,因此性能不應該受到 imo 的影響。

我已經放棄了標量函式解決方案,並創建了一個創建日曆的表函式。這也無濟於事,因為我看不到如何將這個功能與我的 Incidents 表結合起來。

如果您的報告基於維度建模的數據倉庫(通常是商業智能解決方案的情況),則很可能存在日期維度和時間維度。

如果沒有這樣的對象,創建它們可能非常有用,因為它們變得高度可重用,因為需要其他報告。

在日期維度中,應該有一個 IsHolidayFlag 列。在時間維度中,應該有一個 IsBusinessMinuteFlag 列(根據這個維度的粒度,可能是 IsBusinessSecondFlag)。

如果索引正確,則查詢將相對直接且快速。CROSS JOIN 存在成本(可以創建為可重用的視圖),但對於 200,000 行,它應該可以忽略不計。

SELECT  i.IncidentID,
      'WorkMinutesInRange' = SUM(CASE WHEN d.IsHolidayFlag = 'N' AND t.IsBusinessMinuteFlag = 'Y' THEN 1 ELSE 0 END)
FROM    dbo.Incidents i
       INNER JOIN Dim_Date d
           ON d.CalendarDate BETWEEN i.CreatedOn AND i.ResolvedOn
       CROSS JOIN Dim_Time t
GROUP BY i.IncidentID

這在偽尺度上看起來很簡單:

Determine number of business days in the date range: D 

Determine number of Holidays in range H

return (D-H)*10*60

引用自:https://dba.stackexchange.com/questions/3608