group by day-hour
/SQL/group by day-hour.sql
-- Demandes de synchro /heures (civs_d_creation) :
SELECT TOP 100
DATEADD(HOUR, DATEPART(HOUR, civs_d_creation), CAST(CAST(civs_d_creation AS DATE) AS datetime)) AS jjhh
, COUNT(*) AS nbCivs
FROM chrono_iresa_vers_sirocco civs WITH(NOLOCK)
WHERE 1=1
AND civs_table = 'DOSSIER'
--AND civs_d_traitement IS NULL
GROUP BY CAST(civs_d_creation AS DATE), DATEPART(HOUR, civs_d_creation)
ORDER BY jjhh DESC
-- Traitement /hh (civs_d_traitement) :
SELECT TOP 200
-- DATEADD(MINUTE, DATEPART(MINUTE, civs_d_traitement), -- si par minute
DATEADD(HOUR, DATEPART(HOUR, civs_d_traitement),
CAST(CAST(civs_d_traitement AS DATE) AS datetime)
)
-- )
AS jjhh
, COUNT(*) AS nbCivs
FROM chrono_iresa_vers_sirocco civs WITH(NOLOCK)
WHERE 1=1
AND civs_table = 'DOSSIER'
--AND civs_d_traitement IS NULL
--AND civs_d_traitement > DATEADD(MINUTE, -120, GETDATE())
GROUP BY CAST(civs_d_traitement AS DATE)
, DATEPART(HOUR, civs_d_traitement)
-- , DATEPART(MINUTE, civs_d_traitement) -- si par minute
ORDER BY jjhh