CODEWITHSUNDEEP
c#mysqldapperidictionary
Aldimir
Aldimir

Reputation: 191

Is there an easier way to make this query?

I have two different tables from a DataBase named "empleados" and "fichajes". empleados has the employees data and fichajes has the date and time from when they started working.

I want to get the total work time done by a specific employee between two dates, lets say from 20th to 29th.

I have this query which I use with Dapper on C#:

SELECT CONCAT(e.nombre, " " ,e.apellido) as fullName, tfichajes.total 
FROM empleados e 
    INNER JOIN (
                SELECT f1.nif, 
                    SEC_TO_TIME(SUM(TIME_TO_SEC(f1.fechasalida) - TIME_TO_SEC(f1.fechaentrada))) AS total 
                    FROM fichajes f1 
                    WHERE f1.fechasalida <= '2019-04-29' 
                    and f1.fechaentrada >= '2019-04-20' 
                    GROUP BY f1.nif
                ) AS tfichajes 
        ON e.nif = tfichajes.nif 
WHERE e.nif = '33333333P'

This works just fine, but I was wondering if it was possible to make it simpler.

This is the code I have in my program:

public static List<string> CalculaTotalHoras(string nif, DateTime fechaEntrada, DateTime fechaSalida)
{
    var dbCon = DBConnection.Instancia();
    if (dbCon.Conectado())
    {
        string format = "yyyy-MM-dd";
        List<string> result = new List<string>();
        using (IDbConnection conexion = dbCon.Conexion)
        {
            var output = conexion.Query($"SELECT CONCAT(e.nombre, \" \" ,e.apellido) as fullName, tfichajes.total " +
                $"FROM empleados e INNER JOIN (SELECT f1.nif, SEC_TO_TIME(SUM(TIME_TO_SEC(f1.fechasalida) - TIME_TO_SEC(f1.fechaentrada))) AS total " +
                $"FROM fichajes f1 where f1.fechasalida <= '{fechaSalida.ToString(format)}' and f1.fechaentrada >= '{fechaEntrada.ToString(format)}' GROUP BY f1.nif) " +
                $"as tfichajes ON e.nif = tfichajes.nif where e.nif = '{ nif }';").ToList();

            var i = 0;
            foreach (IDictionary<string, object> row in output)
            {
                foreach (var pair in row)
                {
                    if (i == 0)
                    {
                        result.Add(pair.Value.ToString());
                        i++;
                    }
                    else
                    {
                        result.Add(pair.Value.ToString());
                    }
                }
            }
            return result;
        }
    }
    else return null;
}

If you have problems with the readability of the code here you have a gyazo. Workingtime fichajes table and employees empleados table.

With that exact query, the expected results are Alvaro Arguelles 00:05:00, and in the code, I want to get Alvaro Arguelles and 00:05:00 separated in the result List.

Did I made it much harder than it actually is?

Upvotes: 1

Views: 80

Answers (1)

DRapp
DRapp

Reputation: 48139

Your query can be significantly simplified. Your inner query is selecting ALL employees working in the date in question, I would start with the INNER query as the main FROM table getting the data for the one employee. THEN join to the employee to grab the name.

select
      CONCAT(e.nombre, " " ,e.apellido) as fullName,
      OneEmp.Total
   from
      ( select
              f1.nif,
              SEC_TO_TIME( SUM(TIME_TO_SEC(f1.fechasalida) 
                             - TIME_TO_SEC(f1.fechaentrada))) AS total 
           from
              fichajes f1
           where
                  f1.nif = '33333333P'
              AND f1.fechasalida >= '2019-04-20' 
              AND f1.fechasalida <= '2019-04-29' ) OneEmp
         JOIN empleados e
            on OneEmp.nif = e.nif

Since your query was summarizing per the one employee, it would only return a single row anyhow. So the above query will return 1 row, 2 columns... name and total. Should be able to read those two values back directly.

But as others commented, PARAMETERIZE your query calls.

Upvotes: 1

Related Questions