April 27, 2005 at 4:29 am
Hi
I have the following table(sse below) which monitors the status of a line. It is updated every minute. If the reasonid is null then the line is running otherwise it is stopped(due to reasonid).
I want to be able to group this information into 5 minute buckets
i.e at
8:00 the line is running for 3 min and down for 2 min
8:05 the line is down for 4 min and running for 1 min
8:10 the line is running for 5 min
etc..
ID DateTime ReasonID
9 27/04/2005 10:52:00 NULL
10 27/04/2005 10:53:00 20206
11 27/04/2005 10:54:00 20206
12 27/04/2005 10:55:00 20206
13 27/04/2005 10:56:00 20212
15 27/04/2005 10:58:00 NULL
16 27/04/2005 10:59:01 NULL
17 27/04/2005 11:00:00 NULL
18 27/04/2005 11:01:00 NULL
20 27/04/2005 11:03:00 NULL
21 27/04/2005 11:04:00 NULL
What is the best way to go about doing this?
Thnaks in advance
Shane
April 27, 2005 at 4:44 am
Not entirely sure if I understand, but this should give you some ideas to go on:
CREATE TABLE #time
(
ID INT IDENTITY
, dt DATETIME
, ReasonID INT
)
INSERT INTO #time
SELECT '20050427 10:50:00', NULL
UNION ALL
SELECT '20050427 10:51:00', NULL
UNION ALL
SELECT '20050427 10:52:00', NULL
UNION ALL
SELECT '20050427 10:53:00', 20206
UNION ALL
SELECT '20050427 10:54:00', 20206
UNION ALL
SELECT '20050427 10:55:00', NULL
UNION ALL
SELECT '20050427 10:56:00', 20212
SELECT
DATEADD(minute, DATEDIFF(minute,'19000101',dt) / 5 * 5,'19000101')
, COUNT(*)-SUM(CASE WHEN ReasonID IS NULL THEN 0 ELSE 1 END) AS Running
, COUNT(*)-SUM(CASE WHEN ReasonID IS NOT NULL THEN 0 ELSE 1 END) AS Down
FROM
#time
GROUP BY
DATEADD(minute,DATEDIFF(minute, '19000101',dt) / 5 * 5,'19000101')
DROP TABLE #time
Running Down
------------------------------------------------------ ----------- -----------
2005-04-27 10:50:00.000 3 2
2005-04-27 10:55:00.000 1 1
(2 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 27, 2005 at 4:57 am
Thanks a million Frank
That is exactly what i was looking for.
My SQL is not the strongest but could you tell me(or point me to a site) what the signifacnce of '19000101' in the above statment is?
Thanks once again,
Shane
April 27, 2005 at 5:42 am
The '19000101' is just an adjustment to SQL Server's base date. This is possibly best explained with an example:
DECLARE @dt DATETIME
SET @dt = GETDATE()
SELECT
DATEDIFF(minute,0,@dt) Min_Between_BaseDate_And_Now
, DATEDIFF(minute,'19000101',@dt) Same_As_1
, DATEDIFF(minute,'19000101',@dt) / 5 * 5 Int_Div_to_Adjust_to_5_Minutes
, DATEADD(minute, DATEDIFF(minute,0,@dt) / 5 * 5,0) Putting_all_2gether
Min_Between_BaseDate_And_Now Same_As_1 Int_Div_to_Adjust_to_5_Minutes Putting_all_2gether
---------------------------- ----------- ------------------------------ -------------------------
55393285 55393285 55393285 2005-04-27 13:25:00.000
(1 row(s) affected)
The DATEDIFF returns the difference in minutes between the Server's base date and now. The division / 5 and multiplication * 5 is to get rid of these annoying minutes between every 5 minutes. In the example here it doesn't get obvious, because coincidentally the time was 13:30 anyway. And that return value is added again back to the Server's base date, that is transform into a valid DATETIME value.
HTH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 27, 2005 at 8:26 am
Thanks Frank
I have a better understanding of it now
Shane
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply