Question

Edit: See Calculate how many minutes used in a given hour for how I ultimately ended up solving this.

我知道如何做像每小时销售一样的事情,因为如果销售实际进行,我就算出销售,而不是随时间推移。然而,根据以下数据,我试图按小时计算劳动力的最佳方法。

EmployeeID  InTime   OutTime  PayRate  OTRate  DTRate
6           59:09.0  05:17.0  10.75    16.13   21.5
6           33:45.0  54:58.0  10.75    16.13   21.5
6           59:25.0  24:07.0  10.75    16.13   21.5
6           55:18.0  29:35.0  10.75    16.13   21.5
6           59:50.0  02:17.0  10.75    16.13   21.5
7           00:45.0  40:48.0  9.25     13.88   18.5
7           00:21.0  58:41.0  9.25     13.88   18.5
13          00:34.0  27:46.0  9        13.5    18
6           55:23.0  11:02.0  10.75    16.13   21.5
6           37:03.0  30:32.0  10.75    16.13   21.5

我知道,我终于看到了这段话,但是如果有人能够给我一些要点,以加快这一进程,让我不要做过多的 con,我肯定会对此表示赞赏。我需要用这一信息做一些更为复杂的报告,但首先要看一看最简单的询问,告诉我雇主每小时花多少劳动开支。感到可以随意忽略加班/双倍,因为我可以在我获得另一部分数字后再回来。

Edit: Intime and Outtime areas are actual Annual Times... for someêt it transformation the Value when Icop/pasted

Edit: Results would look like...

Hour   Labor Cost

1:00       $
2:00       $
3:00       $
4:00       $
5:00       $
etc.

在那里,这个小时的劳动开支是多少。因此,我想做的头一件事是建立一个有24个牢房的桌子,每小时一个。然后,在新桌旁边的休息时间将进入小时现场。只是想想一下加入这一表达方式,现在就想到......。

Edit: Mark reminded me that I will also need to show the date along with the hour which leads me to think that my 24 row table will actually need to be 24 * however many days I m querying over. Additionally I realized that 1 record per hour isn t going to work well because people work partial hours so I think I need to do a record for every minute and then group/sum over those results to get accurate data.

Answer 1

Untested of course, but I believe this should work minus whatever typos I ve made:

-- Assume that there is a tblNumbers table
--  that has been populated with at least the values 0 to 60.
-- First, we need to find the days for which there are time records.
; WITH Days_cte (MyDay) AS
(
    SELECT DISTINCT
        -- Strip off the time from the date.  
        DATEADD(DAY, DATEDIFF(DAY, 0, InTime), 0) AS MyDay
    FROM tblTimeSheet

    UNION

    SELECT DISTINCT
        -- Strip off the time from the date.  
        DATEADD(DAY, DATEDIFF(DAY, 0, OutTime), 0) AS MyDay
    FROM tblTimeSheet
),
-- Next, explode this into hours AND minutes for every day.
-- This cte will contain 1440 records for every day.
Times_cte (MyTime) AS
(
    SELECT
        DATEADD(MINUTE, minutes.Number, 
                DATEADD(HOUR, hours.Number, days.MyDay)) AS MyTime
    FROM
        Days_cte days JOIN
        tblNumbers hours ON hours.Number < 24 JOIN
        tblNumbers minutes ON minutes.Number < 60 LEFT JOIN
)
-- Now, determine which of these minutes 
--  falls between an employee s in and out time.
SELECT
    -- Strip off the minutes, leaving only the hour.
    DATEADD(HOUR, DATEDIFF(HOUR, 0, times.MyTime), 0) AS [Hour],
    -- Divide by 60, since the aggregation is done by the minute.
    SUM(ISNULL(ts.PayRate, 0) / 60) AS LaborCost
FROM    
    Time_cte times LEFT JOIN
    tblTimeSheet ts ON times.MyTime BETWEEN ts.InTime AND ts.OutTime
GROUP BY
    DATEADD(HOUR, DATEDIFF(HOUR, 0, times.MyTime), 0)

Edit: See Calculate how many minutes used in a given hour for how I ultimately ended up solving this.

友情链接