Divide records by a column in a SQL Server view

0

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.

    
asked by Urtx 28.08.2018 в 09:29
source

0 answers