TSQL

Question

TSQL

m.rajesh.uk

Hall of Fame

Points: 3653
More actions
May 23, 2016 at 6:28 am

#316725

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

We need DDL, sample data and expected results. For details on what to post, read the articles in my signature.

(Rude question, rude answer)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2