create a view to show the backup status in every 10 mins

  • I have a question,

    my table have following data:

    userID, startTime, EndTime

    —————————————

    101, 04/11/2013 11:00:00, 04/11/2013 11:55:00

    102, 04/11/2013 11:00:00, 04/11/2013 11:24:00

    103, 04/11/2013 11:20:00, 04/11/2013 11:45:00

    104, 04/11/2013 11:30:00, 04/11/2013 11:35:00

    105, 04/11/2013 11:40:00, 04/11/2013 11:55:00

    can I use the view to show the backup status in every 10 mins?

    I wonder the result as following:

    time, count

    ——————————

    04/11/2013 11:00:00, 2

    04/11/2013 11:10:00, 2

    04/11/2013 11:20:00, 3

    04/11/2013 11:30:00, 3

    04/11/2013 11:40:00, 3

    04/11/2013 11:50:00, 2

    04/11/2013 12:00:00, 0

    04/11/2013 11:00:00 – 04/11/2013 11:09:59 have 2 jobs, 101 & 102

    04/11/2013 11:10:00 – 04/11/2013 11:19:59 have 2 jobs, 101 & 102

    04/11/2013 11:20:00 – 04/11/2013 11:29:59 have 3 jobs, 101 & 102 & 103

    04/11/2013 11:50:00 – 04/11/2013 11:59:59 have 2 jobs, 101 & 105

    04/11/2013 12:00:00 – 04/11/2013 12:09:59 have 0 job

    I wonder if you can give me a help……thanks a lot

  • can I use the view to show the backup status in every 10 mins?

    What do you mean by every 10 mins, view is not a job that executes automatically by a scheduler.

    Can you explain your requirement clearly

    Regards
    Durai Nagarajan

  • durai nagarajan (11/5/2013)


    can I use the view to show the backup status in every 10 mins?

    What do you mean by every 10 mins, view is not a job that executes automatically by a scheduler.

    Can you explain your requirement clearly

    I have the data like left side of the picture as below

    I would like to create the view like right side

    But my query can't work as right report

    I have 602 records in DB within 22:00:00 - 22:09:59

    but my report show "0" at 22:00:00

    Can you give me a hand?

  • Sure can help you, can you post your table structure.

    Just a doubt do you have backup every 1 sec? even then the time you mentioned is not matching, elobrate it.

    Regards
    Durai Nagarajan

  • durai nagarajan (11/5/2013)


    Sure can help you, can you post your table structure.

    Just a doubt do you have backup every 1 sec? even then the time you mentioned is not matching, elobrate it.

    With Numbers

    AS

    (

    SELECT 0 AS N

    UNION ALL

    SELECT N + 10

    FROM Numbers

    WHERE N + 10 < 60

    )

    SELECT DATEADD(minute,N,Start) AS [time],Cnt

    FROM (SELECT DISTINCT DATEADD(hh,DATEDIFF(hh,0,startTime),0) AS Start FROM dbo.FileBackup) t

    CROSS JOIN Numbers n

    CROSS APPLY (SELECT COUNT(jobid) AS Cnt

    FROM dbo.FileBackup

    WHERE DATEADD(minute,N,Start) BETWEEN StartTime AND EndTime

    )c

    WHERE Start >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1) + '19:00:00'

    AND Start < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) + '09:00:00'

  • durai nagarajan (11/5/2013)


    Sure can help you, can you post your table structure.

    Just a doubt do you have backup every 1 sec? even then the time you mentioned is not matching, elobrate it.

    The backup scheduled at. 22:00:00 and 02:00:00,

    But a lot of jobs, so the jobs. Maybe delay for few second

    We would like to know how jobs in process by every 10 minutes

    Even if the job run 1 minutes, we should count in 1 section (10 minutes).

    If the job over 10 minutes, we count it in (n\10 mins)+1 sections

    Such as the job started at 22:00:00, end at 22:11:00

    we will count this job process in 22:00:00 & 22:10:00

  • show it in graphic

    the excel data file on 4 Nov 2013 19:00:00 to 5 Nov 2013 09:00:00

    https://drive.google.com/file/d/0B-NM1bwOKut7TFJZWEc4ZGI1VDQ/edit?usp=sharing

  • I guess this solution works as you want.

    I used a temp table with the data from your first post.

    /***********************/

    /* TEST DATA */

    if OBJECT_ID('tempdb..#FileBackup') IS NOT NULL

    DROP TABLE #FileBackup

    CREATE TABLE #FileBackup

    (JobID INT IDENTITY,

    StartTime DATETIME,

    EndTime DATETIME)

    INSERT INTO #FileBackup

    SELECT '04/11/2013 11:00:00', '04/11/2013 11:55:00' UNION ALL

    SELECT '04/11/2013 11:00:00', '04/11/2013 11:24:00' UNION ALL

    SELECT '04/11/2013 11:20:00', '04/11/2013 11:45:00' UNION ALL

    SELECT '04/11/2013 11:30:00', '04/11/2013 11:35:00' UNION ALL

    SELECT '04/11/2013 11:40:00', '04/11/2013 11:55:00'

    -- SELECT * FROM #FileBackup

    /***********************/

    DECLARE @StartReportPeriod DATETIME,

    @EndReportPeriod DATETIME,

    @interval INT

    SET @StartReportPeriod = '20130411 10:00'

    SET @EndReportPeriod = '20130411 15:00'

    SET @interval = 10 /* minutes */

    WITH Intervals AS

    (SELECT @StartReportPeriod AS BeginInterval,

    DATEADD(MINUTE, @interval, @StartReportPeriod) AS EndInterval

    UNION ALL

    SELECT EndInterval,

    DATEADD(MINUTE, @interval, EndInterval)

    FROM Intervals

    WHERE DATEADD(MINUTE, @interval, EndInterval) <= @EndReportPeriod

    )

    SELECT

    CONVERT(char(5), i.BeginInterval, 108) AS BeginInterval,

    CONVERT(char(5), i.EndInterval, 108) AS EndInterval,

    c.Cnt

    FROM Intervals i

    CROSS APPLY

    (SELECT COUNT(t.JobID) AS Cnt

    FROM #FileBackup t

    WHERE (t.StartTime >= i.BeginInterval

    AND t.StartTime < i.EndInterval)

    OR (t.EndTime >= i.BeginInterval

    AND t.EndTime < i.EndInterval)

    OR (t.StartTime <= i.BeginInterval

    AND t.EndTime >= i.EndInterval)

    )c

    WHERE c.Cnt > 0 /* OPTIONAL: to hide intervals with no jobs. */

    /* RESULTS

    BeginInterval EndInterval Cnt

    ------------- ----------- -----------

    11:00 11:10 2

    11:10 11:20 2

    11:20 11:30 3

    11:30 11:40 3

    11:40 11:50 3

    11:50 12:00 2

    */

    Greetings to all!

  • CGZ (11/7/2013)


    I guess this solution works as you want.

    I used a temp table with the data from your first post.

    /***********************/

    /* TEST DATA */

    if OBJECT_ID('tempdb..#FileBackup') IS NOT NULL

    DROP TABLE #FileBackup

    CREATE TABLE #FileBackup

    (JobID INT IDENTITY,

    StartTime DATETIME,

    EndTime DATETIME)

    INSERT INTO #FileBackup

    SELECT '04/11/2013 11:00:00', '04/11/2013 11:55:00' UNION ALL

    SELECT '04/11/2013 11:00:00', '04/11/2013 11:24:00' UNION ALL

    SELECT '04/11/2013 11:20:00', '04/11/2013 11:45:00' UNION ALL

    SELECT '04/11/2013 11:30:00', '04/11/2013 11:35:00' UNION ALL

    SELECT '04/11/2013 11:40:00', '04/11/2013 11:55:00'

    -- SELECT * FROM #FileBackup

    /***********************/

    DECLARE @StartReportPeriod DATETIME,

    @EndReportPeriod DATETIME,

    @interval INT

    SET @StartReportPeriod = '20130411 10:00'

    SET @EndReportPeriod = '20130411 15:00'

    SET @interval = 10 /* minutes */

    WITH Intervals AS

    (SELECT @StartReportPeriod AS BeginInterval,

    DATEADD(MINUTE, @interval, @StartReportPeriod) AS EndInterval

    UNION ALL

    SELECT EndInterval,

    DATEADD(MINUTE, @interval, EndInterval)

    FROM Intervals

    WHERE DATEADD(MINUTE, @interval, EndInterval) <= @EndReportPeriod

    )

    SELECT

    CONVERT(char(5), i.BeginInterval, 108) AS BeginInterval,

    CONVERT(char(5), i.EndInterval, 108) AS EndInterval,

    c.Cnt

    FROM Intervals i

    CROSS APPLY

    (SELECT COUNT(t.JobID) AS Cnt

    FROM #FileBackup t

    WHERE (t.StartTime >= i.BeginInterval

    AND t.StartTime < i.EndInterval)

    OR (t.EndTime >= i.BeginInterval

    AND t.EndTime < i.EndInterval)

    OR (t.StartTime <= i.BeginInterval

    AND t.EndTime >= i.EndInterval)

    )c

    WHERE c.Cnt > 0 /* OPTIONAL: to hide intervals with no jobs. */

    /* RESULTS

    BeginInterval EndInterval Cnt

    ------------- ----------- -----------

    11:00 11:10 2

    11:10 11:20 2

    11:20 11:30 3

    11:30 11:40 3

    11:40 11:50 3

    11:50 12:00 2

    */

    Greetings to all!

    Sorry about that the DECLARE can't using in "VIEW"

    can other option?

  • Good point! I've replaced the variables with another CTE.

    I have also changed the code to prevent recursion by adding a Tally table so you can see the report for more than one day.

    Hope it helps!

    CREATE VIEW TestViewFileBackup

    AS

    WITH

    Variables AS

    (/* Change values as needed. */

    SELECT StartReportPeriod = CAST(DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1) + '19:00:00' AS DATETIME),

    EndReportPeriod = CAST(DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) + '09:00:00' AS DATETIME),

    interval = CAST(10 AS INT) /* minutes */

    ),

    Tally AS

    (SELECT TOP (SELECT CEILING(DATEDIFF(MINUTE, StartReportPeriod, EndReportPeriod) / interval) FROM Variables)

    N = ROW_NUMBER() OVER(ORDER BY (SELECT 1))

    FROM master.sys.columns A CROSS JOIN master.sys.columns B

    ),

    Intervals AS

    (SELECT DATEADD(MINUTE, (t.N - 1 ) * v.interval, v.StartReportPeriod) AS BeginInterval,

    DATEADD(MINUTE, t.N * v.interval, v.StartReportPeriod) AS EndInterval

    FROM Tally t

    CROSS JOIN Variables v

    WHERE DATEADD(MINUTE, t.N * v.interval, v.StartReportPeriod) <= v.EndReportPeriod

    )

    SELECT

    CONVERT(char(8), i.BeginInterval, 112) AS Date,

    CONVERT(char(5), i.BeginInterval, 108) AS BeginInterval,

    CONVERT(char(5), i.EndInterval, 108) AS EndInterval,

    c.Cnt

    FROM Intervals i

    CROSS APPLY

    (SELECT COUNT(t.JobID) AS Cnt

    FROM dbo.FileBackup t /* REPLACE WITH YOU REAL TABLE. */

    WHERE (t.StartTime >= i.BeginInterval

    AND t.StartTime < i.EndInterval)

    OR (t.EndTime >= i.BeginInterval

    AND t.EndTime < i.EndInterval)

    OR (t.StartTime <= i.BeginInterval

    AND t.EndTime >= i.EndInterval)

    )c

    WHERE c.Cnt > 0; /* OPTIONAL: to hide intervals with no jobs. */

    GO

  • Thanks a lot, the results is great.

    CGZ (11/7/2013)


    Good point! I've replaced the variables with another CTE.

    I have also changed the code to prevent recursion by adding a Tally table so you can see the report for more than one day.

    Hope it helps!

    CREATE VIEW TestViewFileBackup

    AS

    WITH

    Variables AS

    (/* Change values as needed. */

    SELECT StartReportPeriod = CAST(DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1) + '19:00:00' AS DATETIME),

    EndReportPeriod = CAST(DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) + '09:00:00' AS DATETIME),

    interval = CAST(10 AS INT) /* minutes */

    ),

    Tally AS

    (SELECT TOP (SELECT CEILING(DATEDIFF(MINUTE, StartReportPeriod, EndReportPeriod) / interval) FROM Variables)

    N = ROW_NUMBER() OVER(ORDER BY (SELECT 1))

    FROM master.sys.columns A CROSS JOIN master.sys.columns B

    ),

    Intervals AS

    (SELECT DATEADD(MINUTE, (t.N - 1 ) * v.interval, v.StartReportPeriod) AS BeginInterval,

    DATEADD(MINUTE, t.N * v.interval, v.StartReportPeriod) AS EndInterval

    FROM Tally t

    CROSS JOIN Variables v

    WHERE DATEADD(MINUTE, t.N * v.interval, v.StartReportPeriod) <= v.EndReportPeriod

    )

    SELECT

    CONVERT(char(8), i.BeginInterval, 112) AS Date,

    CONVERT(char(5), i.BeginInterval, 108) AS BeginInterval,

    CONVERT(char(5), i.EndInterval, 108) AS EndInterval,

    c.Cnt

    FROM Intervals i

    CROSS APPLY

    (SELECT COUNT(t.JobID) AS Cnt

    FROM dbo.FileBackup t /* REPLACE WITH YOU REAL TABLE. */

    WHERE (t.StartTime >= i.BeginInterval

    AND t.StartTime < i.EndInterval)

    OR (t.EndTime >= i.BeginInterval

    AND t.EndTime < i.EndInterval)

    OR (t.StartTime <= i.BeginInterval

    AND t.EndTime >= i.EndInterval)

    )c

    WHERE c.Cnt > 0; /* OPTIONAL: to hide intervals with no jobs. */

    GO

Viewing 11 posts - 1 through 10 (of 10 total)

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