Need help with a query for report

  • 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]

  • 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

  • Hi Anthony....

    Works as exactly how I wanted.

    Many thanks... 🙂

    Renuka__

    [font="Verdana"]Renuka__[/font]

  • 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