# Ajuda na query

• ### Pergunta

• Bom dia
Senhores

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

SELECT
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

segunda-feira, 31 de julho de 2017 16:09