Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help with a query for report Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 5:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 9:19 AM
Points: 1,016, Visits: 1,343
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__


Renuka__
Post #1434825
Posted Monday, March 25, 2013 5:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:10 AM
Points: 5,216, Visits: 5,108
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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1434839
Posted Monday, March 25, 2013 5:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 9:19 AM
Points: 1,016, Visits: 1,343
Hi Anthony....

Works as exactly how I wanted.
Many thanks...

Renuka__


Renuka__
Post #1434842
Posted Monday, March 25, 2013 6:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 2:51 AM
Points: 41, Visits: 227
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
Post #1434865
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse