Query calcolo ore lavorate

di il
12 risposte

Query calcolo ore lavorate

Salve,
non riesco a realizzare questa query:

Ho una tabella composta da 3 campi

UserID       DataOra                Tipo
1            2015-06-21 07:34:00    I
1            2015-06-21 07:50:00    O
1            2015-06-21 16:00:00    I
1            2015-06-21 16:48:00    O
2            2015-06-21 07:08:00    I
2            2015-06-21 13:48:00    O
2            2015-06-21 17:08:00    I
Ho realizzato una query che mi calcola i minuti lavorati prendendo la prima e l'ultima registrazione di ogni giorno ma non va bene in quanto ogni utente può avere per lo stesso giorno più entrate e uscite

Qui la query mi dovrebbe calcolare i minuti per ogni coppia cronologica tra ingresso e uscita

Del tipo:

UserID    DataOraIN             DataOraOut             Minuti
1         2015-06-21 07:34:00   2015-06-21 07:50:00    16
1         2015-06-21 16:00:00   2015-06-21 16:48:00    48
C'è qualcuno che riesce ad aiutarmi ?

Grazie mille

12 Risposte

  • Re: Query calcolo ore lavorate

    A posto, ho risolto
  • Re: Query calcolo ore lavorate

    Ciao,

    sono nuovo del forum, piacere.

    Ho il tuo stesso problema.

    Puoi indicarmi la SQL che hai utilizzato per creare la tabella ingressi uscite con orari ?

    Ti ringrazio !

    Un Saluto

    Paolo
  • Re: Query calcolo ore lavorate

    Ciao,
    partendo dalla tabella delle registrazioni "checkinout" composta da 3 campi: Userid, CheckTime e CheckType, ho realizzato 3 query:

    Le prime 2 mi raggruppano le entrate e le uscite e le ho denominate QInOut e QInOut2

    QInOut
    
    select `a`.`Userid` AS `Userid`,cast(`a`.`CheckTime` as date) AS `DataInOut`,cast(`a`.`CheckTime` as time) AS `Ingresso`,cast(coalesce((select min(`b`.`CheckTime`) from `checkinout` `b` where ((`a`.`Userid` = `b`.`Userid`) and (cast(`a`.`CheckTime` as date) = cast(`b`.`CheckTime` as date)) and (`b`.`CheckTime` >= `a`.`CheckTime`) and (`b`.`CheckType` = 1))),NULL) as time) AS `Uscita` from `checkinout` `a` where (`a`.`CheckType` = 0) order by `a`.`CheckTime`,`a`.`Userid`
    
    QInOut2
    
    select `c`.`Userid` AS `Userid`,cast(`c`.`CheckTime` as date) AS `DataInOut`,cast(coalesce((select min(`d`.`CheckTime`) from `V_Record` `d` where ((`c`.`Userid` = `d`.`Userid`) and (cast(`c`.`CheckTime` as date) = cast(`d`.`CheckTime` as date)) and (`d`.`CheckTime` <= `c`.`CheckTime`) and (`d`.`CheckType` = 0))),NULL) as time) AS `Ingresso`,cast(`c`.`CheckTime` as time) AS `Uscita` from `checkinout` `c` where (`c`.`CheckType` = 1) order by `c`.`CheckTime` desc,`c`.`Userid`
    
    La terza invece effettua l'unione delle due precedenti e mi consente di avere su 2 colonne l'orario di ingresso e uscita, da qui poi puoi effettuare i vari calcoli sulle ore lavorate

    QIngressiUscite:
    
    select `QInOut`.`Userid` AS `Userid`,`QInOut`.`DataInOut` AS `DataInOut`,`QInOut`.`Ingresso` AS `Ingresso`,`QInOut`.`Uscita` AS `Uscita` from `QInOut` union all select `QInOut2`.`Userid` AS `Userid`,`QInOut2`.`DataInOut` AS `DataInOut`,`QInOut2`.`Ingresso` AS `Ingresso`,`QInOut2`.`Uscita` AS `Uscita` from `QInOut2` where (isnull(`QInOut2`.`Ingresso`) or isnull(`QInOut2`.`Uscita`)) order by `DataInOut`
    
    Spero di esserti stato utile

    Ciao !
  • Re: Query calcolo ore lavorate

    Grazie mille ! Non sei stato utile, sei stato super utile !!!

    Paolo
  • Re: Query calcolo ore lavorate

    Mi fà piacere, quanto meno hai ricevuto la dritta che io non ho trovato
  • Re: Query calcolo ore lavorate

    Ciao,

    una domanda : per te CheckType è un numerico...quindi 1 è ingresso mentre 0 è uscita ? Io ho un campo testo con INT per ingresso ed EXT per uscita. Posso sostituirli nelle tue query ?

    Scusa ma sei stato davvero prezioso
  • Re: Query calcolo ore lavorate

    Si checktype è numerico 0 = ingresso e 1 = uscita
    Puoi cambiare il tipo in stringa e mettere il valore tra apici

    Ciao
  • Re: Query calcolo ore lavorate

    Perfetto grazie mille.

    Domanda, le 3 query le hai poi realizzate con delle viste ?

    Oppure hai creato tre tabelle nuove partendo dalla prima che contiene i dati ?

    Grazie
  • Re: Query calcolo ore lavorate

    Ho creato 3 viste, in mysql
  • Re: Query calcolo ore lavorate

    Ciao grazie mille.

    Sto provando a creare le viste ma si blocca tutto con uso cpu al 100% per il processo mysqld...non termina proprio di creare la vista. Eppure la query secca la fa senza problemi o rallentamenti ... idee ?

    tnx

    paolo
  • Re: Query calcolo ore lavorate

    Non saprei aiutarti, credo sia più un problema di mysql che della creazione delle viste
    sei sicuro di aver assegnato i campi correttamente ?
    se prima di creare la vista provi a farla parte come query, funziona ?
    mysql gira in locale oppure in remoto ?
  • Re: Query calcolo ore lavorate

    Ciao,
    i campi sono assegnati bene e la query "secca" funziona bene.

    Il problema era legato alla numerosità di righe che stavo elaborando ... ho creato alcuni indici e si è velocizzato.

    Ora comunque lavoro su ingressi e uscite del mese corrente così alleggerisco il carico di lavoro.

    L'ultimo giorno del mese consolido e sposto i record del mese su una tabella "consolidata" esterna che è comunque più efficente di una vista (che peraltro è rallentata dato che usi una subquery).

    Grazie mille comunque per l'aiuto

    davvero prezioso

    Paolo
Devi accedere o registrarti per scrivere nel forum
12 risposte