J'ai effectivement une table avec deux champs importants. ReadyDate et CompleteDate.
Je veux que ma sortie me donne 24 colonnes, correspondant à une heure de la journée, chacune me donnant le nombre d'enregistrements qui étaient encore "ouverts" (définis comme ReadyDate avant la fin de l'heure mais CompleteDate après la fin d'une heure) pendant cette heure d'horloge.
L'astuce est donc qu'un enregistrement peut apparaître dans zéro ou plusieurs colonnes. Quelque chose qui était ReadyDate à 6h05 et CompleteDate 9h17 apparaîtrait dans la colonne 6: 00a, 7: 00a et 8: 00a.
Donc, si le tableau contient les entrées suivantes
< pre> XXXJe m'attendrais à ce que ma sortie contienne 24 colonnes avec les nombres suivants 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 1, 1, 2, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0
Je ne sais pas comment procéder. Toutes les informations seraient appréciées
3 Réponses :
Ce n'est pas le plus joli mais cela fonctionne comme vous l'avez expliqué. J'adorerais voir une solution plus élégante si quelqu'un en a une.
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 0 0 0 0 0 0 0 1 1 0 1 1 2 1 1 0 0 0 0 0 0 0 0 0
Sortie:
DECLARE @t TABLE
(
date1 DATETIME NOT NULL,
date2 DATETIME NOT NULL
);
INSERT INTO @t
(
date1,
date2
)
VALUES
('10-14-2019 06:05', '10-14-2019 06:10'),
('10-14-2019 07:12', '10-14-2019 09:30'),
('10-14-2019 10:02', '10-14-2019 13:55'),
('10-14-2019 12:50', '10-14-2019 15:30');
SELECT SUM(t.[0]) AS [0],
SUM(t.[1]) AS [1],
SUM(t.[2]) AS [2],
SUM(t.[3]) AS [3],
SUM(t.[4]) AS [4],
SUM(t.[5]) AS [5],
SUM(t.[6]) AS [6],
SUM(t.[7]) AS [7],
SUM(t.[8]) AS [8],
SUM(t.[9]) AS [9],
SUM(t.[10]) AS [10],
SUM(t.[11]) AS [11],
SUM(t.[12]) AS [12],
SUM(t.[13]) AS [13],
SUM(t.[14]) AS [14],
SUM(t.[15]) AS [15],
SUM(t.[16]) AS [16],
SUM(t.[17]) AS [17],
SUM(t.[18]) AS [18],
SUM(t.[19]) AS [19],
SUM(t.[20]) AS [20],
SUM(t.[21]) AS [21],
SUM(t.[22]) AS [22],
SUM(t.[23]) AS [23]
FROM
(
SELECT id,
date1,
date2,
CASE
WHEN 0
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [0],
CASE
WHEN 1
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [1],
CASE
WHEN 2
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [2],
CASE
WHEN 3
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [3],
CASE
WHEN 4
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [4],
CASE
WHEN 5
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [5],
CASE
WHEN 6
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [6],
CASE
WHEN 7
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [7],
CASE
WHEN 8
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [8],
CASE
WHEN 9
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [9],
CASE
WHEN 10
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [10],
CASE
WHEN 11
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [11],
CASE
WHEN 12
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [12],
CASE
WHEN 13
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [13],
CASE
WHEN 14
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [14],
CASE
WHEN 15
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [15],
CASE
WHEN 16
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [16],
CASE
WHEN 17
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [17],
CASE
WHEN 18
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [18],
CASE
WHEN 19
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [19],
CASE
WHEN 20
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [20],
CASE
WHEN 21
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [21],
CASE
WHEN 22
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [22],
CASE
WHEN 23
BETWEEN DATEPART(HOUR, date1) AND DATEPART(HOUR, date2) - 1 THEN
1
ELSE
0
END AS [23]
FROM @t
) t;
Le problème avec ceci est que si les heures passent au-dessus de minuit, cela ne fonctionnera pas comme prévu. Votre ensemble de données aurait-il des résultats comme celui-ci?
Voici une option PIVOT qui utilise un tableau de pointage ad-hoc. Le le report de minuit est pris en compte. Vous ne savez pas si vous vouliez des lignes par date. Sinon, ce serait une petite question à supprimer. Exemple Retours ;with cte1 as (
Select Date = convert(date,D)
,Item = datepart(hour,D)
,Value = 1
From YourTable A
Cross Apply (
Select Top (DateDiff(HOUR,ReadyDate,CompleteDate)+0)
D=DateAdd(HOUR,-1+Row_Number() Over (Order By (Select Null)),ReadyDate)
From master..spt_values n1,master..spt_values n2
) B
), cte2 as (
Select Date
,B.Item
,B.Value
From (Select distinct Date from cte1 ) a
Cross Join (Select Top 24 Item=-1+Row_Number() Over (Order By (Select NULL)),Value=0 From master..spt_values n1 ) B
)
Select *
From (Select * from cte1
Union All
Select * from cte2
) src
Pivot (sum(Value) for Item in ([0] ,[1] ,[2] ,[3] ,[4] ,[5] ,[6] ,[7] ,[8] ,[9] ,[10],[11],
[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]) ) pvt
Votre solution est géniale et je l'apprécie beaucoup. Je travaille toujours sur l'exemple et je pense en comprendre l'essentiel. J'ai dû ajouter ma propre table remplie de valeurs de 0 à 23 car je n'ai pas accès à la base de données principale. Cependant, cela fonctionne extrêmement bien. J'essaye de comprendre la première croix s'appliquent. D'une manière ou d'une autre, il utilise la table des valeurs deux fois. J'ai divisé cette partie en une sélection distincte pour la comprendre. Cela fonctionne, mais je ne suis pas compétitif dans la logique. Pouvez-vous expliquer?
@AbrahamLincoln Heureux que cela ait aidé. CTE1 «étendra» chaque ligne via un tableau de pointage / nombres ad hoc. CTE2 créera une liste factice de toutes les combinaisons de dates et d'heures ... c'est une petite astuce pour éviter les valeurs NULL dans le PIVOT final. La sélection finale est un simple PIVOT de cte1 et cte2
@AbrahamLincoln Je devrais ajouter ... BIEN FAIT en prenant l'initiative de le décomposer, c'est ainsi que nous apprenons :)
Il y a quelques questions sur l'échantillon de données que vous avez fourni. Il n'est pas tout à fait clair comment chaque heure est sélectionnée. Une heure est-elle sélectionnée si le nombre de minutes pendant lesquelles un élément était actif est supérieur à un certain nombre? Il y a également un problème possible avec CompletedDate le jour suivant. Dans les deux cas, voici un autre exemple de comment y parvenir. Vous devrez bricoler la logique des heures pour obtenir la réponse dont vous avez besoin.
DECLARE @data Table
(
id int identity(1,1)
,ReadyDate datetime
,CompletedDate datetime
,foo varchar(500)
)
INSERT INTO @data
VALUES
('10-14-2019 06:05','10-14-2019 06:10','(this will populate no columns)')
,('10-14-2019 07:12','10-14-2019 09:30','(this will populate the 7:00 hour and 8:00 hour)')
,('10-14-2019 10:02','10-14-2019 13:55','(this will populate the 10:00 hour, 11:00, 12:00)')
,('10-14-2019 12:50','10-14-2019 15:30','(this will populate the 12:00, 13:00, 14:00)')
;
;WITH Numbers AS
(
SELECT 1 AS Number
UNION ALL
SELECT Number+1
FROM Numbers
WHERE Number < 24
)
,src
as (
Select *,
flag = CASE WHEN Number >= DATEPART(hour, ReadyDate) AND Number < DATEPART(hour, ReadyDate) + DateDiff(hour, ReadyDate, CompletedDate) THEN 1 ELSE 0 END
from @Data
CROSS APPLY Numbers
)
Select *
from src
PIVOT
(
max(flag) for number in ([1],[2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24])
) as ptable
Marquez votre question avec la base de données que vous utilisez.