There is a better way to write this sql query (union)?

Question

I want to know if there is a better way to write this sql query, in terms of performance and stability.

Because, I think I'm repeating the code many times and maybe I can do it with the CASE clause or another one.

SELECT x.Fecha,x.IdTrabajador,Cast(x.Cantidad AS DECIMAL(18, 2)) AS Cantidad,x.Motivo,x.IdTrabajadorIncidencia
    FROM   (SELECT I.IdTrabajador,I.Fecha,
                Datediff(second, I.HoraIngreso, (SELECT HD.HoraInicio
                    FROM   [rrhh].TrabajadorHorarioDetalle HD
                            INNER JOIN [rrhh].TrabajadorHorario H
                                    ON HD.IdTrabajadorHorario = H.IdTrabajadorHorario
                    WHERE  H.Estado = 1
                            AND HD.Estado = 1
                            AND HD.Dia = Datename(dw, I.Fecha)
                            AND H.IdTrabajador = I.IdTrabajador)) / 3600.0 AS Cantidad,
                'Sobretiempo en hora de ingreso...' AS Motivo,I.IdTrabajadorIncidencia
            FROM   [rrhh].TrabajadorIncidencia I
            WHERE  I.Estado = 1
                   AND I.IdIncidencia = 1) AS x
    WHERE  x.Cantidad > 0.00
    UNION
    SELECT x.Fecha,x.IdTrabajador,Cast(x.Cantidad AS DECIMAL(18, 2)) AS Cantidad,x.Motivo,x.IdTrabajadorIncidencia
    FROM   (SELECT I.IdTrabajador,I.Fecha,
                Datediff(second, (SELECT HD.HoraInicioRefrigerio
                    FROM   [rrhh].TrabajadorHorarioDetalle HD
                            INNER JOIN [rrhh].TrabajadorHorario H
                                    ON HD.IdTrabajadorHorario = H.IdTrabajadorHorario
                    WHERE  H.Estado = 1
                            AND HD.Estado = 1
                            AND HD.Dia = Datename(dw, I.Fecha)
                            AND H.IdTrabajador = I.IdTrabajador), I.HoraInicioRefrigerio) / 3600.0 AS Cantidad,
                'Sobretiempo en hora de inicio de refrigerio...' AS Motivo,I.IdTrabajadorIncidencia
            FROM   [rrhh].TrabajadorIncidencia I
            WHERE  I.Estado = 1
                   AND I.IdIncidencia = 1) AS x
    WHERE  x.Cantidad > 0.00
    UNION
    SELECT x.Fecha,x.IdTrabajador,Cast(x.Cantidad AS DECIMAL(18, 2)) AS Cantidad,x.Motivo,x.IdTrabajadorIncidencia
    FROM   (SELECT I.IdTrabajador,I.Fecha,
                Datediff(second, I.HoraFinRefrigerio, (SELECT HD.HoraFinRefrigerio
                    FROM   [rrhh].TrabajadorHorarioDetalle HD
                        INNER JOIN [rrhh].TrabajadorHorario H
                                ON HD.IdTrabajadorHorario = H.IdTrabajadorHorario
                    WHERE  H.Estado = 1
                        AND HD.Estado = 1
                        AND HD.Dia = Datename(dw, I.Fecha)
                        AND H.IdTrabajador = I.IdTrabajador)) / 3600.0 AS Cantidad,
                'Sobretiempo en hora de término de refrigerio...' AS Motivo,I.IdTrabajadorIncidencia
            FROM   [rrhh].TrabajadorIncidencia I
            WHERE  I.Estado = 1
                   AND I.IdIncidencia = 1) AS x
    WHERE  x.Cantidad > 0.00
    UNION
    SELECT x.Fecha,x.IdTrabajador,Cast(x.Cantidad AS DECIMAL(18, 2)) AS Cantidad,x.Motivo,x.IdTrabajadorIncidencia
    FROM   (SELECT I.IdTrabajador,I.Fecha,
                Datediff(second, (SELECT HD.HoraFin
                    FROM   [rrhh].TrabajadorHorarioDetalle HD
                            INNER JOIN [rrhh].TrabajadorHorario H
                                    ON HD.IdTrabajadorHorario = H.IdTrabajadorHorario
                    WHERE  H.Estado = 1
                            AND HD.Estado = 1
                            AND HD.Dia = Datename(dw, I.Fecha)
                            AND H.IdTrabajador = I.IdTrabajador), I.HoraSalida) / 3600.0 AS Cantidad,
                'Sobretiempo en hora de salida...' AS Motivo,I.IdTrabajadorIncidencia
            FROM   [rrhh].TrabajadorIncidencia I
            WHERE  I.Estado = 1
                   AND I.IdIncidencia = 1) AS x
    WHERE  x.Cantidad > 0.00
    UNION
    --Search Horas Extras (HE25 y HE35) in table TrabajadorIncidencia
    SELECT x.Fecha,x.IdTrabajador,Cast(x.Cantidad AS DECIMAL(18, 2)) AS Cantidad,x.Motivo,x.IdTrabajadorIncidencia
    FROM   (SELECT I.IdTrabajador,I.Fecha,Datediff(second, I.HoraIngreso, I.HoraSalida) / 3600.0 AS Cantidad,'Sobretiempo en hora extra registrada...' AS Motivo,I.IdTrabajadorIncidencia
            FROM   [rrhh].TrabajadorIncidencia I
            WHERE  I.Estado = 1
                   AND ( I.IdIncidencia = 2
                          OR I.IdIncidencia = 4 )) AS x
    WHERE  x.Cantidad > 0.00
    ORDER  BY x.Fecha;

The previous sql query works fine, but maybe it could be better. Thanks.

codereview.stackexchange.com is a good place to get optimization / refactoring advice on something this large. Performance questions should always include the actual execution plan too :) — S3S
– S3S, Commented Jan 31, 2019 at 21:33
what's the difference in each UNION ? I'm looking, but it's a lot of code and it's not jumping out (aside from the last union) — S3S
– S3S, Commented Jan 31, 2019 at 21:37
@scsimon Basically, in the order of the parameters in Datediff (in the first four selects) — Raul Miranda
– Raul Miranda, Commented Jan 31, 2019 at 21:40

Daniel Gimenez · Accepted Answer · 2019-02-05 14:46:41Z

1

You can use Common Table Expressions (CTEs) to break the problem down. In the example below I broke the problem down into its various parts. I think the end result is much easier to follow.

Don't think that the CTEs will create any additional processing. I am often amazed how the efficiently SQL Server's query planner uses them.

A few caveats: There is a chance the results might be different because the SELECTs in the DATEDIFF function were removed and replaced with a JOIN. I'm going to assume you had a one to one relationship with the given predicates or you would've been getting errors. Also I probably made a mistake in the cutting and pasting, so don't be surprised if this doesn't run.

WITH TrabajadorIncidenciaDetalleCte AS (
    SELECT I.*, HD.HoraInicio, HD.HoraFinRefrigerio, HD.HoraFin
    FROM [rrhh].TrabajadorIncidencia I
    INNER JOIN [rrhh].TrabajadorHorarioDetalle HD ON HD.Dia = DATENAME(dw, I.Fecha)
    INNER JOIN [rrhh].TrabajadorHorario H ON HD.IdTrabajadorHorario = H.IdTrabajadorHorario AND H.IdTrabajador = I.IdTrabajador
    WHERE  H.Estado = 1 AND HD.Estado = 1 AND I.Estado = 1 AND I.IdIncidencia = 1
)
, IncidenciaCte AS (
    SELECT IdTrabajador,Fecha, DATEDIFF(second, HoraIngreso, HoraInicio) AS Cantidad,
        'Sobretiempo en hora de ingreso...' AS Motivo, IdTrabajadorIncidencia
    FROM TrabajadorIncidenciaDetalleCte
    UNION ALL
    SELECT IdTrabajador,Fecha, DATEDIFF(second, HoraInicioRefrigerio, HoraFinRefrigerio) AS Cantidad,
        'Sobretiempo en hora de término de refrigerio...' AS Motivo, IdTrabajadorIncidencia
    FROM TrabajadorIncidenciaDetalleCte
    UNION ALL
    SELECT IdTrabajador,Fecha, DATEDIFF(second, HoraFin, HoraSalida) AS Cantidad,
        'Sobretiempo en hora de salida...' AS Motivo, IdTrabajadorIncidencia
    FROM TrabajadorIncidenciaDetalleCte
    UNION ALL
    SELECT IdTrabajador, Fecha, DATEDIFF(second, HoraIngreso, HoraSalida) AS Cantidad,
        'Sobretiempo en hora extra registrada...' AS Motivo, IdTrabajadorIncidencia
    FROM [rrhh].TrabajadorIncidencia
    WHERE Estado = 1 AND ( IdIncidencia = 2 OR IdIncidencia = 4 )   
)
SELECT x.Fecha, x.IdTrabajador, 
    CAST((x.Cantidad / 3600.0) AS DECIMAL(18, 2)) AS Cantidad,
    x.Motivo, x.IdTrabajadorIncidencia
FROM IncidenciaCte x
WHERE x.Cantidad > 0.00
ORDER BY x.Fecha;

edited Feb 5, 2019 at 14:46

answered Jan 31, 2019 at 22:59

Daniel Gimenez

21.1k4 gold badges60 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Raul Miranda Over a year ago

Thanks! Works fine!

Raul Miranda Over a year ago

Ps: Can I edit your query? Miss this part: SELECT... DATEDIFF(... HoraInicioRefrigerio...

Daniel Gimenez Over a year ago

I went ahead and fixed it.

Martin Smith · Accepted Answer · 2019-01-31 23:43:33Z

The WHERE x.Cantidad > 0.00 means that you eliminate any results where the subquery didn't match any rows and returns NULL.

Your current query will fail with an error if the subquery returns more than one row. If you are happy to go without the error in that eventuality then you can use an INNER JOIN instead of 4 similar subqueries, and then CROSS APPLY ... VALUES to unpivot the four joined column values into rows. Then UNION that onto the final branch.

SELECT I.Fecha,
       I.IdTrabajador,
       CAST(x.Cantidad / 3600.0 AS DECIMAL(18, 2)) AS Cantidad,
       x.Motivo,
       I.IdTrabajadorIncidencia
FROM   [rrhh].TrabajadorIncidencia I
       INNER JOIN [rrhh].TrabajadorHorario H
               ON H.IdTrabajador = I.IdTrabajador
                  AND H.Estado = I.Estado
       INNER JOIN [rrhh].TrabajadorHorarioDetalle HD
               ON HD.IdTrabajadorHorario = H.IdTrabajadorHorario
                  AND HD.Estado = H.Estado
       CROSS APPLY ( VALUES (DATEDIFF(second, I.HoraIngreso, HD.HoraInicio), 'Sobretiempo en hora de ingreso...'),
                            (DATEDIFF(second, HD.HoraFinRefrigerio, I.HoraInicioRefrigerio), 'Sobretiempo en hora de inicio de refrigerio...'),
                            (DATEDIFF(second, I.HoraFinRefrigerio, HD.HoraFinRefrigerio), 'Sobretiempo en hora de término de refrigerio...'),
                            (DATEDIFF(second, HD.HoraFin, I.HoraSalida), 'Sobretiempo en hora de salida...') ) x(Cantidad, Motivo)
WHERE  x.Cantidad > 0
       AND I.Estado = 1
       AND I.IdIncidencia = 1
       AND HD.Dia = DATENAME(dw, I.Fecha)
UNION
--Search Horas Extras (HE25 y HE35) in table TrabajadorIncidencia
SELECT x.Fecha,
       x.IdTrabajador,
       CAST(x.Cantidad AS DECIMAL(18, 2)) AS Cantidad,
       x.Motivo,
       x.IdTrabajadorIncidencia
FROM   (SELECT I.IdTrabajador,
               I.Fecha,
               DATEDIFF(second, I.HoraIngreso, I.HoraSalida) / 3600.0 AS Cantidad,
               'Sobretiempo en hora extra registrada...'              AS Motivo,
               I.IdTrabajadorIncidencia
        FROM   [rrhh].TrabajadorIncidencia I
        WHERE  I.Estado = 1
               AND ( I.IdIncidencia = 2
                      OR I.IdIncidencia = 4 )) AS x
WHERE  x.Cantidad > 0.00
ORDER  BY Fecha;

OMG! So close!! The previous query return 16 rows. This, 14.

Collectives™ on Stack Overflow

There is a better way to write this sql query (union)?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related