I'm working with a view in SQL Server and I currently have the following:
I get in a row the sum of the allocation of hours of the employees and the machines for each task.
Example of a record:
0ba22dd5-0041-4a85-91622bc6f-3213-46cf-9 f5bd2590-3a46-4f5b-a778-c3206548c4b3 GMK4100-XN COMERCIAL T02 2018-06-05 00:00:50.000 2018-06-05 08:00:00.000 NULL NULL **15 25** 8 75,0000000000 2018-06-05 14:26:45.923 berbina
15 and 25 would be the time of each resource added, being able to be 0 in each of them.
SELECT { fn CONCAT(LEFT(dbo.PROProjectTask.IDProTask, 20), COALESCE (LEFT(dbo.PROProjectResourceEmployee.IDProResourceEmployee, 20),
LEFT(dbo.PROProjectResourceMachine.IDProResourceMachine, 20), RIGHT(dbo.PROProjectTask.IDProTask, 20))) } AS IDOcupacionRecursos,
dbo.PROProject.IDProject, dbo.PROProject.Description AS Desc_Proyecto, dbo.PROProjectTask.Description AS Desc_Tarea, dbo.PROProjectTask.CodPROTask,
dbo.PROProjectTask.PlannedStartDate, dbo.PROProjectTask.PlannedEndDate, PROProjectTask_1.CodPROTask AS CodTareaPadre,
PROProjectTask_1.Description AS Desc_Padre, COALESCE (SUM(dbo.PROProjectResourceEmployee.Duration / 60), 0) AS TiempoEmpleado,
COALESCE (SUM(dbo.PROProjectResourceMachine.Duration / 60), 0) AS TiempoMaquina, dbo.PROProjectTask.ExecutionTime / 60 AS ExecutionTime,
dbo.PROProjectTask.PercentProgress, dbo.PROProject.RowTimestamp, dbo.PROProject.LastModifiedBy
FROM dbo.PROProject INNER JOIN
dbo.PROProjectTask ON dbo.PROProject.IDProject = dbo.PROProjectTask.IDProject LEFT OUTER JOIN
dbo.PROProjectTask AS PROProjectTask_1 ON dbo.PROProjectTask.IDProTaskParent = PROProjectTask_1.IDProTask LEFT OUTER JOIN
dbo.PROProjectResourceMachine ON dbo.PROProjectTask.IDProTask = dbo.PROProjectResourceMachine.IDProTask LEFT OUTER JOIN
dbo.PROProjectResourceEmployee ON dbo.PROProjectTask.IDProTask = dbo.PROProjectResourceEmployee.IDProTask
GROUP BY dbo.PROProject.IDProject, dbo.PROProjectTask.Description, dbo.PROProjectTask.CodPROTask, dbo.PROProjectTask.PlannedStartDate,
PROProjectTask_1.CodPROTask, dbo.PROProjectTask.PlannedEndDate, PROProjectTask_1.Description, dbo.PROProject.RowTimestamp,
dbo.PROProject.LastModifiedBy, dbo.PROProjectTask.PercentProgress, { fn CONCAT(LEFT(dbo.PROProjectTask.IDProTask, 20),
COALESCE (LEFT(dbo.PROProjectResourceEmployee.IDProResourceEmployee, 20), LEFT(dbo.PROProjectResourceMachine.IDProResourceMachine, 20),
RIGHT(dbo.PROProjectTask.IDProTask, 20))) }, dbo.PROProjectTask.ExecutionTime / 60, dbo.PROProject.Description
HAVING (dbo.PROProject.IDProject = 'f5bd2590-3a46-4f5b-a778-c3206548c4b3')
And what I need to do is:
Instead of taking the two resources added in the same row, take the type of resource (employee or machine) with your individual time allocated for each row.
Following the previous example, instead of having a single row with the task called "Commercial", there would be 5, 3 with imputation of employees and 2 with imputation of machines.
How can I do it?
Thank you very much.