TSQL

  • 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

  • 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

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

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