APS.NET MVC stored procedure that returns an array


How can I create a stored procedure that shows me employee deductions

I need a result like this: where it shows the uniform deduction

This is the table employees

is the table deductions

this is the Deductions Detail table

asked by Marco Eufragio 19.10.2018 в 22:58

1 answer


I do not know if this is what you need but in a matter of joins between tables, this would be the way:

Select emp.EmpId as Codigo, emp.EmpNombre + ' ' + emp.EmpApellido as Empleado,
td.DedValor as Uniforme FROM Tbl_Empleados emp INNER JOIN Tbl_DetalleDeduccionesEmpleado
tde ON emp.EmpId = tde.EmpId INNER JOIN Tbl_Deducciones td ON tde.DedId = td.DedId

You can use Left Join instead of Inner join to show all the data when some are null. For example DevValor.

I think you could use a pivot for what you need in question of putting all the deductions in the same row, I do not know how many deductions are, I put 5 as an example, I do not know if the query is going to work, it's just an example:

        SELECT emp.EmpId AS Codigo
        ,emp.EmpNombre + ' ' + emp.EmpApellido AS Empleado
            ,[1] AS Ded1
            ,[2] AS Ded2
            ,[3] AS Ded3
            ,[4] AS Ded4
            ,[5] AS Ded5
            FROM Tbl_Empleados emp
            INNER JOIN(
            SELECT * FROM ( SELECT empId, DetDedEmpValor, DetId 
       FROM Tbl_DetalleDeduccionesEmpleado) I 
        PIVOT(MAX(DetDedEmpValor) FOR DetID IN (
                    )) P) I ON emp.EmpId = I.EmpId

Try it and tell me.

answered by 20.10.2018 / 00:06