none
Ajuda na query RRS feed

  • Pergunta

  • Bom dia
    Senhores

    Preciso pegar o resultado do ADM+ DEM dividir ele por 2 depois dividir pelo total geral.

    SELECT
    'ADMISSAO' = ( SELECT count(1)
       FROM [vetorh_homolog].[dbo].[usu_tkpicol]
       WHERE usu_afast<>'7' AND usu_datref BETWEEN '20170101' and '20170131')

    ,'DEMISSAO' =( SELECT COUNT(1)
    FROM [vetorh_homolog].[dbo].[usu_tkpicol]
    WHERE usu_afast='7' AND usu_datref BETWEEN '20170101' and '20170131')

    , 'ADM + DEM'  = ( SELECT COUNT(1)
    FROM [vetorh_homolog].[dbo].[usu_tkpicol]
    WHERE usu_datref BETWEEN '20170101' and '20170131')

    , 'TOTAL GERAL'   =( SELECT COUNT(1)
       FROM [vetorh_homolog].[dbo].[usu_tkpicol]
       WHERE usu_afast<>'7' )

    segunda-feira, 31 de julho de 2017 14:55

Respostas

  • Bom dia,

    Experimente fazer uns testes dessa forma:

    with CTE_Count as
    (
        SELECT
            'ADMISSAO' = count(case when usu_afast <> '7' AND usu_datref BETWEEN '20170101' and '20170131' then 1 end)
           ,'DEMISSAO' = COUNT(case when usu_afast = '7' AND usu_datref BETWEEN '20170101' and '20170131' then 1 end)
           ,'ADM + DEM' = COUNT(case when usu_datref BETWEEN '20170101' and '20170131' then 1 end)
           ,'TOTAL GERAL' = COUNT(case when usu_afast <> '7' then 1 end)
        FROM [vetorh_homolog].[dbo].[usu_tkpicol]
    )
    
    select
        *
       ,[ADM + DEM] / 2 / [TOTAL GERAL]
    from CTE_Count

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br


    segunda-feira, 31 de julho de 2017 15:07

Todas as Respostas

  • Bom dia,

    Experimente fazer uns testes dessa forma:

    with CTE_Count as
    (
        SELECT
            'ADMISSAO' = count(case when usu_afast <> '7' AND usu_datref BETWEEN '20170101' and '20170131' then 1 end)
           ,'DEMISSAO' = COUNT(case when usu_afast = '7' AND usu_datref BETWEEN '20170101' and '20170131' then 1 end)
           ,'ADM + DEM' = COUNT(case when usu_datref BETWEEN '20170101' and '20170131' then 1 end)
           ,'TOTAL GERAL' = COUNT(case when usu_afast <> '7' then 1 end)
        FROM [vetorh_homolog].[dbo].[usu_tkpicol]
    )
    
    select
        *
       ,[ADM + DEM] / 2 / [TOTAL GERAL]
    from CTE_Count

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br


    segunda-feira, 31 de julho de 2017 15:07
  • vou testar obrigado

    segunda-feira, 31 de julho de 2017 16:09
  • Deu certo obrigado
    terça-feira, 1 de agosto de 2017 14:04