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

Question

Post reply

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

cs 29850

SSC Enthusiast

Points: 146
More actions
November 4, 2013 at 8:54 am

#282068

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

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

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

durai nagarajan SSCoach Points: 15778 More actions · Answer 1

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

cs 29850 SSC Enthusiast Points: 146 More actions · Answer 2

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?

durai nagarajan SSCoach Points: 15778 More actions · Answer 3

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

cs 29850 SSC Enthusiast Points: 146 More actions · Answer 4

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'

cs 29850 SSC Enthusiast Points: 146 More actions · Answer 5

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

cs 29850 SSC Enthusiast Points: 146 More actions · Answer 6

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

CGZ Mr or Mrs. 500 Points: 522 More actions · Answer 7

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!

cs 29850 SSC Enthusiast Points: 146 More actions · Answer 8

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?

CGZ Mr or Mrs. 500 Points: 522 More actions · Answer 9

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

)