SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Create a view to show the status in every 10 mins
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mattluk
Starting Member

15 Posts

Posted - 11/04/2013 :  11:03:41  Show Profile  Reply with Quote
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

Edited by - mattluk on 11/04/2013 11:04:26

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/04/2013 :  13:55:51  Show Profile  Reply with Quote

;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
Go to Top of Page

mattluk
Starting Member

15 Posts

Posted - 11/05/2013 :  00:24:36  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/05/2013 :  00:54:01  Show Profile  Reply with Quote
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
Go to Top of Page

mattluk
Starting Member

15 Posts

Posted - 11/05/2013 :  01:49:19  Show Profile  Reply with Quote
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:
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/05/2013 :  02:31:19  Show Profile  Reply with Quote
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
Go to Top of Page

mattluk
Starting Member

15 Posts

Posted - 11/05/2013 :  05:08:45  Show Profile  Reply with Quote
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 @.@
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/05/2013 :  06:01:04  Show Profile  Reply with Quote
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
Go to Top of Page

mattluk
Starting Member

15 Posts

Posted - 11/05/2013 :  06:29:20  Show Profile  Reply with Quote
I have a lot of data like left side of above pictures.....

Your query format is right....
But only can't count the data....
Go to Top of Page

mattluk
Starting Member

15 Posts

Posted - 11/05/2013 :  06:41:20  Show Profile  Reply with Quote
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

Edited by - mattluk on 11/05/2013 06:42:47
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/05/2013 :  08:22:31  Show Profile  Reply with Quote
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
Go to Top of Page

mattluk
Starting Member

15 Posts

Posted - 11/05/2013 :  09:10:45  Show Profile  Reply with Quote
22:00:00-22:09:59 count in 22:00:00 group
22:10:00-22:19:59 count in 22:10:00 group
Go to Top of Page

mattluk
Starting Member

15 Posts

Posted - 11/05/2013 :  10:27:27  Show Profile  Reply with Quote
show it in graphic

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/05/2013 :  10:33:15  Show Profile  Reply with Quote
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
Go to Top of Page

mattluk
Starting Member

15 Posts

Posted - 11/05/2013 :  13:10:19  Show Profile  Reply with Quote
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?

Edited by - mattluk on 11/05/2013 13:15:17
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/05/2013 :  13:58:43  Show Profile  Reply with Quote

;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)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mattluk
Starting Member

15 Posts

Posted - 11/06/2013 :  02:09:42  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/06/2013 :  04:11:19  Show Profile  Reply with Quote

;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)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mattluk
Starting Member

15 Posts

Posted - 11/06/2013 :  19:38:24  Show Profile  Reply with Quote
still not working................

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/07/2013 :  03:19:15  Show Profile  Reply with Quote

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




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mattluk
Starting Member

15 Posts

Posted - 11/08/2013 :  10:05:08  Show Profile  Reply with Quote
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



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000