March 25, 2013 at 5:04 am
Hi,
We have a table in the following format:
ServerName ServerFunctionBackupReqCode
Server1 IS0
Server2 Ops0
Server3 Ops2
Server4 Ops4
Server5 IS1
Server6 IS5
Server7 Ops0
Server8 IS2
Server9 Ops3
Server10 Ops3
The requirement is to generate a report in the following format:
BackupReqCodeCountOfISServers CountOfOpsServers
0 12
1 10
2 11
3 02
4 01
5 10
With the very limited TSQL knowledge I have, I am able to get either first two columns(BackupCode, CountofISServers) or the first and third (BackupCode and CountOfOpsServers) but not the all the three columns in one result set. We dont have any codes for serverfunction so only way to differentiate is 'WHERE ServerFunction Like '<>'. This is for a report, if it is not possible from TSQL, I am hoping to get any work around from SSRS (like joining two datasets etc.,).
Any help or suggestion would be greatly appreciated.
Thank you
Renuka__
[font="Verdana"]Renuka__[/font]
March 25, 2013 at 5:37 am
This work for you?
DECLARE @Temp TABLE (ServerName VARCHAR(10), ServerFunction VARCHAR(3), BackupReqCode TINYINT)
INSERT INTO @Temp VALUES
('Server1','IS',0),
('Server2','Ops',0),
('Server3','Ops',2),
('Server4','Ops',4),
('Server5','IS',1),
('Server6','IS',5),
('Server7','Ops',0),
('Server8','IS',2),
('Server9','Ops',3),
('Server10','Ops',3)
SELECT
BackupReqCode,
ISNULL(SUM(CASE WHEN ServerFunction = 'IS' THEN 1 END),0) AS 'IS',
ISNULL(SUM(CASE WHEN ServerFunction = 'OPS' THEN 1 END),0) AS 'OPS'
FROM
@Temp
GROUP BY
BackupReqCode
March 25, 2013 at 5:52 am
Hi Anthony....
Works as exactly how I wanted.
Many thanks... 🙂
Renuka__
[font="Verdana"]Renuka__[/font]
March 25, 2013 at 6:22 am
This query gives output as you required please try
SELECT temp.BackupReqCode,max(case WHEN temp.ServerFunction='IS' THEN temp.CountOfOpsServer ELSE 0 END ) as 'CountOfISServer',
max(case WHEN temp.ServerFunction='Ops' THEN temp.Countofserver ELSE 0 END ) as 'CountOfOpsServer' from
(SELECT BackupReqCode,ServerFunction,count(ServerName)as 'Countofserver'
from TabName
group by BackupReqCode,ServerFunction) temp
group by temp.BackupReqCode
Regards,
Arjun Singh Shekhawat
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply