sql calculate, count, grouping

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)

sql calculate, count, grouping

Author

Topic

nonsec
Starting Member

26 Posts

Posted - 2009-04-15 : 17:44:21

Hello Everyone,

I have table field names and statement I need to get an output but I don`t have any idea how I can achieve this goal. Looks like so complex for me. Any input greatly appreciated really. Thank you so much in advance.

This application database is System Center Operation Manager, I am trying to get How Many Alert closed ( ResolutionState=255) between 0 Minutes to 1 Minutes
1 Minutes to 2 Minutes
2 Minutes to 3 Minutes
3 Minutes to 4 Minutes
and like this Sample output is below.

here is my basic query;

select ResolutionState, TimeAdded, TimeResolved
From Alertview
Where ResolutionState=255

Here is the sample output I have to get,

Time to resolve by Time of Day Summary

0 mins to resolve 749 messages
1 mins to resolve 334 messages
2 mins to resolve 159 messages
3 mins to resolve 121 messages
4+ mins to resolve 503 messages
-- 4 mins to resolve 74 messages
-- 5 mins to resolve 71 messages
-- 6 mins to resolve 28 messages
-- 7 mins to resolve 45 messages
-- 8 mins to resolve 41 messages
-- 9 mins to resolve 24 messages
-- 10 mins to resolve 20 messages
-- 11 mins to resolve 13 messages
-- 12 mins to resolve 6 messages
-- 13 mins to resolve 13 messages
-- 14 mins to resolve 8 messages
-- 15 mins to resolve 12 messages
-- 16 mins to resolve 5 messages
-- 17 mins to resolve 5 messages
-- 18 mins to resolve 7 messages
-- 19 mins to resolve 3 messages
-- 20 mins to resolve 17 messages
-- 21 mins to resolve 5 messages
-- 22 mins to resolve 2 messages
-- 23 mins to resolve 7 messages
-- 24 mins to resolve 4 messages
-- 26 mins to resolve 3 messages
-- 27 mins to resolve 5 messages
-- 29 mins to resolve 1 messages
-- 30 mins to resolve 8 messages
-- 31 mins to resolve 4 messages
-- 32 mins to resolve 5 messages
-- 34 mins to resolve 1 messages
-- 37 mins to resolve 3 messages
-- 38 mins to resolve 4 messages
-- 40 mins to resolve 5 messages
-- 44 mins to resolve 5 messages
-- 47 mins to resolve 1 messages
-- 49 mins to resolve 1 messages
-- 50 mins to resolve 3 messages
-- 51 mins to resolve 1 messages
-- 57 mins to resolve 1 messages
-- 58 mins to resolve 1 messages
-- 59 mins to resolve 1 messages
-- 60 mins to resolve 2 messages
-- 62 mins to resolve 1 messages
-- 64 mins to resolve 1 messages
-- 65 mins to resolve 6 messages
-- 66 mins to resolve 5 messages
-- 67 mins to resolve 4 messages
-- 68 mins to resolve 1 messages
-- 69 mins to resolve 3 messages
-- 70 mins to resolve 1 messages
-- 71 mins to resolve 1 messages
-- 72 mins to resolve 1 messages
-- 73 mins to resolve 1 messages
-- 92 mins to resolve 1 messages
-- 95 mins to resolve 1 messages
-- 100 mins to resolve 1 messages
-- 105 mins to resolve 1 messages
-- 118 mins to resolve 1 messages
-- 130 mins to resolve 2 messages
-- 140 mins to resolve 1 messages
-- 208 mins to resolve 1 messages
-- 232 mins to resolve 1 messages
-- 304 mins to resolve 1 messages
-- 397 mins to resolve 1 messages
-- 434 mins to resolve 1 messages

Total Messages: 1866

Thank you So much Again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 17:47:14

[code]SELECT DATEDIFF(MINUTE, TimeAdded, TimeResolved),
COUNT(*)
FROM Alertview
WHERE ResolutionState = 255
GROUP BY DATEDIFF(MINUTE, TimeAdded, TimeResolved)
ORDER BY DATEDIFF(MINUTE, TimeAdded, TimeResolved)[/code]

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

nonsec
Starting Member

26 Posts

Posted - 2009-04-16 : 10:06:34

Hi Peso,

Thank you it is really helpful,

Here is your query output,


Time to Resolve    NumMessages
0			7
1			6
2			5
3			3
4			3
5			3
6			2
7			1
8			1		
9			1
10			1

Is it possible to aggregate ( count ) after 4 minutes as a 4+ minutes or something like that. Actually I am trying to achieve something like this,


Date	0 Min	1 Min	2 Min	3 Min	4+ Min
1-Feb	1115	229	93	51	242
2-Feb	1591	477	203	115	445
3-Feb	1976	384	123	60	125
4-Feb	1096	352	146	78	311
5-Feb	1933	447	143	57	165
6-Feb	1377	258	102	44	116
7-Feb	941	282	125	47	86

How should I get results something like that?

Thank you very much,
Really appreciate it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 11:23:23

do you mean this?


SELECT		DATEADD(dd,DATEDIFF(dd,0, TimeAdded),0) AS Date,
                COUNT(CASE WHEN DATEDIFF(MINUTE, TimeAdded, TimeResolved) = 0 THEN 1 ELSE NULL END) AS [0 Min],
COUNT(CASE WHEN DATEDIFF(MINUTE, TimeAdded, TimeResolved) = 1 THEN 1 ELSE NULL END) AS [1 Min],
COUNT(CASE WHEN DATEDIFF(MINUTE, TimeAdded, TimeResolved) = 2 THEN 1 ELSE NULL END) AS [2 Min],
COUNT(CASE WHEN DATEDIFF(MINUTE, TimeAdded, TimeResolved) = 3 THEN 1 ELSE NULL END) AS [3 Min],
COUNT(CASE WHEN DATEDIFF(MINUTE, TimeAdded, TimeResolved) > 3 THEN 1 ELSE NULL END) AS [4+ Min]
FROM		Alertview
WHERE		ResolutionState = 255
GROUP BY	DATEADD(dd,DATEDIFF(dd,0, TimeAdded),0)
ORDER BY	DATEADD(dd,DATEDIFF(dd,0, TimeAdded),0)

nonsec
Starting Member

26 Posts

Posted - 2009-04-16 : 14:44:22

Visakh16,

thank you very much, this helped a lot thank you guys for your times. one more thing,
I just realized that I have a table that keeps second, I think I need to convert second to minute.
Here is what looks like,


SELECT [ResolutionState]
      ,[TimeFromRaisedSeconds]
      ,[StateSetDateTime]
     
  FROM [OperationsManagerDW].[Alert].[vAlertResolutionState]
  where ResolutionState=255
  Order by StateSetDateTime

Here is 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

How can get same results like this?


Date	0 Min	1 Min	2 Min	3 Min	4+ Min
1-Feb	1115	229	93	51	242
2-Feb	1591	477	203	115	445
3-Feb	1976	384	123	60	125
4-Feb	1096	352	146	78	311
5-Feb	1933	447	143	57	165
6-Feb	1377	258	102	44	116
7-Feb	941	282	125	47	86

If you guys give me an example I will be happy.

Thank again.
Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources