Need help with the Select statement.

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

Transact-SQL (2008)

Need help with the Select statement.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2012-03-26 : 13:12:09

[code]I need to return the total count of each operation and within each operation return the duration it takes. Please
see the business rules and desire output below. SQL 2008

Thank you in advance for your help.

IF OBJECT_ID('Tempdb.dbo.#t', 'u') IS NOT NULL
DROP TABLE #t

CREATE TABLE #t
(
P_KEY INT NULL,
Operation INT NULL,
MiLSecs INT NULL,
App INT NULL
)
go

INSERT #t VALUES ( 81083301, 10101, 2438, 2), ( 81083323, 10101, 3235, 2), ( 81083434, 10101, 2734, 3),
( 81083305, 10713, 0, 1);
GO

SELECT P_KEY,
Operation,
(MiLSecs/1000.0) AS 'Sec'
FROM #t
GO

-- Business rule: return each operation for duration it takes. GROUP BY @Operation = 10100.
-- If (MiLSecs/1000.0) AS 'Sec' values in this case 2.438 and 2.734 then count as 2 because
-- it fall under 2 - 3 Sec ranges, values 3-4 Sec count = 1.

-- Result want:
P_KEY Operation TotalCount 0-0 Sec 1 - 2 Sec 2-3 Sec 3-4 Sec >=5 Seconds.
----------- ----------- ------ ---- -------- ---- ----- --------------
81083301 10101 3 0 0 2 1 0
81083305 10713 1 0 0 0 0 0[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-26 : 13:25:11

[code]
SELECT MIN(P_KEY) AS MinKey,Operation,COUNT(P_KEY) AS TotalCount,
COUNT( CASE WHEN MiLSecs <= 1000 THEN 1 END) AS [0-0 Sec],
COUNT( CASE WHEN MiLSecs > 1000 AND MiLSecs <= 2000 THEN 1 END) AS [1-2 Sec],
COUNT( CASE WHEN MiLSecs > 2000 AND MiLSecs <= 3000 THEN 1 END) AS [2-3 Sec],
COUNT( CASE WHEN MiLSecs > 3000 AND MiLSecs <= 4000 THEN 1 END) AS [3-4 Sec],
COUNT( CASE WHEN MiLSecs >4000 THEN 1 END) AS [>=5 Sec]
FROM table
GROUP BY Operation
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NguyenL71
Posting Yak Master

228 Posts

Posted - 2012-03-26 : 13:47:59

Thanks so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-26 : 14:47:36

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources