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

SQL Server将DATEDIFF的累积和转换为百分比

sql-server-2012 sql-server sql

Matt · 技术社区 · 7 年前

ID     IIn                       IOut
AB123  2015-11-06 15:24:44.057   2015-11-14 01:00:00.000
QA565  2015-10-27 20:12:19.753   2015-11-06 03:00:00.000
UN555  2015-12-29 06:29:23.417   2016-01-03 08:00:00.000
LG602  2015-08-07 16:52:13.573   2015-08-11 03:00:00.000

等等等等

然后我使用DATEDIFF得到天数:

SELECT ID, DATEDIFF(hour, IIn, IOut)/24.0 IDays
FROM TimeTable

这给了我:

ID     IDays
AB123  7.416666
QA565  9.291666
UN555  5.083333
LG602  3.458333

我要的是数一数 ID IDay 的(向下舍入),从最低 艾迪 是这样的:

ID     IDays  IDaysPer
LG602  3      12.5
UN555  5      33.33
AB123  7      62.49
QA565  9      100

3 回复 | 直到 7 年前

Matt 7 年前

您可以使用几个窗口聚合来实现这一点,为了方便起见,将原始查询放在CTE中(子查询也可以工作):

declare @timeTable table (ID char(5) not null, IIn datetime not null,
                          IOut datetime not null)
insert into @timeTable(ID,IIn,IOut) values
('AB123','2015-11-06T15:24:44.057','2015-11-14T01:00:00.000'),
('QA565','2015-10-27T20:12:19.753','2015-11-06T03:00:00.000'),
('UN555','2015-12-29T06:29:23.417','2016-01-03T08:00:00.000'),
('LG602','2015-08-07T16:52:13.573','2015-08-11T03:00:00.000')

;With Diffs as (
    SELECT ID, DATEDIFF(hour, IIn, IOut)/24.0 IDays
    FROM @timeTable
)
select
    *,
    (
      SUM(IDays) OVER (ORDER BY IDays, ID)
      /
      SUM(IDays) OVER ()
    ) * 100 as IDaysPer
from
    Diffs
order by IDays

ID    IDays                                   IDaysPer
----- --------------------------------------- ---------------------------------------
LG602 3.458333                                13.696300
UN555 5.083333                                33.828300
AB123 7.416666                                63.201300
QA565 9.291666                                100.000000

serge 7 年前

考虑时间表已经有数据了

WITH t1 (ID, IDays)
AS (
    SELECT ID, DATEDIFF(hour, IIn, IOut) / 24.0 AS IDays
    FROM TimeTable
)
SELECT 
    ID, FLOOR(IDays), 
    (FLOOR(IDays) / (SELECT SUM(FLOOR(IDays)) FROM t1 t2 WHERE t1.IDays <= t2.IDays)) * 100.0 AS IDaysPer
FROM t1
ORDER BY 2 ASC

WhoamI 7 年前

给你:输出与你的匹配。。。

        create table #TEMp
(ID VARCHAR(100)
,IIn datetime
,IOut datetime
)


insert into #temp(ID,IIn,IOut) values
('AB123','2015-11-06T15:24:44.057','2015-11-14T01:00:00.000'),
('QA565','2015-10-27T20:12:19.753','2015-11-06T03:00:00.000'),
('UN555','2015-12-29T06:29:23.417','2016-01-03T08:00:00.000'),
('LG602','2015-08-07T16:52:13.573','2015-08-11T03:00:00.000')

select ID,IDays AS Idays,ROUND(CAST(SUM(IDays) OVER(ORDER BY IDays) AS FLOAT)/CAST(SUM(IDays)OVER() AS FLOAT) * 100,2) AS IdaysPer
from
(
select *,ROUND(DATEDIFF(hour, IIn, IOut)/24,0) IDays
from #TEMP
)T