Group Querys in a Stored Procedure SQL

Navigation
0

Good morning, I have a problem and I do not know how to solve it, I need to make a report, which I requested certain measurements by MES. What I have is the Query of each measurement, but I do not know how to group all these Querys through an SP, rather than help, I need to know how to do it, with what method, etc.

Querys:

Commitments per Month: 

Select COUNT(c.ID) as 'CantidadGenerada' ,MONTH(c.Com_FechaCreacion) as 'Mes' from Compromisos c
group by MONTH(c.Com_FechaCreacion)

Number of attendees: 

select Count(Re.ID) as 'CantidadIntegrantes' from Rel_Reunion_Integrantes RE
inner join Reuniones R on
    R.ID=RE.Reu_ID
    group by MONTH(R.Reu_FechaCitaInicio)

Number of cases solved: 

Select COUNT(c.ID) as 'CantidadResuelto' ,MONTH(c.Com_FechaTermino) as 'Mes' from Compromisos c
group by MONTH(c.Com_FechaTermino)

Total Hours per month: 

 select sum((datediff(minute, R.Reu_FechaCitaInicio, R.Reu_FechaCitaTermino))) AS 'DuracionReuniones',Month(Reu_FechaCitaInicio) as 'Mes'
 from Rel_Reunion_Integrantes RE
inner join Reuniones R on
    R.ID=RE.Reu_ID
    group by MONTH(R.Reu_FechaCitaInicio)

Quantity Meetings: 

select COUNT(R.ID) as 'CantidadReuniones',MONTH(R.Reu_FechaCitaInicio) as 'Mes' from Reuniones R
    group by MONTH(R.Reu_FechaCitaInicio)

Result to obtain:

    
asked by Ariel Ignacio 11.09.2017 в 15:40
source

2 answers

3

Looking at the query it occurs to me that you can group the result sets into a table in memory and pivot from that table as follows: 

declare @Datos Table
(
  Tipo varchar(150),
  Cantidad int,
  Mes varchar(25)
)

SET LANGUAGE Spanish
-- Compromisos * mes
Insert into @Datos
Select 'CantidadGenerada', COUNT(c.ID) ,datename(mm ,c.Com_FechaCreacion) from Compromisos c
group by c.Com_FechaCreacion

--Cantidad de asistentes
Insert into @Datos
select 'CantidadIntegrantes', Count(Re.ID), datename(mm ,R.Reu_FechaCitaInicio) from Rel_Reunion_Integrantes RE
inner join Reuniones R on
    R.ID=RE.Reu_ID
    group by R.Reu_FechaCitaInicio

--Cantidad casos resueltos
Insert into @Datos
Select 'CantidadResuelto', COUNT(c.ID)  ,datename(mm ,c.Com_FechaTermino) from Compromisos c
group by c.Com_FechaTermino

--Horas por mes
Insert into @Datos
 select 'DuracionReuniones', sum((datediff(minute, R.Reu_FechaCitaInicio, R.Reu_FechaCitaTermino))), datename(mm ,Reu_FechaCitaInicio) 
 from Rel_Reunion_Integrantes RE
inner join Reuniones R on
    R.ID=RE.Reu_ID
    group by R.Reu_FechaCitaInicio
-- Cantidad de reuniones
Insert into @Datos
select 'CantidadReuniones', COUNT(R.ID) ,datename(mm ,R.Reu_FechaCitaInicio) from Reuniones R
    group by R.Reu_FechaCitaInicio

Select Tipo,
isnull([Enero], 0) as Enero,
isnull([Febrero], 0) as Febrero,
isnull([Marzo], 0) as Marzo,
isnull([Abril], 0) as Abril,
isnull([Mayo], 0) as Mayo,
isnull([Junio], 0) as Junio,
isnull([Julio], 0) as Julio,
isnull([Agosto], 0) as Agosto,
isnull([Septiembre], 0) as Septiembre,
isnull([Octubre], 0) as Octubre,
isnull([Noviembre], 0) as Noviembre,
isnull([Diciembre], 0) as Diciembre
from (
    Select Tipo, Cantidad, Mes
    from @Datos
) as Pvt
Pivot
(
    SUM(Cantidad)
    For Mes in ([Enero], [Febrero], [Marzo], [Abril], [Mayo], [Junio],
        [Julio], [Agosto], [Septiembre], [Octubre], [Noviembre], [Diciembre])
) as TablaPivoteada

Someone with more experience can suggest something more elegant, but for effects of your reference image gives you the result, you would still have to polish the results that return null, but the basic idea is there

[ link to SqlFiddel 1

  

Note: The fiddle is based on results that should return your queries, and as Lamak mentions in his answer if the queries do not limit the year the query will add the data of all the months of years different

    
answered by 11.09.2017 / 16:19
source
2

Ok, this is quite simple, if somewhat repetitive. Just make a series of joins between your queries: 

SELECT  COALESCE(q1.Mes,q2.Mes,q3.Mes,q4.Mes,q5.Mes) Mes,
        ISNULL(q1.CantidadGenerada,0) CantidadGenerada,
        ISNULL(q2.CantidadIntegrantes,0) CantidadIntegrantes,
        ISNULL(q3.CantidadResuelto,0) CantidadResuelto,
        ISNULL(q4.DuracionReuniones,0) DuracionReuniones,
        ISNULL(q5.CantidadReuniones,0) CantidadReuniones
FROM (  SELECT  MONTH(c.Com_FechaCreacion) Mes
                COUNT(c.ID) CantidadGenerada
        FROM Compromisos c
        GROUP BY MONTH(c.Com_FechaCreacion)) q1
FULL JOIN ( SELECT  MONTH(R.Reu_FechaCitaInicio) Mes,
                    COUNT(Re.ID) CantidadIntegrantes 
            FROM Rel_Reunion_Integrantes RE
            INNER JOIN Reuniones R 
                ON R.ID = RE.Reu_ID
            GROUP BY MONTH(R.Reu_FechaCitaInicio)) q2
    ON q1.Mes = q2.Mes
FULL JOIN ( SELECT  COUNT(c.ID) CantidadResuelto,
                    MONTH(c.Com_FechaTermino) Mes
            FROM Compromisos c
            GROUP BY MONTH(c.Com_FechaTermino)) q3
    ON COALESCE(q1.Mes,q2.Mes) = q3.Mes
FULL JOIN ( SELECT  SUM((DATEDIFF(MINUTE, R.Reu_FechaCitaInicio, R.Reu_FechaCitaTermino))) DuracionReuniones,
                    MONTH(Reu_FechaCitaInicio) Mes
            FROM Rel_Reunion_Integrantes RE
            INNER JOIN Reuniones R 
                ON R.ID = RE.Reu_ID
            GROUP BY MONTH(R.Reu_FechaCitaInicio)) q4
    ON COALESCE(q1.Mes,q2.Mes,q3.Mes) = q4.Mes
FULL JOIN ( SELECT  COUNT(R.ID) CantidadReuniones,
                    MONTH(R.Reu_FechaCitaInicio) Mes
            FROM Reuniones R
            GROUP BY MONTH(R.Reu_FechaCitaInicio)) q5
    ON COALESCE(q1.Mes,q2.Mes,q3.Mes,q4.Mes) = q5.Mes
;

Yes I must remind you that this would be valid if the consultations are for a particular year, but you will be joined months of different years.

    
answered by 11.09.2017 в 16:20
Execute html in asp.net Core Web Applications (.Net framework) How to use single quotes when I have php text [duplicated]