Create a view to show the status in every 10 mins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Create a view to show the status in every 10 mins

Author

Topic

mattluk
Starting Member

15 Posts

Posted - 2013-11-04 : 11:03:41

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 13:55:51

[code]
;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 Table) t
CROSS JOIN Numbers n
CROSS APPLY (SELECT COUNT(userid) AS Cnt
FROM Table
WHERE DATEADD(minute,N,Start) BETWEEN StartTime AND EndTime
)c
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mattluk
Starting Member

15 Posts

Posted - 2013-11-05 : 00:24:36

quote:
Originally posted by visakh16

;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 Table) t
CROSS JOIN Numbers n
CROSS APPLY (SELECT COUNT(userid) AS Cnt
FROM Table
WHERE DATEADD(minute,N,Start) BETWEEN StartTime AND EndTime
)c

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

it is work!!!
thank for your help,

but can I only extract yesterday data?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 00:54:01

you can


;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 Table) t
CROSS JOIN Numbers n
CROSS APPLY (SELECT COUNT(userid) AS Cnt
             FROM Table
             WHERE DATEADD(minute,N,Start) BETWEEN StartTime AND EndTime
             )c
WHERE Start >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1)
AND Start < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mattluk
Starting Member

15 Posts

Posted - 2013-11-05 : 01:49:19

quote:

Originally posted by visakh16

you can


;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 Table) t
CROSS JOIN Numbers n
CROSS APPLY (SELECT COUNT(userid) AS Cnt
             FROM Table
             WHERE DATEADD(minute,N,Start) BETWEEN StartTime AND EndTime
             )c
WHERE Start >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1)
AND Start < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I'm very sorry to told you that the query didn't work,

I have "602" lines record between 22:00 - 22:09
but the query show 22:00 is "0" line

ref:

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 02:31:19

when you say 22:00 then it means 22:00:00 so any record with time part (example 22:04 ) ahead of it wont get included in it. only ones having startdate 22:00 will get included. Seeing your data I think its working as expected.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mattluk
Starting Member

15 Posts

Posted - 2013-11-05 : 05:08:45

Sorry about that,
I can't clear to classify what I need,

Would you mind change the query for this case?

Because my boss need this view @.@

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 06:01:04

what do you mean by that? If you dont know the requirement how do you expect me to pick it up.
First explain what you want for sample data above as your expected result with clear explanation. Then somebody might be able to help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mattluk
Starting Member

15 Posts

Posted - 2013-11-05 : 06:29:20

I have a lot of data like left side of above pictures.....

Your query format is right....
But only can't count the data....

mattluk
Starting Member

15 Posts

Posted - 2013-11-05 : 06:41:20

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 08:22:31

quote:
Originally posted by mattluk

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

thats what it does currently
job starting at 22:04 will not be counted for 22:00 which is what you're asking for.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mattluk
Starting Member

15 Posts

Posted - 2013-11-05 : 09:10:45

22:00:00-22:09:59 count in 22:00:00 group
22:10:00-22:19:59 count in 22:10:00 group

mattluk
Starting Member

15 Posts

Posted - 2013-11-05 : 10:27:27

show it in graphic

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 10:33:15

quote:
Originally posted by mattluk

22:00:00-22:09:59 count in 22:00:00 group
22:10:00-22:19:59 count in 22:10:00 group

it will work the same way only
if you see some difference in behavior it may be that you'll have some milliseconds part also coming

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mattluk
Starting Member

15 Posts

Posted - 2013-11-05 : 13:10:19

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

this the logs excel file in 4 Nov 2013 22:00:00 to 5 Nov 2013 9:00:00

I export the from DB,

only have JobID , startTime, endTime

can I count in every 10 minuts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 13:58:43

[code]
;With Numbers
AS
(
SELECT 0 AS N
UNION ALL
SELECT N + 10
FROM Numbers
WHERE N + 10 < 1440
)
SELECT DATEADD(minute,N,Start) AS [time],COALESCE(Cnt,0) AS Cnt
FROM (SELECT DISTINCT DATEADD(dd,DATEDIFF(dd,0,startTime),0) AS Start FROM Table) t
CROSS JOIN Numbers n
OUTER APPLY (SELECT COUNT(jobid) AS Cnt
FROM Table
WHERE (StartTime >= Start AND StartTime < DATEADD(minute,10,Start))
OR (EndTime >= Start AND EndTime < DATEADD(minute,10,Start))
)c
WHERE Start >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1)
AND Start < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mattluk
Starting Member

15 Posts

Posted - 2013-11-06 : 02:09:42

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

I got this massage when I run your query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-06 : 04:11:19

[code]
;With Numbers
AS
(
SELECT 0 AS N
UNION ALL
SELECT N + 10
FROM Numbers
WHERE N + 10 < 1440
)
SELECT DATEADD(minute,N,Start) AS [time],COALESCE(Cnt,0) AS Cnt
FROM (SELECT DISTINCT DATEADD(dd,DATEDIFF(dd,0,startTime),0) AS Start FROM Table) t
CROSS JOIN Numbers n
OUTER APPLY (SELECT COUNT(jobid) AS Cnt
FROM Table
WHERE (StartTime >= Start AND StartTime < DATEADD(minute,10,Start))
OR (EndTime >= Start AND EndTime < DATEADD(minute,10,Start))
)c
WHERE Start >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1)
AND Start < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
OPTION (MAXRECURSION 0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mattluk
Starting Member

15 Posts

Posted - 2013-11-06 : 19:38:24

still not working................

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 03:19:15

[code]
declare @t table
(userID int,
startTime datetime,
EndTime datetime
)
insert @t
values
(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')
SELECT DISTINCT DATEADD(dd,DATEDIFF(dd,0,startTime),0) AS Start FROM @t

;With Numbers
AS
(
SELECT 0 AS N
UNION ALL
SELECT N + 10
FROM Numbers
WHERE N + 10 < 1440
)
SELECT DATEADD(minute,N,Start) AS [time],COALESCE(Cnt,0) AS Cnt
FROM (SELECT DISTINCT DATEADD(dd,DATEDIFF(dd,0,startTime),0) AS Start FROM @t) t
CROSS JOIN Numbers n
CROSS APPLY (SELECT COUNT(userID) AS Cnt
FROM @t
WHERE DATEADD(minute,N,Start) BETWEEN startTime AND EndTime
)c
WHERE Cnt > 0
OPTION (MAXRECURSION 0)

output
-------------------------------------------
time Cnt
-------------------------------------------
2013-04-11 11:00:00.000 2
2013-04-11 11:10:00.000 2
2013-04-11 11:20:00.000 3
2013-04-11 11:30:00.000 3
2013-04-11 11:40:00.000 3
2013-04-11 11:50:00.000 2

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mattluk
Starting Member

15 Posts

Posted - 2013-11-08 : 10:05:08

sorry about that, I still have problem on your query.
Maybe my problem.
Thanks a lots for your help!!

I find the code from other forum.
share your you....


WITH 
Variables AS
( /* Change values as needed. */
SELECT StartReportPeriod = CAST(DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-4) + '22:00:00' AS DATETIME),
EndReportPeriod = CAST(DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-3) + '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(19), i.BeginInterval, 120) AS BeginInterval,
c.Cnt
FROM Intervals i
CROSS APPLY 
(SELECT COUNT(t.JobID) AS Cnt
FROM dbo.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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources