SQL计算8个工作日中一半时间的Datediff

Question

我需要比较两个日期并返回其间的天数。以下是一个表格示例：

+----+--------+-------------------------+-------------------------+
| id | userid |       datestarted       |     datefinished        |
+----+--------+-------------------------+-------------------------+
|    |        |                         |                         |
|  1 |     23 | 2014-03-25 09:05:00.000 | 2014-03-25 12:15:00.000 |
|  2 |     43 | 2014-03-25 09:05:00.000 | 2014-03-25 12:15:00.000 |
|  3 |     23 | 2014-03-31 09:05:00.000 | 2014-03-31 12:15:00.000 |
|  4 |     12 | 2014-03-25 09:05:00.000 | 2014-03-26 12:15:00.000 |
+----+--------+-------------------------+-------------------------+

在前3种情况下，我们有同一天，只是时间不匹配。

Datestarted = 2014-03-25 09:05:00.000
Datefinished = 2014-03-25 12:15:00.000

我们只输入hours和minutes。

到目前为止，我们只需要将difference显示为whole number，而不显示decimal points，并且是这样做的：

DATEDIFF(carsharing.datestarted, carsharing.datefinished)

但现在，我们必须将日期之间的difference显示为0,5 day，如果它是less而不是4,5 hours。如果差值为greater，则应保持为1 day。

在表中更完整的最后一种情况下，我们还应该比较并显示两个不同日期之间的差异

Datestarted = 2014-03-25 09:05:00.000
Datefinished = 2014-03-26 12:15:00.000

这里的结果应该是1,5 days

Answer 1

我相信这就是你想要的-这将使一天中4.5小时以下的差异四舍五入到0.5，而超过这一点的所有事情都将持续一整天：

Declare @StartDate  DateTime = '2014-03-25 09:05:00.000',
        @EndDate    DateTime = '2014-03-26 12:15:00.000'
;With TotalHours As
(
    Select  DateDiff(Minute, @StartDate, @EndDate) / 60.0 As TotalHours
)
Select  Case 
            When TotalHours % 24 = 0
                Then Floor(TotalHours / 24)
            When TotalHours % 24 < 4.5 
                Then Floor(TotalHours / 24) + 0.5 
            Else Floor(TotalHours / 24) + 1.0
        End As Days
From    TotalHours

Answer 2

您可以尝试此查询。它得到以分钟为单位的差值，并将其乘以2，得到0.5天的范围。然后，在计算"天花板"值之前，它将其除以24小时和60分钟。一旦你有了它，它可以再除以2。

当该值超过4.5*24*60（4.5天，以分钟为单位）时，它只需要除以24和60。

查询：

Select id, userid, datestarted, datefinished
    , Days = Case When DATEDIFF(minute, datestarted, datefinished) > 4.5*60*24      
        then DATEDIFF(minute, datestarted, datefinished) / 24 / 60
        else CEILING(((2.0*DATEDIFF(minute, datestarted, datefinished)) / 24 / 60)) / 2
    end
From @dates

输出：

id  userid  datestarted                 datefinished                Days
1   23      2014-03-25 09:05:00.000     2014-03-25 12:15:00.000     0.500000
2   43      2014-03-25 09:05:00.000     2014-03-25 12:15:00.000     0.500000
3   23      2014-03-31 09:05:00.000     2014-03-31 12:15:00.000     0.500000
4   12      2014-03-25 09:05:00.000     2014-03-26 12:15:00.000     1.500000
5   12      2014-03-25 09:05:00.000     2014-03-29 12:15:00.000     4.500000
6   12      2014-03-25 09:05:00.000     2014-03-29 22:15:00.000     4.000000

样本数据

declare @dates table(id int, userid int, datestarted datetime, datefinished datetime);
insert into @dates(id, userid, datestarted, datefinished) values
(1, 23, '2014-03-25 09:05:00.000', '2014-03-25 12:15:00.000')
, (2, 43, '2014-03-25 09:05:00.000', '2014-03-25 12:15:00.000')
, (3, 23, '2014-03-31 09:05:00.000', '2014-03-31 12:15:00.000')
, (4, 12, '2014-03-25 09:05:00.000', '2014-03-26 12:15:00.000')
, (5, 12, '2014-03-25 09:05:00.000', '2014-03-29 12:15:00.000')
, (6, 12, '2014-03-25 09:05:00.000', '2014-03-29 22:15:00.000')

Answer 3

DECLARE 
    @StartDate datetime = '2014-03-25 09:05:00.000'
    ,@EndDate datetime = '2014-03-26 09:05:00.000'
;WITH d AS (SELECT DATEDIFF(d,@StartDate,@EndDate) Dys) 
,h AS (SELECT DATEDIFF(hh,@StartDate,@EndDate) Hrs)
SELECT d.Dys + CASE WHEN (h.Hrs - d.Dys*24) = 0 THEN 0 ELSE CASE WHEN (h.Hrs - d.Dys*24) < 4.5 THEN 0.5 ELSE 1 END END
FROM d,h