March 25, 2013 at 5:04 am
Hi,
We have a table in the following format:
ServerName ServerFunction BackupReqCode
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
The requirement is to generate a report in the following format:
BackupReqCode CountOfISServers CountOfOpsServers
0 1 2
1 1 0
2 1 1
3 0 2
4 0 1
5 1 0
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