none
how to create dashboard total Clients & Non clients

Odpovědi

  • Hi,

    Try this:

    select count(v_R_System.resourceID) AS 'Total Clients', count(v_R_System.Client0) AS 'Clients',
    count(v_R_System.resourceID) - count(v_R_System.Client0) AS 'Non-Clients'
    from v_R_System

    Regards,
    Jörgen


    -- My System Center blog ccmexec.com -- Twitter @ccmexec

    6. března 2012 8:51
  • you can use the following query:-

    select sub.AssignedSite,        sum(sub.cnt ) 'Total Count',
            SUM(CASE SUB.ACTIVE0 when 1 then SUB.cnt END) AS 'Total Active Clients',
            SUM(CASE SUB.ACTIVE0 when 0 then SUB.cnt END) AS 'Total Inactive Clients',
            SUM(CASE SUB.obsolete0 when 1 then SUB.cnt END) AS 'Total Obsolete Clients'
            ,ROUND((CAST(SUM(CASE SUB.ACTIVE0 when 0 then SUB.cnt END) AS float) / SUM(sub.Cnt)) * 100,2) AS '% of Inactive Client Count'
            ,ROUND((CAST(SUM(CASE SUB.obsolete0 when 1 then SUB.cnt END) AS float) / SUM(sub.Cnt)) * 100,2) AS '% of Obsolete Client Count'
    FROM
        (select sit.sms_assigned_sites0 AssignedSite,
                sys.active0,sys.obsolete0 ,
                COUNT(*) cnt
      from v_R_System sys
      join v_RA_System_SMSAssignedSites sit  on sys.resourceID=sit.resourceID
        and (sys.Active0 is not null and sys.Obsolete0 is not null)
    group by sit.sms_assigned_sites0,sys.active0,sys.obsolete0
    )sub
    group by sub.AssignedSite

    13. března 2012 14:21
  • select sub.AssignedSite,        sum(sub.cnt ) 'Total Count',
            SUM(CASE SUB.ACTIVE0 when 1 then SUB.cnt END) AS 'Total Active Clients',
            SUM(CASE SUB.ACTIVE0 when 0 then SUB.cnt END) AS 'Total Inactive Clients',
            ROUND((CAST(SUM(CASE SUB.ACTIVE0 when 0 then SUB.cnt END) AS float) / SUM(sub.Cnt)) * 100,2) AS '% of Inactive Client Count'
    FROM
        (select sit.sms_assigned_sites0 AssignedSite,
                sys.active0,sys.obsolete0 ,
                COUNT(*) cnt
      from v_R_System sys
      join v_RA_System_SMSAssignedSites sit  on sys.resourceID=sit.resourceID
        and (sys.Active0 is not null and sys.Obsolete0 is not null)
    group by sit.sms_assigned_sites0,sys.active0,sys.obsolete0
    )sub
    group by sub.AssignedSite
    19. března 2012 15:08

Všechny reakce