m.rajesh.uk (5/23/2016)

---

Hi All,

I need a query to get the data on hourly wise. Below query gives data on day basis.

SELECT

stat_date AS "date",

DATEPART(YEAR,stat_date) AS year,

SUBSTRING(CONVERT(VARCHAR(10),stat_date,102),0,8) AS year_month,

DATEPART(MONTH,stat_date) AS month,

DATEPART(WEEK,stat_date) AS week,

MAX(nb_error) AS nb_error,

MAX(nb_synchro) AS nb_synchro,

MAX(avg_synchr_dur) AS avg_synchr_dur

FROM

(SELECT

CAST(CONVERT(char(11), [time], 113) AS DATETIME) AS stat_date,

COUNT(*) AS nb_error,

0 AS nb_synchro,

0 AS avg_synchr_dur

FROM

[UFC_Stats].[dbo].[repl_error] as re

WHERE

error_text LIKE 'The Merge Agent failed after detecting that retention-based%'

and [time] >= DATEADD(day,-20, GETDATE())

GROUP BY

CAST(CONVERT(char(11), [time], 113) AS DATETIME)

UNION ALL

SELECT

start_date AS synchro_date,

0 AS nb_error,

COUNT(session_id) AS nb_synchro,

SUM(duration_sec) / COUNT(session_id) AS avg_synchr_dur

FROM

[UFC_Stats].[dbo].[repl_sess]

WHERE

publication LIKE 'UFC_Market-HH-Synchronization%'

AND start_date >= DATEADD(day,-20, GETDATE())

GROUP BY

start_date

UNION ALL

SELECT

CAST(CONVERT(char(11), t1.start_time, 113) AS DATETIME) AS synchro_date,

0 AS nb_error,

COUNT(session_id) AS nb_synchro,

SUM(duration) / COUNT(session_id) AS avg_synchr_dur

FROM

distribution.dbo.MSmerge_sessions t1 INNER JOIN distribution.dbo.MSmerge_agents t2 on t1.agent_id = t2.id

WHERE

(publication LIKE 'UFC_Market-HH-Synch%') AND

CAST(CONVERT(char(11), t1.start_time, 113) AS DATETIME) = CAST(CONVERT(char(11), GETDATE(), 113) AS DATETIME)

GROUP BY

CAST(CONVERT(char(11), t1.start_time, 113) AS DATETIME)) T1

GROUP BY

stat_date

ORDER BY

1 DESC