Get Different records from a single table in a Multiple query

Question

Get Different records from a single table in a Multiple query

Navigation

#1 by (0 votes)

0

Good morning everyone.

I come to you asking for your advice about how I can make a sentence (if possible only one).

The situation is as follows, in my database I have 4 tables from which I need to extract information for a report that has these fields:

The structure of the tables would be something like this:

users

| Employee - INT 
| FName - VarChar 
| LName - Varchar 
| Campaign_ID - INT 
| Active - BOOLEAN

schedule

| Employee - INT 
| Year - INT 
| Week - INT 
| Mon - VarChar 
| Tue - VarChar 
| Wed - VarChar (etc etc)

logs

| UserName - INT 
| Time - VarChar 
| Date - Varchar 
| Status - Varchar

campaigns

| Campaign_ID - INT 
| Name - VarChar

Query

SET @Fecha = '4/23/2018' ;

SELECT c.'Name',u.'Employee',u.'FName',u.'LName',s.'Tue' ,l1.'Time' as 'Check In',l2.'Time' as 'Check Out',TIMEDIFF(l1.'Time',l2.'Time') as 'Worked Hours' FROM
'users' u, 'campaigns' c, 'schedule' s, 'logs' l1, 'logs' l2 
WHERE 
u.'Active' = 1 
AND c.'Campaign_ID' = u.'Campaign_ID'
AND l1.'UserName' = u.'Employee'
AND l2.'UserName' = u.'Employee'
AND l1.'Date' = @Fecha
AND l2.'Date' = @Fecha
AND l1.'Status' = 'Clocked In'
AND l2.'Status' = 'Clocked Out'
AND l2.'Time' = MAX(l2.'Time')
AND l1.'Time' = MIN(l1.'time')
AND s.'Employee' = u.'Employee'
AND s.'Year' = Year(@Fecha)
AND s.'Week' = Week(@Fecha,6)

This is the query I try to use, but obviously it does not work. I want to avoid making queries to the logs table for each of the 400 employees

Previously I had an application that loaded in Data Set the information of all 400 users, in another Data Set loaded all the schedules, so by For Loop generated two queries for each user in Data Set for See the arrival time and the departure time.

Afterwards, the application calculated the difference in hours and accommodated them within the report, this took up to 10 minutes, which I think is too much time for something relatively simple. Also the computers that we have available are not the most powerful that we can say, however we are renting a server that has very good specifications, so I think it is more convenient to give more load to the server.

P.D. I am aware of the sin that we committed when we put the time and date as VarChar , but the applications that are used to take assistance so specified, we are in the process of changing that issue and managing those two fields by TimeStamp , meanwhile we still have little more than a million records in logs with which we still have to deal.

Thank you in advance.

mysql

asked by Neoz Memphisto 24.04.2018 в 17:31

source

1 answer

Error in Ada language (raised TASKING_ERROR: s-tasren.adb: 446) webpack + javascript

score 0 · Accepted Answer

Try the query like this:

SELECT 
    c.'Name',
    u.'Employee',
    u.'FName',
    u.'LName',
    s.'Tue' ,
    l1.'Time' Check_In,
    l2.'Time' Check_Out,
    TIMEDIFF(l1.'Time',l2.'Time') 'Worked_Hours' 

FROM 'logs' l1
    INNER JOIN 'users'     u   ON   l1.'UserName'   =   u.'Employee'
    INNER JOIN 'campaigns' c   ON   c.'Campaign_ID' =   u.'Campaign_ID'
    INNER JOIN 'schedule'  s   ON   s.'Employee'    =   u.'Employee'
    INNER JOIN 'logs'      l2  ON   l2.'UserName'   =   u.'Employee'

WHERE 
        u.'Active' = 1 
    AND l.'Date' = @Fecha
    AND l1.'Status' = 'Clocked In'
    AND l2.'Status' = 'Clocked Out'
    AND l2.'Time' = MAX(l2.'Time')
    AND l1.'Time' = MIN(l1.'time')
    AND s.'Year' = Year(@Fecha)
    AND s.'Week' = Week(@Fecha,6)
;

Anyway, if the indexes of the tables are not well established, it is likely that the queries will slow down, and while the BD grows it will be worse. It is convenient that you design a safe strategy to normalize the data.

I do not know if the use of TIMEDIFF within the query is also convenient, or if it would not be better to do that calculation by programming. It is a matter of doing tests, to choose what is most convenient.