Convert Seconds to Minutes and Count per minutes

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 2005 Forums

Transact-SQL (2005)

Convert Seconds to Minutes and Count per minutes

Author

Topic

nonsec
Starting Member

26 Posts

Posted - 2009-04-23 : 16:08:00

Hello Everyone,

I have a table I have some fields and I need to convert second to minutes and then count them per minute and grouping per hour then later per day.

Here is my table and fields,

Here is my query;

SELECT [ResolutionState]
,[TimeFromRaisedSeconds]
,[StateSetDateTime]

FROM [OperationsManagerDW].[Alert].[vAlertResolutionState]
where ResolutionState=255
Order by StateSetDateTime
GO

Query output;


ResolutionState	TimeFromRaisedSeconds	StateSetDateTime
255			176		2009-03-30 18:42:57.490
255			587		2009-03-30 18:44:48.800
255			288		2009-03-30 18:44:48.800
255			888		2009-03-30 18:44:48.803
255			887		2009-03-30 18:44:48.803
255			588		2009-03-30 18:44:48.803
255			1188		2009-03-30 18:44:48.807
255			1188		2009-03-30 18:44:48.807
255			95		2009-03-30 18:46:36.370
255			95		2009-03-30 18:46:36.370
255			16748	        2009-03-30 18:46:36.373

and I am trying to get results something like this, How can I get something like this as a first step?


Minutes       ResolutionStateCount
0			7
1			6
2			5
3			3
4			3
5			3
6			2
7			1
8			1		
9			1
10			1

Thank you so much,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 16:20:04

To get minutes from seconds; divide by 60.
To get hours from seconds; divide by 6300.
To get hours from minutes; divide by 60.

E 12°55'05.63"
N 56°04'39.26"

nonsec
Starting Member

26 Posts

Posted - 2009-04-23 : 18:06:32

Peso,

thank you very much. Here is my query;


SELECT     TimeFromRaisedSeconds / 60 AS Minutes, COUNT(*) AS Counts
FROM         Alert.vAlertResolutionState
WHERE     (ResolutionState = 255) AND (StateSetDateTime BETWEEN @startdate AND @enddate)
GROUP BY TimeFromRaisedSeconds / 60
ORDER BY Minutes

Here is output;


Minutes Counts
0	14
1	5
2	5
3	5
4	2
5	2
6	2
7	2
32	2
373	1
1832	1
2774	1
2915	1

Last thing How can I group by hour in the query? I want to get something like this;

Date				0 Min	1 Min	2 Min	3 Min  4min  5 Min 6 Min and.etc
2009-04-07 12:00:00.000		0	0	0	0	0	0	0
2009-04-08 01:00:00.000		0	0	0	0	0	0	0	
2009-04-09 02:00:00.000		11	5	6	2	7	5	4	
2009-04-13 03:00:00.000		3	5	3	2	1	1	4	
2009-04-14 04:00:00.000		2	2	2	2	0	0	0
2009-04-15 05:00:00.000		0	0	0	0	0	0	0
2009-04-16 06:00:00.000		7	6	5	3	0	0	0

Thank you, I appreciate it.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-04-23 : 19:55:29

quote:
Originally posted by Peso

To get minutes from seconds; divide by 60.
To get hours from seconds; divide by 6300.
To get hours from minutes; divide by 60.

E 12°55'05.63"
N 56°04'39.26"

Don't you mean 3600? ;)

Some days you're the dog, and some days you're the fire hydrant.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-24 : 00:44:10

Of course !
(slap on the forehead...)

I'll have to restrict access to this site for my Isabelle bot.

E 12°55'05.63"
N 56°04'39.26"

andrewz00
Starting Member

15 Posts

Posted - 2009-04-24 : 08:55:48

would converting the time work?

Select
cast(convert(varchar(10),StateSetDateTime,101)as datetime)

Group by
cast(convert(varchar(10),StateSetDateTime,101)as datetime)

nonsec
Starting Member

26 Posts

Posted - 2009-04-24 : 13:46:44

Hi andrewz00,

Thanks for replying. Here is my query,

SELECT CAST(CONVERT(varchar(10), StateSetDateTime, 101) AS datetime) AS datetime, TimeFromRaisedSeconds / 60 AS Minutes, COUNT(*) AS Counts
FROM Alert.vAlertResolutionState
WHERE (ResolutionState = 255) AND (StateSetDateTime BETWEEN @startdate AND @enddate)
GROUP BY CAST(CONVERT(varchar(10), StateSetDateTime, 101) AS datetime)

and I am getting following error; is something wrong with count?

Msg 8120, Level 16, State 1, Line 1
Column 'Alert.vAlertResolutionState.TimeFromRaisedSeconds' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources