代码之家 › 专栏 › 技术社区 › DRUIDRUID

从多行收集的已用时间总和

sql-server-2008 sql-server sql

-1

DRUIDRUID · 技术社区 · 6 年前

我试图展示我的团队的生产力与非生产力的百分比,这取决于他们一天中的工作类型。我可以用下面的代码成功地实现我正在寻找的格式,但是如果一个同事在一天内在两个不同的部门中具有生产力,则子查询将失败,因为它返回多行。

这就是我现在的工作

声明 @偏移int, @开始日期时间, @本地输入, @非时间(0) @生产时间(0) @总时间(0) 设置@offset=4 设置@start=dateadd(day,datediff(day,0,getdate()),-@offset) --所选日期范围内所有部门类别的总和选择代码=(deptcode+“”+opcode) ,timesum=right(convert(varchar(9),(sum(datediff(second,starttime,finishtime))/3600)),3)+':'+right('0'+convert(varchar(2),(sum(datediff(second,starttime,finishtime))/60)%60),2) ,单位=总和(单位) 从TimeLogNew 其中eventdate=@start and location=10,id=2372013 按事件日期、ID、DEPTCODE、操作码分组 --工作的非生产时间总和(单位总和为“0”视为非生产时间)--不包括“ll”(午餐时间) 设置@非= (选择nonprod=right(convert(varchar(9),(sum(datediff(second,'0:00:00',timesum))/3600)),3)+':'+right('0'+convert(varchar(2),(sum(datediff(second,'0:00:00',timesum))/60)%60),2) 从(选择 timesum=right(convert(varchar(9),(sum(datediff(second,m.starttime,m.finishtime))/3600)),3)+':'+right('0'+convert(varchar(2),(sum(datediff(second,m.starttime,m.finishtime))/60)%60),2) ,单位=总和(m.units) 从TimeLogNew M 其中m.eventdate=@start和opcode不在(‘ll’)中,m.location=10,m.id=2372013 按m.eventdate、m.id、m.deptcode和m.opcode分组 )t按单位分组,单位=0) 选择@non作为“非生产时间” --已工作的生产工时总和(单位总和大于“0”视为生产工时)--不包括“ll”(午餐时段) 设置@prod= (选择totalProd=right(convert(varchar(9),(sum(datediff(second,'0:00:00',timesum))/3600)),3)+':'+right('0'+convert(varchar(2),(sum(datediff(second,'0:00:00',timesum))/60)%60),2) 从(选择 timesum=right(convert(varchar(9),(sum(datediff(second,m.starttime,m.finishtime))/3600)),3)+':'+right('0'+convert(varchar(2),(sum(datediff(second,m.starttime,m.finishtime))/60)%60),2) ,单位=总和(m.units) 从TimeLogNew M 其中m.eventdate=@start和opcode不在(‘ll’)中,m.location=10,m.id=2372013 按m.eventdate、m.id、m.deptcode和m.opcode分组 )t按具有单位的单位分组>0) 选择@prod作为'prod_hours' --所有工作时间的总和——不包括'll'(午餐时间) 设置@total= (选择 totalTime_nolunch=right(convert(varchar(9),(sum(datediff(second,starttime,finishtime))/3600)),3)+':'+right('0'+convert(varchar(2),(sum(datediff(second,starttime,finishtime))/60)%,2) 从TimeLogNew 其中eventdate=@start,id=2372013,opcode不在(“ll”)中) 选择@total作为'sum\u hours' --生产率百分比选择 [prod%]=转换(varchar(30),(100*datediff(minute,'0:00:00',@prod)/datediff(minute,'0:00:00',@total))+'%' --非生产力百分比选择 [non prod%]=转换(varchar(30),(100*datediff(minute,'0:00:00',@non)/datediff(minute,'0:00:00',@total))+'%'


   结果显示。适用于7-2-18



如果部门类别codes>1以上,则子查询失败。我一直在尝试重新安排查询,以允许对等于0且大于0的和进行分组,但在这里遇到了困难。取消分组Sub中的UnitsColumn时,我没有得到正确的结果。


以下是一个以上生产操作的原始数据的外观。7月3日-18日
.

这就是我现在的工作


DECLARE 
    @Offset     INT         ,
    @Start      DATETIME    ,
    @Loc        INT         ,
    @NON        time(0)     ,
    @PROD       time(0)     ,
    @TOTAL      TIME(0)     
    SET @Offset    = 4
    SET @Start     = dateadd(day, datediff(day, 0, getdate()), -@Offset)

--SUM OF ALL DEPT CATEGORIES WORKED FOR SELECTED DATE RANGE 
select Codes = (DeptCode + ' ' + Opcode)
     , TimeSum = right(convert(varchar(9),(sum(datediff(second,StartTime,FinishTime)) / 3600 )),3) + ':' + right('0' + convert(varchar(2),(sum(datediff(second,StartTime,FinishTime)) / 60) % 60 ),2) 
     , Units = sum(units)
FROM TimeLogNEW 
where EventDate = @Start and Location = 10 and ID = 2372013 
group by EventDate, id, DeptCode, OpCode

--SUM OF NON PRODUCTIVE HOURS WORKED (HAVING A UNITS SUM OF '0' IS CONSIDERED NON PRODUCTIVE) --NOT INCLUDING 'LL' (WHICH IS A LUNCH PERIOD)
set @NON = 
(select NonProd = right(convert(varchar(9),(sum(datediff(second,'0:00:00',TimeSum)) / 3600 )),3) + ':' + right('0' + convert(varchar(2),(sum(datediff(second,'0:00:00',TimeSum)) / 60) % 60 ),2) 
from (select 
            TimeSum = right(convert(varchar(9),(sum(datediff(second,m.StartTime,m.FinishTime)) / 3600 )),3) + ':' + right('0' + convert(varchar(2),(sum(datediff(second,m.StartTime,m.FinishTime)) / 60) % 60 ),2) 
           ,Units = sum(m.units)
FROM TimeLogNEW m
where m.EventDate = @Start  and opCode Not in ('ll') and m.Location = 10 and m.ID = 2372013 
group by m.EventDate, m.id, m.DeptCode, m.OpCode
)t Group By Units Having Units = 0)
select @NON as 'NON-PROD_Hours'

--SUM OF PRODUCTIVE HOURS WORKED (HAVING A UNITS SUM OF MORE THAN '0' IS CONSIDERED PRODUCTIVE) --NOT INCLUDING 'LL' (WHICH IS A LUNCH PERIOD)
set @PROD = 
(select TotalProd = right(convert(varchar(9),(sum(datediff(second,'0:00:00',TimeSum)) / 3600 )),3) + ':' + right('0' + convert(varchar(2),(sum(datediff(second,'0:00:00',TimeSum)) / 60) % 60 ),2)
from (select 
            TimeSum = right(convert(varchar(9),(sum(datediff(second,m.StartTime,m.FinishTime)) / 3600 )),3) + ':' + right('0' + convert(varchar(2),(sum(datediff(second,m.StartTime,m.FinishTime)) / 60) % 60 ),2) 
           ,Units = sum(m.units)
FROM TimeLogNEW m
where m.EventDate = @Start  and opCode Not in ('ll') and m.Location = 10 and m.ID = 2372013 
group by m.EventDate, m.id, m.DeptCode, m.OpCode
)t Group By Units Having Units >0 )
select @PROD as 'PROD_Hours'

--SUM OF ALL HOURS WORKED --NOT INCLUDING 'LL' (WHICH IS A LUNCH PERIOD) 
set @TOTAL = 
(select 
TotalTime_NoLunch = right(convert(varchar(9),(sum(datediff(second,StartTime,FinishTime)) / 3600 )),3) + ':' + right('0' + convert(varchar(2),(sum(datediff(second,StartTime,FinishTime)) / 60) % 60 ),2) 
FROM TimeLogNEW
where EventDate = @Start  and ID = 2372013 and OpCode not in ('ll'))
select @TOTAL as 'SUM_Hours'

--PERCENTAGE OF PRODUCTIVITY 
select
[Prod%] =convert(varchar(30),(100 *datediff(minute,'0:00:00',@PROD)/datediff(minute,'0:00:00',@TOTAL) )) +'%'

--PERCENTAGE OF NON-PRODUCTIVITY
select
[NON Prod%] =convert(varchar(30),(100 *datediff(minute,'0:00:00',@NON)/datediff(minute,'0:00:00',@TOTAL) )) +'%'


结果显示。适用于7-2-18



如果超过1个部门类别codes大于0Units子查询失败。我一直在尝试重新安排查询,以允许对等于0且大于0的和进行分组,但在这里遇到了困难。取消分组时,我没有得到正确的结果单位子表中的列。

以下是一个以上生产操作的原始数据的外观。7月3日-18日

1 回复 | 直到 6 年前

Wolfgang Kais 6 年前

DECLARE
    @Offset     INT       ,
    @Start      DATETIME  ,
    @Loc        INT = 10  ,
    @NON        INT       ,
    @PROD       INT       ,
    @TOTAL      INT
    SET @Offset    = 4;
    SET @Start     = DATEADD(day, DATEDIFF(day, 0, GETDATE()), -@Offset);

--SUM OF ALL DEPT CATEGORIES WORKED FOR SELECTED DATE RANGE 
WITH 
  TimeLog (EventDate, [Location], ID, DeptCode, OpCode, Units, Seconds) AS (
    SELECT EventDate, [Location], ID, DeptCode, OpCode, SUM(Units)
      , SUM(DATEDIFF(second, StartTime, FinishTime))
    FROM TimeLogNEW
    GROUP BY EventDate, [Location], ID, DeptCode, OpCode
  )
SELECT
    Codes = (DeptCode + ' ' + Opcode)
  , TimeSum = CAST(DATEADD(second, Seconds, 0) AS time(0))
  , Units
FROM TimeLog
WHERE EventDate = @Start AND [Location] = @Loc AND ID = 2372013;

WITH
  TimeLog (EventDate, [Location], ID, DeptCode, OpCode, Units, Seconds) AS (
    SELECT EventDate, [Location], ID, DeptCode, OpCode, SUM(Units)
      , SUM(DATEDIFF(second, StartTime, FinishTime))
    FROM TimeLogNEW
    GROUP BY EventDate, [Location], ID, DeptCode, OpCode
  ),
  TimeSplit (EventDate, Loc, ID, DC, OC, Units, Seconds, NON_PROD, PROD) AS (
    SELECT EventDate, [Location], ID, DeptCode, OpCode, Units, Seconds
      , CASE WHEN Units = 0 THEN Seconds ELSE 0 END
      , CASE WHEN Units > 0 THEN Seconds ELSE 0 END
    FROM TimeLog
  )
SELECT @NON = SUM(NON_PROD), @PROD = SUM(PROD), @TOTAL = SUM(Seconds)
FROM TimeSplit
WHERE EventDate = @Start AND Loc = @Loc AND ID = 2372013 AND OC NOT IN ('LL');

-- RESULTS
SELECT
  CONVERT(nvarchar(30), @NON/3600) + RIGHT(CONVERT(nvarchar(8), DATEADD(second, @NON, 0), 108), 6) AS 'NON-PROD_Hours',
  CONVERT(nvarchar(30), @PROD/3600) + RIGHT(CONVERT(nvarchar(8), DATEADD(second, @PROD, 0), 108), 6) AS 'PROD_Hours',
  CONVERT(nvarchar(30), @TOTAL/3600) + RIGHT(CONVERT(nvarchar(8), DATEADD(second, @TOTAL, 0), 108), 6) AS 'SUM_Hours',
  CAST(100.0 * @PROD / @TOTAL AS varchar(30)) +'%' AS 'Prod%',
  CAST(100.0 * @NON / @TOTAL AS varchar(30)) +'%' AS 'NON Prod%';