Data Grouping

Question

Data Grouping

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

Good evening, I need to make a query and I do not give with the one I need. I have a table with activities, which last, or not, more than one day, and in each activity 1 or up to 3 employees who perform the activity, it would be something like this

Actividad - fechaInicio - FechaFin - Empleado1 - Empleado2 - Empleado3

Now, I have to count how many days each activity lasts, I group them and calculate how many days each employee worked in the month, but only do it for an employee1, I do not add the employee2 nor the 3, I hit the query, for that is more graphic.

SELECT a.FechaInicio
,e.DiaAcuerdo
,SUM(datediff(day, a.FechaInicio, a.FechaFin)+1) as DiasTrabajados
,(e.DiaAcuerdo - sum (datediff (day, a.FechaInicio, a.FechaFin)+1)) as DiasRestantes
,(E.Nombre +' ' + E.Apellido) as Nombre
from Actividades as A
inner join Empleados E on
    a.ID_Emp = E.ID_Emp 
inner join Empleados E2 on
    a.ID_Emp1 = E2.ID_Emp
inner join Empleados E3 on
    a.ID_Emp1 = E3.ID_Emp
where DATEPART(month, A.FechaInicio) = DATEPART(month, getdate()) 
group by a.FechaInicio, a.FechaFin, a.ID_Emp,e.Nombre,e.Apellido,e.DiaAcuerdo

This query brings me

    2018-06-03  20  18  2   Paula Alvarez

Could you give me a hand? Thank you!

sql

asked by Jorge Falcon 04.06.2018 в 02:18

source

2 answers

1

According to your question you only want to know the days that each activity lasts, for this it is not necessary that you know how many days each one worked, so I would do it in the following way.

--Presumo que actividad es el (idActividad)
SELECT actividad, DATEDIFF(DD,diaInicio,diaFin) FROM Actividades
GROUP BY actividad

answered by 04.06.2018 в 15:14

capture the text of a vba tag Get the month number from the week number Javascript [closed]

score 0 · Accepted Answer

The main problem in your case is to pass a horizontal structure as Actividades that has up to three columns per employee, to a vertical, where you only have one column per employee and then be able to do a SUM() all. Needless to say, the ideal would be to manage a record by Activity / Employee, but if you can no longer modify this, a possible solution would be to do the following:

SELECT  E.ID_Emp,
    E.Nombre +' ' + E.Apellido as Nombre,
    e.DiaAcuerdo,
    SUM(A.DiasTrabajados)                   AS 'DiasTrabajados',
    e.DiaAcuerdo - SUM(A.DiasTrabajados)    AS 'DiasRestantes'
    FROM (  SELECT  ID_Emp                  AS 'ID_Emp',
            SUM(DATEDIFF(DAY, FechaInicio, FechaFin)+1)     AS 'DiasTrabajados',
            FROM Actividades
            WHERE DATEPART(MONTH, FechaInicio) = DATEPART(MONTH, GETDATE()) 
            GROUP BY ID_Emp

        UNION ALL

        SELECT  ID_Emp1                     AS 'ID_Emp',
            SUM(DATEDIFF(DAY, FechaInicio, FechaFin)+1)     AS 'DiasTrabajados',
            FROM Actividades
            WHERE DATEPART(MONTH, FechaInicio) = DATEPART(MONTH, GETDATE()) 
            GROUP BY ID_Emp1

        UNION ALL

        SELECT  ID_Emp2                     AS 'ID_Emp',
            SUM(DATEDIFF(DAY, FechaInicio, FechaFin)+1)     AS 'DiasTrabajados',
            FROM Actividades
            WHERE DATEPART(MONTH, FechaInicio) = DATEPART(MONTH, GETDATE()) 
            GROUP BY ID_Emp2
    ) A
    INNER JOIN Empleados E 
        ON A.ID_Emp = E.ID_Emp 
    GROUP BY E.ID_Emp,
        E.Nombre,
        E.Apellido,
        E.DiaAcuerdo

Basically we have a main query that is almost your same query repeated 3 times by each employee.
Note, that you remove from GROUP BY the dates, it is a contradiction to find the sum of the days worked per employee, and the dates in each activity.
With the three queries we get a total for each employee in each of the three columns per activity.
The only thing that remains to be done is to add again and now get a single total per Employee.