How to join three queries but from the same Table in SQL SERVER?

Question

How to join three queries but from the same Table in SQL SERVER?

Navigation

#1 by (2 votes)

1

I would like to know if you can join three tables but with different conditions, one winged result of the other.

---- First Consultation ----

--Atenciones Emergencia
    select  
    year(fecha)*100+month(fecha) as Periodo, 
    NombreMedicoAtencion as Medico ,
    TipoAtencionNombre, 
    count(distinct CodigoOA) as Atenc_Emer 
    from TBL_QV_Hecho_Produccion_IF
    where 
    year(fecha)>=2018 
    and CodigoSucursal='ceg' 
    and GrupoProduccion='Consultas' 
    and IdTipoAtencion=2
    group by year(fecha)*100+month(fecha), NombreMedicoAtencion ,TipoAtencionNombre
    order by  Medico, Periodo

---- Second Consultation ----

--Consultorio Externo
select 
year(fecha)*100+month(fecha) as Periodo,
NombreMedicoAtencion as Medico,
TipoAtencionNombre, 
sum(cantidad) as Atenc_Amb from TBL_QV_Hecho_Produccion_IF
where 
year(fecha)>=2016 
and CodigoSucursal='ceg' 
and GrupoProduccion='Consultas' 
and IdTipoAtencion=1
group by year(fecha)*100+month(fecha),NombreMedicoAtencion, TipoAtencionNombre
order by  Medico, Periodo

---- Third Consultation ----

   ----Egresos hospitalario
    select 
    year(fecha)*100+month(fecha) as Periodo, 
    IdMedicoAtencion ,
    NombreMedicoAtencion as Medico, 
    count(distinct CodigoOA) as Egresos 
    from TBL_QV_Hecho_Produccion_IF
    where 
    year(fecha)>=2016 
    and CodigoSucursal='ceg' 
    and IdTipoAtencion=3 
    and NombreEstadoDocumento<>'anulado' 
    and GrupoProduccion='hospitalizacion'
    group by year(fecha)*100+month(fecha), NombreMedicoAtencion
    order by  Medico, Periodo

As you can see there are three queries with different conditions, what I was trying to do was to have

Only the headers

PERIOD DOCTOR COUNT (Distinct CodigoOA) ---- > of the first consultation Sum (Quantities) ----- > of the second consultation COUNT (Distinct CodeTo) ----- > of the third consultation

the goal is to have all the doctors but for amounts of their services.

sql-server

asked by Christian Guerra Huertas 05.11.2018 в 22:20

source

1 answer

ASP how to create a table Save index to reuse

score 2 · Answer 1

You can try to make the queries in the same one since you use the same data just doing a different count or sum and that you fix it with where where you put that it takes the 3 types of Attention.

Alli you get a general query for the 3 fields can be any kind of attention.

select  
year(fecha)*100+month(fecha) as Periodo, 
NombreMedicoAtencion as Medico ,
TipoAtencionNombre, 
count(distinct CodigoOA) as Atenc_Emer,
sum(cantidad) as Atenc_Amb,
count(distinct CodigoOA) as Egresos 
from TBL_QV_Hecho_Produccion_IF
where 
year(fecha)>=2018 
and CodigoSucursal='ceg' 
and GrupoProduccion='Consultas' 
and IdTipoAtencion IN ('1','2','3')
group by year(fecha)*100+month(fecha), NombreMedicoAtencion ,TipoAtencionNombre
order by  Medico, Periodo