none
Dúvida Calculo em SQL RRS feed

  • Pergunta

  • Galera, Primeiramente desculpa sou novo em SQL ... talvez esteja fazendo tudo errado ..

    O caso é o seguinte estou fazendo um calculo baseado nas tabelas do SCCM para obter um relatório gerencial onde devo ter o resultado abaixo por exemplo.

    |All Clients|Not Compliant|%Of not Not Compliant|

    |     79      |       5           |             6,3%             |

    Na minha query eu consegui obter os dois primeiros resultados, mas não consigo utilizar eles para realizar o calculo de porcentagem, segue abaixo a query ... caso alquem possa ajudar e me dar uma luz .... Muito Obrigado desde já.

    SELECT	
    
    (SELECT	COUNT(DISTINCT SYS.Netbios_Name0) AS 'Expr2'
    
    	FROM	v_R_System AS SYS
    			INNER JOIN v_ClientCollectionMembers AS CCM ON SYS.ResourceID = CCM.ResourceID
    			
    	WHERE	(CCM.CollectionID IN ('Z000007B', 'Z0000078')) AND 
    			(SYS.Resource_Domain_OR_Workgr0 IN (@Domain)) AND (LEFT(SYS.AD_Site_Name0, 3) IN (@Site))) AS 'AllClients',
    
    
    (SELECT	COUNT(DISTINCT SYS.Netbios_Name0) AS 'Expr1'
    
    	FROM	v_UpdateComplianceStatus AS css 
    			LEFT OUTER JOIN v_CITargetedMachines AS ctm ON ctm.CI_ID = css.CI_ID 
    			INNER JOIN v_UpdateCIs AS UCI ON UCI.CI_ID = css.CI_ID 
    			INNER JOIN v_CICategories_All AS catall2 ON catall2.CI_ID = css.CI_ID 
    			INNER JOIN v_CategoryInfo AS catinfo2 ON catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID AND catinfo2.CategoryTypeName = 'UpdateClassification' 
    			INNER JOIN v_R_System AS SYS ON css.ResourceID = SYS.ResourceID 
    			INNER JOIN v_ClientCollectionMembers AS CCM ON SYS.ResourceID = CCM.ResourceID
    			
    	WHERE	(css.Status = 2) AND (ctm.ResourceID IS NOT NULL) AND (UCI.IsSuperseded = 0) AND (UCI.IsExpired = 0) AND 
    			(catinfo2.CategoryInstanceName LIKE 'Security%') AND (CCM.CollectionID IN ('Z000007B', 'Z0000078')) AND 
    			(SYS.Resource_Domain_OR_Workgr0 IN (@Domain)) AND (LEFT(SYS.AD_Site_Name0, 3) IN (@Site))) AS 'NotCompliant'

    quinta-feira, 9 de outubro de 2014 07:51

Respostas

  • opa...

    bom existem N formas, mas se vc quiser usar seu proprio SQL unico podeira fazer assim

    SELECT	
    
    (SELECT	COUNT(DISTINCT SYS.Netbios_Name0) AS 'Expr2'
    
    	FROM	v_R_System AS SYS
    			INNER JOIN v_ClientCollectionMembers AS CCM ON SYS.ResourceID = CCM.ResourceID
    			
    	WHERE	(CCM.CollectionID IN ('Z000007B', 'Z0000078')) AND 
    			(SYS.Resource_Domain_OR_Workgr0 IN (@Domain)) AND (LEFT(SYS.AD_Site_Name0, 3) IN (@Site))) AS 'AllClients',
    
    
    (SELECT	COUNT(DISTINCT SYS.Netbios_Name0) AS 'Expr1'
    
    	FROM	v_UpdateComplianceStatus AS css 
    			LEFT OUTER JOIN v_CITargetedMachines AS ctm ON ctm.CI_ID = css.CI_ID 
    			INNER JOIN v_UpdateCIs AS UCI ON UCI.CI_ID = css.CI_ID 
    			INNER JOIN v_CICategories_All AS catall2 ON catall2.CI_ID = css.CI_ID 
    			INNER JOIN v_CategoryInfo AS catinfo2 ON catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID AND catinfo2.CategoryTypeName = 'UpdateClassification' 
    			INNER JOIN v_R_System AS SYS ON css.ResourceID = SYS.ResourceID 
    			INNER JOIN v_ClientCollectionMembers AS CCM ON SYS.ResourceID = CCM.ResourceID
    			
    	WHERE	(css.Status = 2) AND (ctm.ResourceID IS NOT NULL) AND (UCI.IsSuperseded = 0) AND (UCI.IsExpired = 0) AND 
    			(catinfo2.CategoryInstanceName LIKE 'Security%') AND (CCM.CollectionID IN ('Z000007B', 'Z0000078')) AND 
    			(SYS.Resource_Domain_OR_Workgr0 IN (@Domain)) AND (LEFT(SYS.AD_Site_Name0, 3) IN (@Site))) AS 'Not Compliant'
    			
    			
    			,
    
    ((SELECT	COUNT(DISTINCT SYS.Netbios_Name0) AS 'Expr1'
    
    	FROM	v_UpdateComplianceStatus AS css 
    			LEFT OUTER JOIN v_CITargetedMachines AS ctm ON ctm.CI_ID = css.CI_ID 
    			INNER JOIN v_UpdateCIs AS UCI ON UCI.CI_ID = css.CI_ID 
    			INNER JOIN v_CICategories_All AS catall2 ON catall2.CI_ID = css.CI_ID 
    			INNER JOIN v_CategoryInfo AS catinfo2 ON catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID AND catinfo2.CategoryTypeName = 'UpdateClassification' 
    			INNER JOIN v_R_System AS SYS ON css.ResourceID = SYS.ResourceID 
    			INNER JOIN v_ClientCollectionMembers AS CCM ON SYS.ResourceID = CCM.ResourceID
    			
    	WHERE	(css.Status = 2) AND (ctm.ResourceID IS NOT NULL) AND (UCI.IsSuperseded = 0) AND (UCI.IsExpired = 0) AND 
    			(catinfo2.CategoryInstanceName LIKE 'Security%') AND (CCM.CollectionID IN ('Z000007B', 'Z0000078')) AND 
    			(SYS.Resource_Domain_OR_Workgr0 IN (@Domain)) AND (LEFT(SYS.AD_Site_Name0, 3) IN (@Site)))
    			 /			
    			
    (SELECT	COUNT(DISTINCT SYS.Netbios_Name0) AS 'Expr2'
    
    	FROM	v_R_System AS SYS
    			INNER JOIN v_ClientCollectionMembers AS CCM ON SYS.ResourceID = CCM.ResourceID
    			
    	WHERE	(CCM.CollectionID IN ('Z000007B', 'Z0000078')) AND 
    			(SYS.Resource_Domain_OR_Workgr0 IN (@Domain)) AND (LEFT(SYS.AD_Site_Name0, 3) IN (@Site))) ) as [%Of not Not Compliant] 			
    			

    Você pode usar as mesmas colunas para fazer cálculo...

    o que daria para fazer tb é

    create table #temp (expressao1 decimal(18,4), expressao2 decimal(18,4), expressao3 decimal(18,4))
    
    insert into #temp 
    SELECT	
    
    (SELECT	COUNT(DISTINCT SYS.Netbios_Name0) AS 'Expr2'
    
    	FROM	v_R_System AS SYS
    			INNER JOIN v_ClientCollectionMembers AS CCM ON SYS.ResourceID = CCM.ResourceID
    			
    	WHERE	(CCM.CollectionID IN ('Z000007B', 'Z0000078')) AND 
    			(SYS.Resource_Domain_OR_Workgr0 IN (@Domain)) AND (LEFT(SYS.AD_Site_Name0, 3) IN (@Site))) AS 'AllClients',
    
    
    (SELECT	COUNT(DISTINCT SYS.Netbios_Name0) AS 'Expr1'
    
    	FROM	v_UpdateComplianceStatus AS css 
    			LEFT OUTER JOIN v_CITargetedMachines AS ctm ON ctm.CI_ID = css.CI_ID 
    			INNER JOIN v_UpdateCIs AS UCI ON UCI.CI_ID = css.CI_ID 
    			INNER JOIN v_CICategories_All AS catall2 ON catall2.CI_ID = css.CI_ID 
    			INNER JOIN v_CategoryInfo AS catinfo2 ON catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID AND catinfo2.CategoryTypeName = 'UpdateClassification' 
    			INNER JOIN v_R_System AS SYS ON css.ResourceID = SYS.ResourceID 
    			INNER JOIN v_ClientCollectionMembers AS CCM ON SYS.ResourceID = CCM.ResourceID
    			
    	WHERE	(css.Status = 2) AND (ctm.ResourceID IS NOT NULL) AND (UCI.IsSuperseded = 0) AND (UCI.IsExpired = 0) AND 
    			(catinfo2.CategoryInstanceName LIKE 'Security%') AND (CCM.CollectionID IN ('Z000007B', 'Z0000078')) AND 
    			(SYS.Resource_Domain_OR_Workgr0 IN (@Domain)) AND (LEFT(SYS.AD_Site_Name0, 3) IN (@Site))) AS 'Not Compliant', 0
    			
    
    
    update #temp set expressao3 = (expressao2 / expressao1) * 100
    			
    
    Select * from #temp


    Isco Sistemas José Luiz Borges

    quinta-feira, 9 de outubro de 2014 12:08

Todas as Respostas

  • Deleted
    quinta-feira, 9 de outubro de 2014 12:05
  • opa...

    bom existem N formas, mas se vc quiser usar seu proprio SQL unico podeira fazer assim

    SELECT	
    
    (SELECT	COUNT(DISTINCT SYS.Netbios_Name0) AS 'Expr2'
    
    	FROM	v_R_System AS SYS
    			INNER JOIN v_ClientCollectionMembers AS CCM ON SYS.ResourceID = CCM.ResourceID
    			
    	WHERE	(CCM.CollectionID IN ('Z000007B', 'Z0000078')) AND 
    			(SYS.Resource_Domain_OR_Workgr0 IN (@Domain)) AND (LEFT(SYS.AD_Site_Name0, 3) IN (@Site))) AS 'AllClients',
    
    
    (SELECT	COUNT(DISTINCT SYS.Netbios_Name0) AS 'Expr1'
    
    	FROM	v_UpdateComplianceStatus AS css 
    			LEFT OUTER JOIN v_CITargetedMachines AS ctm ON ctm.CI_ID = css.CI_ID 
    			INNER JOIN v_UpdateCIs AS UCI ON UCI.CI_ID = css.CI_ID 
    			INNER JOIN v_CICategories_All AS catall2 ON catall2.CI_ID = css.CI_ID 
    			INNER JOIN v_CategoryInfo AS catinfo2 ON catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID AND catinfo2.CategoryTypeName = 'UpdateClassification' 
    			INNER JOIN v_R_System AS SYS ON css.ResourceID = SYS.ResourceID 
    			INNER JOIN v_ClientCollectionMembers AS CCM ON SYS.ResourceID = CCM.ResourceID
    			
    	WHERE	(css.Status = 2) AND (ctm.ResourceID IS NOT NULL) AND (UCI.IsSuperseded = 0) AND (UCI.IsExpired = 0) AND 
    			(catinfo2.CategoryInstanceName LIKE 'Security%') AND (CCM.CollectionID IN ('Z000007B', 'Z0000078')) AND 
    			(SYS.Resource_Domain_OR_Workgr0 IN (@Domain)) AND (LEFT(SYS.AD_Site_Name0, 3) IN (@Site))) AS 'Not Compliant'
    			
    			
    			,
    
    ((SELECT	COUNT(DISTINCT SYS.Netbios_Name0) AS 'Expr1'
    
    	FROM	v_UpdateComplianceStatus AS css 
    			LEFT OUTER JOIN v_CITargetedMachines AS ctm ON ctm.CI_ID = css.CI_ID 
    			INNER JOIN v_UpdateCIs AS UCI ON UCI.CI_ID = css.CI_ID 
    			INNER JOIN v_CICategories_All AS catall2 ON catall2.CI_ID = css.CI_ID 
    			INNER JOIN v_CategoryInfo AS catinfo2 ON catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID AND catinfo2.CategoryTypeName = 'UpdateClassification' 
    			INNER JOIN v_R_System AS SYS ON css.ResourceID = SYS.ResourceID 
    			INNER JOIN v_ClientCollectionMembers AS CCM ON SYS.ResourceID = CCM.ResourceID
    			
    	WHERE	(css.Status = 2) AND (ctm.ResourceID IS NOT NULL) AND (UCI.IsSuperseded = 0) AND (UCI.IsExpired = 0) AND 
    			(catinfo2.CategoryInstanceName LIKE 'Security%') AND (CCM.CollectionID IN ('Z000007B', 'Z0000078')) AND 
    			(SYS.Resource_Domain_OR_Workgr0 IN (@Domain)) AND (LEFT(SYS.AD_Site_Name0, 3) IN (@Site)))
    			 /			
    			
    (SELECT	COUNT(DISTINCT SYS.Netbios_Name0) AS 'Expr2'
    
    	FROM	v_R_System AS SYS
    			INNER JOIN v_ClientCollectionMembers AS CCM ON SYS.ResourceID = CCM.ResourceID
    			
    	WHERE	(CCM.CollectionID IN ('Z000007B', 'Z0000078')) AND 
    			(SYS.Resource_Domain_OR_Workgr0 IN (@Domain)) AND (LEFT(SYS.AD_Site_Name0, 3) IN (@Site))) ) as [%Of not Not Compliant] 			
    			

    Você pode usar as mesmas colunas para fazer cálculo...

    o que daria para fazer tb é

    create table #temp (expressao1 decimal(18,4), expressao2 decimal(18,4), expressao3 decimal(18,4))
    
    insert into #temp 
    SELECT	
    
    (SELECT	COUNT(DISTINCT SYS.Netbios_Name0) AS 'Expr2'
    
    	FROM	v_R_System AS SYS
    			INNER JOIN v_ClientCollectionMembers AS CCM ON SYS.ResourceID = CCM.ResourceID
    			
    	WHERE	(CCM.CollectionID IN ('Z000007B', 'Z0000078')) AND 
    			(SYS.Resource_Domain_OR_Workgr0 IN (@Domain)) AND (LEFT(SYS.AD_Site_Name0, 3) IN (@Site))) AS 'AllClients',
    
    
    (SELECT	COUNT(DISTINCT SYS.Netbios_Name0) AS 'Expr1'
    
    	FROM	v_UpdateComplianceStatus AS css 
    			LEFT OUTER JOIN v_CITargetedMachines AS ctm ON ctm.CI_ID = css.CI_ID 
    			INNER JOIN v_UpdateCIs AS UCI ON UCI.CI_ID = css.CI_ID 
    			INNER JOIN v_CICategories_All AS catall2 ON catall2.CI_ID = css.CI_ID 
    			INNER JOIN v_CategoryInfo AS catinfo2 ON catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID AND catinfo2.CategoryTypeName = 'UpdateClassification' 
    			INNER JOIN v_R_System AS SYS ON css.ResourceID = SYS.ResourceID 
    			INNER JOIN v_ClientCollectionMembers AS CCM ON SYS.ResourceID = CCM.ResourceID
    			
    	WHERE	(css.Status = 2) AND (ctm.ResourceID IS NOT NULL) AND (UCI.IsSuperseded = 0) AND (UCI.IsExpired = 0) AND 
    			(catinfo2.CategoryInstanceName LIKE 'Security%') AND (CCM.CollectionID IN ('Z000007B', 'Z0000078')) AND 
    			(SYS.Resource_Domain_OR_Workgr0 IN (@Domain)) AND (LEFT(SYS.AD_Site_Name0, 3) IN (@Site))) AS 'Not Compliant', 0
    			
    
    
    update #temp set expressao3 = (expressao2 / expressao1) * 100
    			
    
    Select * from #temp


    Isco Sistemas José Luiz Borges

    quinta-feira, 9 de outubro de 2014 12:08
  • José a segunda solução me ajudou ... Muito Obrigado por tudo =)

    a query já esta rodando.

    quinta-feira, 9 de outubro de 2014 12:45