Requête SQL pour le retour de 24 heures, toutes les heures comptent même quand les valeurs n'existent pas?

Vous le faire par la construction de la première le 23 heures de la table, la faisant une jointure externe à l'encontre de la table des transactions. J'utilise, pour les mêmes fins, d'une fonction à valeur de table:

create function tvfGetDay24Hours(@date datetime)
returns table
as return (
select dateadd(hour, number, cast(floor(cast(@date as float)) as datetime)) as StartHour
  , dateadd(hour, number+1, cast(floor(cast(@date as float)) as datetime)) as EndHour
from master.dbo.spt_values
where number < 24 and type = 'p');

Alors je peux utiliser la fonction de TABLE dans les requêtes qui ont besoin d'obtenir " par " l'heure de la base de données, même pour le manque d'intervalles de données:

select h.StartHour, t.TotalHourlyTransactions
from tvfGetDay24Hours(@StartDate) as h
outer apply (
  SELECT 
    COUNT(TransactionID) AS TotalHourlyTransactions
    FROM MyTransactions 
    WHERE TransactionTime BETWEEN h.StartHour and h.EndHour
    AND TerminalId = @TerminalID) as t
order by h.StartHour

Mis à jour

Exemple d'une TABLE qui retourne un délai de 24 heures entre l'arbitraire dates:

create function tvfGetAnyDayHours(@dateFrom datetime, @dateTo datetime)
returns table
as return (
select dateadd(hour, number, cast(floor(cast(@dateFrom as float)) as datetime)) as StartHour
  , dateadd(hour, number+1, cast(floor(cast(@dateFrom as float)) as datetime)) as EndHour
from master.dbo.spt_values
where type = 'p'
and number < datediff(hour,@dateFrom, @dateTo) + 24);

Noter que, depuis le maître.dbo.spt_values ne contient que 2048 nombres, la fonction ne fonctionnera pas entre les dates plus éloignées que 2048 heures.

Pour en revenir à l'aide de Remus' fonction d'origine, j'ai ré-utilisé dans un appel récursif et stocker les résultats dans une table temporaire:

DECLARE @count INT
DECLARE @NumDays INT
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @CurrentDay DATE

    DECLARE @tmp_Transactions TABLE 
    (
        StartHour DATETIME,
        TotalHourlyTransactions INT
    )   

SET @StartDate = '2000/02/10'
SET @EndDate = '2010/02/13'
SET @count = 0
SET @NumDays = DateDiff(Day, @StartDate, @EndDate)
WHILE @count < @NumDays 
    BEGIN
        SET @CurrentDay = DateAdd(Day, @count, @StartDate)
        INSERT INTO @tmp_Transactions (StartHour, TotalHourlyTransactions)
            SELECT  h.StartHour ,
                    t.TotalHourlyTransactions
            FROM    tvfGetDay24Hours(@CurrentDay) AS h
                    OUTER APPLY ( SELECT    COUNT(TransactionID) AS TotalHourlyTransactions
                                  FROM      [dbo].[TerminalTransactions]
                                  WHERE     TransactionTime BETWEEN h.StartHour AND h.EndHour
                                            AND TerminalId = 4
                                ) AS t
            ORDER BY h.StartHour
        SET @count = @Count + 1
    END 

SELECT *
FROM @tmp_Transactions

J'ai couru dans une version de ce problème avant. La suggestion qui a fonctionné le mieux était de l'installation d'une table (temporaire ou non) avec les heures de la journée, puis de faire une jointure externe à la table et de groupe par datepart ("h", timeOfRecord).

Je ne me souviens pas pourquoi, mais probablement en raison du manque de souplesse en raison de la nécessité de l'autre table, j'ai fini par utiliser une méthode où je groupe par quelque partie que je veux et de commande par le datetime, puis la boucle à travers et de remplir les espaces sont sautés avec un 0. Cette approche a bien fonctionné pour moi parce que je ne suis pas tributaire de la base de données à faire tout mon travail pour moi, et c'est aussi BEAUCOUP plus facile d'écrire un test automatisé pour elle.