当然未经测试，但是我认为这应该可以克服我所做的任何错别字：
-- 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)
602392714
清零编程群