Compter le nombre de fois qu'une heure se produit dans une plage de dates

Je ne pouvais pas penser à un bon moyen d'exprimer cette question pour searchr correctement si elle a déjà été posée.

Je cherche un moyen dans SQL 2008 R2 de countr combien de fois 6pm se produit entre deux valeurs datetime.

Par exemple entre '2017-04-17 19:00:00' et '2017-04-19 17:00:00' 6pm se produit une seule fois même si les time couvrent 3 jours différents.

Entre '2017-04-17 18:00:00' et '2017-04-19 18:00:00' il se produit 3 fois tout en s'étendant sur 3 jours.

Heres une expression maquillée vraiment stupide de ce que je veux pour l'illustration.

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

Je vous remercie

Une requête simple à countr:

 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

Cela vous donnera chaque heure et le nombre d'occurrences:

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

Ou juste pour 18h:

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

 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

Essayez ci-dessous formule que j'ai essayé avec un scénario différent et cela fonctionne, laissez-moi savoir si je manque un scénario et ne fonctionne pas selon vos besoins.

 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

Essayez le script ci-dessous, en utilisant 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 )

Voici le nombre de tous les 6pm entre deux datetime:

 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

Voici la vue détaillée de 18h entre deux datetime:

 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

Ici donne le count entre toutes les plages de dates

 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