|
我知道如何按小时销售,因为我是根据销售的实际时间而不是随着时间的推移而计算的。然而,鉴于以下数据,我试图找到按小时计算劳动力的最佳方法。% z0 @+ c* G B* n5 Q
EmployeeID InTime OutTime PayRate OTRate DTRate59:09:09:09:09:09:09:0 .0 05:17.0 10.75 16.13 21.56 33:45.0 54:58.0 10.75 16.13 21.56 59:255.0 24:07.0 10.75 16.13 21.56 55:185:18.0 29:35.0 10.75 16.13 21.56 59:50.0 02:17.0 10.75 16.13 21.57 .0 40:48.0 9.25 13.88 18.57 .0 58:41.0 9.25 13.88 18.513 .0 27:46. 9 133. 555:235:23 555:235.0 11:02.0 10.75 16.13 21.56 37:03.0 30:32.0 10.75 16.13 21.5我知道我最终会弄清楚的,但如果有人能给我一些指导来加快这个过程,防止我做一些太令人费解的事情,我会感激的。我需要使用这些信息来做一些更复杂的报告,但首先,我只是在寻找最简单的查询,这将告诉我雇主每小时花费多少劳动力。加班/加班可以随意忽略,因为我可以在找到另一部分后合并。+ S3 p* E4 k Q
编辑:InTime和OutTime实际上,字段是DateTimes …由于某种原因,当我复制/粘贴时,它会转换值
) ?; f# P8 z4 P, J5 b编辑:结果看起来像…% f! Y8 z$ t1 A; M
Hour Labor Cost1:00 $2:000 $3:00 $4:00 $5:00 $etc.$这是这个小时的劳动力成本。因此,我想做的第一件事就是创建一个包含24行的表。每小时一次。然后,在新表中添加小时字段进行输入/输出时间。想想这个连接表达式现在会是什么样子…
0 z& ~) g- y4 i( J1 R: {8 R5 S编辑:马克提醒我,我还需要同时显示日期和小时,这让我觉得我的24行表实际上需要24
6 l- z( g$ S X8 ^% h$ o*,但是我有很多天要查询。此外,我意识到每小时一次记录不能很好地工作,因为人们只能工作部分小时,所以我认为我需要每分钟记录一次,然后分组/和解这些结果,以获得准确的数据。
" a. {! i9 s% B/ |9 B: L
! Y9 @. V2 X9 I 解决方案: & _5 k, l$ P+ k6 n
当然,没有测试,但我认为这应该能够克服我所做的任何错别字:
& K: [, f6 g& ^( |-- 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, / 60) AS LaborCostFROM Time_cte times LEFT JOIN tblTimeSheet ts ON times.MyTime BETWEEN ts.InTime AND ts.OutTimeGROUP BY DATEADD(HOUR,DATEDIFF(HOUR,0,times.MyTime),0) |
|