计算在日期范围内出现的次数

I could not think of a good way to phrase this question to search properly if its already been asked.

我想不出一个好的方法来说明这个问题是否已经被问到是否正确搜索。

I'm looking for a way in SQL 2008 R2 to count how many times 6pm occurs between two datetime values.

我正在寻找SQL 2008 R2中的一种方法来计算两个日期时间值之间下午6点的次数。

For example between '2017-04-17 19:00:00' and '2017-04-19 17:00:00' 6pm only occurs once even though the times span 3 different days.

例如，在'2017-04-17 19:00:00'和'2017-04-19 17:00:00'之间的下午6点仅发生一次，即使时间跨越3个不同的日子。

Between '2017-04-17 18:00:00' and '2017-04-19 18:00:00' it occurs 3 times whilst also spanning 3 days.

在'2017-04-17 18:00:00'和'2017-04-19 18:00:00'之间，它发生3次，同时也跨越3天。

Heres a really silly made up expression of what I want for illustration.

这是一个非常愚蠢的表达我想要的插图。

timecount(hh, 6, min(datefield), max(datefield))

Thank you

谢谢

7 个解决方案

#1

A simple query to count:

一个简单的查询计数：

DECLARE @StartDate datetime = '2017-04-17 18:00:00'

DECLARE @EndDate datetime = '2017-04-19 18:00:00'

SELECT 
   CASE 
      WHEN CAST(@StartDate AS time) <= '18:00' AND CAST(@EndDate AS time) >= '18:00' 
             THEN datediff(day, @StartDate, @EndDate) + 1
      WHEN CAST(@StartDate AS time) <= '18:00' AND CAST(@EndDate AS time) < '18:00' 
             THEN datediff(day, @StartDate, @EndDate)      
      WHEN CAST(@StartDate AS time) > '18:00' AND CAST(@EndDate AS time) >= '18:00' 
             THEN datediff(day, @StartDate, @EndDate)
      ELSE datediff(day, @StartDate, @EndDate) - 1
   END AS TotalCount

#2

This will give you each hour and the number of occurences:

这将给你每小时和出现的次数：

select datepart(hh, DateColumn) as TheHours, count(*) as occurs
from MyTable
where DateColumn between @SomeDate and @SomeOtherDate
group by datepart(hh, DateColumn)

Or just for 6pm:

或者只是下午6点：

select count(*)
from MyTable
where datepart(hh, DateColumn) = 18
and DateColumn between @SomeDate and @SomeOtherDate

#3

DECLARE 
    @Time time = '18:00',
    @From datetime = '2017-04-17 18:00:00',
    @To datetime = '2017-04-19 18:00:00'


SELECT 
    CASE 
        -- Same date
        WHEN DATEDIFF(DAY, @From, @To) = 0 THEN 
            CASE WHEN CAST(CAST(@From AS date) AS datetime) + @Time BETWEEN @From AND @To THEN 1 ELSE 0 END
        -- Not same date
        WHEN @From <= @To THEN
            CASE WHEN @Time >= CAST(@From AS time) THEN 1 ELSE 0 END 
            + DATEDIFF(DAY, @From, @To) - 1
            + CASE WHEN @Time <= CAST(@To AS time) THEN 1 ELSE 0 END 
        -- Invalid range
        ELSE 0
    END AS CountOfTime

#4

Try below formula I have tried with different scenario and it works, let me know if I miss any scenario and not work as per your requirement.

尝试下面的公式，我尝试了不同的场景，它的工作原理，让我知道如果我错过任何场景，不按你的要求工作。

DECLARE @firstDate Datetime='17-Apr-2017 17:00:00'
DECLARE @secondDate Datetime='17-Apr-2017 18:59:00'


SELECT
    CASE WHEN DATEDIFF(day,@firstDate,@secondDate)=0
    THEN IIF(CAST(@firstDate AS TIME) <='18:00' AND DATEPART(hh,@secondDate) >=18,1,0)
    ELSE 
        CASE WHEN 
            (
                CAST(@firstDate AS TIME) <='18:00' AND CAST(@secondDate AS TIME) <'18:00' 
                OR
                CAST(@firstDate AS TIME) >'18:00' AND CAST(@secondDate AS TIME) >='18:00' 
            )
            THEN DATEDIFF(day,@firstDate,@secondDate)
            WHEN CAST(@firstDate AS TIME) <='18:00' AND CAST(@secondDate AS TIME) >='18:00' THEN DATEDIFF(day,@firstDate,@secondDate)+1 
            ELSE DATEDIFF(day,@firstDate,@secondDate)-1
            END
    END AS TotalCount

#5

Try the below script, using CTE

使用CTE尝试以下脚本

DECLARE @F_DATE     AS DATETIME =   '2017-04-17 19:00:00'
        ,@T_DATE    AS DATETIME =   '2017-04-19 17:00:00'


;WITH CTE
AS  (
    SELECT  (CASE WHEN DATEPART(HH,@F_DATE) <= 18
                THEN @F_DATE 
                ELSE (CASE WHEN  DATEDIFF(DAY,@F_DATE,@T_DATE) > 0 
                        THEN DATEADD(DAY,1,@F_DATE) END)
             END)   AS  CDATE

    UNION ALL

    SELECT  DATEADD(DAY,1,CDATE)
    FROM    CTE
    WHERE   DATEADD(DAY,1,CDATE)    <=  @T_DATE
)

SELECT COUNT(CDATE) DATE_COUNT 
FROM CTE
OPTION ( MAXRECURSION 0 )

#6

Here's the count of every 6pm between two datetime:

这是两个日期时间之间每下午6点的计数：

DECLARE @StartDate datetime
DECLARE @EndDate datetime
set @StartDate = '2017-04-17 19:00:00'
set @EndDate = '2017-04-19 17:00:00'

;WITH cte1 (S) AS (
SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (S)
),
cte2 (S) AS (SELECT 1 FROM cte1 AS cte1 CROSS JOIN cte1 AS cte2),
cte3 (S) AS (SELECT 1 FROM cte1 AS cte1 CROSS JOIN cte2 AS cte2)

select count(datepart(hour,result)) as count from
(SELECT TOP (DATEDIFF(hour, @StartDate, @EndDate) + 1)
        result = DATEADD(hour, ROW_NUMBER() OVER(ORDER BY S) - 1, @StartDate)
FROM cte3) as res where datepart(hour,result) = 18

Here's the detailed view of 6pm between two datetime:

这是两个日期时间之间的下午6点的详细视图：

DECLARE @StartDate datetime
DECLARE @EndDate datetime
set @StartDate = '2017-04-17 19:00:00'
set @EndDate = '2017-04-19 17:00:00'

;WITH cte1 (S) AS (
SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (S)
),
cte2 (S) AS (SELECT 1 FROM cte1 AS cte1 CROSS JOIN cte1 AS cte2),
cte3 (S) AS (SELECT 1 FROM cte1 AS cte1 CROSS JOIN cte2 AS cte2)

select result,datepart(hour,result)  from
(SELECT TOP (DATEDIFF(hour, @StartDate, @EndDate) + 1)
        result = DATEADD(hour, ROW_NUMBER() OVER(ORDER BY S) - 1, @StartDate)
FROM cte3) as res where datepart(hour,result) = 18

#7

Here gives the count between any date ranges

这里给出了任何日期范围之间的计数

declare @time datetime='06:00:00'

declare @startDate datetime='04/20/2017 05:00:00'

declare @enddate datetime='04/21/2017 05:00:00'

SELECT 
  case 
    WHEN datediff(ss,@time, convert(time(0),@startDate)) <=0  and   datediff(ss,@time, convert(time(0),@enddate)) >=0

     THEN datediff(dd,@startDate,@enddate) +1 
WHEN   (datediff(ss,@time, convert(time(0),@startDate)) <=0  and 
datediff(ss,@time, convert(time(0),@enddate)) <=0

        OR
        datediff(ss,@time, convert(time(0),@startDate))> 0  and 

datediff(ss,@time, convert(time(0),@enddate)) >0

        OR

        datediff(ss,@time, convert(time(0),@startDate))> 0  and datediff(ss,@time, convert(time(0),@enddate)) <=0
        )
then  datediff(dd,@startDate,@enddate) 
ELSE  
datediff(dd,@startDate,@enddate)-1
END