[MySQL] da start time, end time ad intervalli temporali con cambio di data

[xp2400]

Ho una tabella così fatta.

Data, Attività, Inizio, Fine
12/05/2014, Lavoro, 08:00, 9:30
12/05/2014, Pausa, 09:30, 09:45
12/05/2014, Lavoro, 23:30, 00:20.

Devo effettuare una query che restituisca grosso modo

Data, Intervallo, Attività, Durata
12/05/2014,08:00, Lavoro, 900 (secondi)
12/05/2014,08:15, Lavoro, 900
12/05/2014,08:30, Lavoro, 900
12/05/2014,08:45, Lavoro, 900
12/05/2014,09:00, Lavoro, 900
12/05/2014,09:15, Lavoro, 900
12/05/2014,09:30, Pausa, 900
12/05/2014,23:30, Lavoro, 900
12/05/2014,23:45, Lavoro, 900
13/05/2014,00:00 Lavoro, 900
13/05/2014,00:15, Lavoro, 300

e mi sto veramente incartando..... HELP!
graz8e

[Daniels118]

Se gli orari nella tua tabella coprono sempre intervalli di almeno 15 minuti puoi adottare il seguente metodo.

Prima di tutto creara una tabella di appoggio nella quale inserirai gli orari della giornata (i quarti d'ora).
Poi crea la query facendo una inner join tra la tua tabella e la tabella di appoggio, specificando come criterio che l'orario della tabella di appoggio sia compreso negli orari di inizio e fine della tua tabella.
Resta da calcolare la durata, per farlo è sufficiente sottrarre l'orario di appoggio all'orario di fine, e limitare il risultato a 900 secondi con IIF.
Lascio a te la ricerca delle funzioni per confrontare e convertire date e orari.

[xp2400]

ho creato una tabella con gli orari (a mezz'ora) che si chiama W_INT30.

il risultato l'ho ottenuto (PU--> non ha significato, il controllo sull'attività non ci interessa), I_Att è l'ora inizio attività, F_att l'ora di Fine Attività, I_Fasc ora di iniziofascia e F_Fascia ora di fine fascia.


a questo punto ho un grosso problema di performance.

il campo data (nella tabella originale) è indicizzato.

tuttavia il campo data di output della query è diverso in quanto varia se le attività sono a cavallo di mezza notte. Quindi se nella tabella originale il record era


12/05/2014, Lavoro, 23:30, 00:20.


il risultato per fascia è
data, attivita, I_Fasc, F_fascia, Durata

12/05/2014, Lavoro, 23:30, 24:00, 1800
13/05/2014, Lavoro, 00:00, 00:30, 1200

ma saltano tutti gli indici, e una query da pochi secondi dura mezz'ora..



select ID, PU, DATA,

Attivita, Nome,I_Att,F_att, I_fasc,F_Fasc,

case
when ((I_att <= I_Fasc) and (F_Att >= F_Fasc)) then time_to_sec(timediff (f_Fasc, I_Fasc) ) /* attivita esterna fascia */
when ((I_att>= I_Fasc) and (F_Att <= F_Fasc)) then time_to_sec(Timediff(F_att,I_att)) /*attivita interna ad una fascia */
when ((I_att<= i_fASC) AND (f_ATT <= f_FASC) and (F_Att>= I_Fasc))THEN time_to_sec(TIMEDIFF (f_ATT,i_FASC)) /* ATTIVITA FINISCE IN una fascia */
when ((I_att>=I_fasc) and (F_att>= F_fasc) and (I_att <= F_Fasc)) then time_to_Sec(timediff( f_fasc,I_att)) /* attivita inizia in una fascia */
end as Durata,
case
when (I_att=F_att) and hour(I_att) = 0 then 'SI'end as Giorno_Intero
,
case when instr (Attivita,' - ') > 0 then substring_index(Attivita,' - ',2)
else (/***** SE ATTIVITA non contiente ' - '> ATTIVITA = PRECEDENTE **/
/******* la prima attività potrebbe non esistere.....*/
select
substring_index(Attivita,' - ',2)
from Attivita
where data >=SUBQ.Data and id=SUBQ.ID
and instr (Attivita,' - ') > 0
order by Data desc, Inizio desc limit 0, 1
)
end Campagna

from



/*____SUB --> FULL JOIN ATTIVITA e ORARI____*/
/*____N.B. MYSQL non supporta il fulljoin, quindi è un prodotto cartesiano */
(select
id, pu,
nome, Attivita,

case when (Att.fine < Att.Inizio) and (Fasce.Inizio < Att.Inizio) /*____________case per gestire la corretta attribuzione delle H per giorno su turni cross midnight*/
then addtime(data, '24:00:00')
else data
end as Data,
addtime(data,Att.Inizio) as I_att,
addtime(addtime(data,Att.Inizio),durata)

as F_Att,
case when (Att.fine < Att.Inizio) and (Fasce.Inizio < Att.Inizio)
then addtime(addtime(data, '24:00:00'),Fasce.Inizio)
else addtime(data,Fasce.Inizio)
end as I_Fasc,
case when (Att.fine < Att.Inizio) and (Fasce.Inizio < Att.Inizio)
then addtime(addtime(addtime(data, '24:00:00'),Fasce.Inizio),'00:30:00')
else addtime(addtime(data,Fasce.Inizio),'00:30:00')
end as f_Fasc


from Attivita Att, W_Int30 Fasce

/*_____________ Filtro da eliminare__________*/
Where






/*F_FASCIA > I_ATT*/
(case when (Att.fine < Att.Inizio) and (Fasce.Inizio < Att.Inizio)
then addtime(addtime(addtime(data, '24:00:00'),Fasce.Inizio),'00:30:00')
else addtime(addtime(data,Fasce.Inizio),'00:30:00')
end /*as f_Fasc */) >
addtime(data,Att.Inizio)/* I_att, */
and
/*I_Fascia > F_ATT*/
(case when (Att.fine < Att.Inizio) and (Fasce.Inizio < Att.Inizio)
then addtime(addtime(data, '24:00:00'),Fasce.Inizio)
else addtime(data,Fasce.Inizio)
end) < addtime(addtime(data,Att.Inizio),durata)

and

(case when (Att.fine < Att.Inizio) and (Fasce.Inizio < Att.Inizio)
then addtime(addtime(addtime(data, '24:00:00'),Fasce.Inizio),'00:30:00')
else addtime(addtime(data,Fasce.Inizio),'00:30:00')
end /*as f_Fasc */) >
addtime(data,Att.Inizio)/* I_att, */


) as SUBQ
/*____________________________________________*/





order by data, Pu, id, Nome

[Daniels118]

Il mio suggerimento era quello di fare una inner join, ma vedo che hai adottato un approccio diverso, hai provato con il metodo che ti avevo suggerito? Non andava bene?

Suggerimento: usa i tag code ed indenta il codice, senza è illeggibile.

[xp2400]

Il mio suggerimento era quello di fare una inner join, ma vedo che hai adottato un approccio diverso, hai provato con il metodo che ti avevo suggerito? Non andava bene?

Suggerimento: usa i tag code ed indenta il codice, senza è illeggibile.

Daniel,
ti ringrazio ma non ho capito COME fare l'inner join...
ho fatto un full join implicito..
perchè con l'inner join se una attività va dalle 14:00 alle 20:00
che regole metto ? ... un aiutino?:mc:

[Daniels118]

Vedi se così funziona:
SELECT IF(attivita.inizio <= w_int30.inizio, attivita.data, date_add(attivita.data, INTERVAL 1 DAY)) AS data,
w_int30.inizio AS intervallo,
attivita.attivita,
LEAST(TIME_TO_SEC(TIMEDIFF(
TIMESTAMP(IF(attivita.inizio <= attivita.fine, attivita.data, DATE_ADD(attivita.data, INTERVAL 1 DAY)), attivita.fine),
TIMESTAMP(IF(attivita.inizio <= w_int30.inizio, attivita.data, DATE_ADD(attivita.data, INTERVAL 1 DAY)), w_int30.inizio)
)), 1800) AS durata
FROM attivita
INNER JOIN w_int30 ON (attivita.inizio <= w_int30.inizio AND w_int30.inizio < attivita.fine)
OR (
attivita.inizio > attivita.fine AND
(attivita.inizio <= w_int30.inizio OR w_int30.inizio < attivita.fine)
)
ORDER BY data, w_int30.inizio
Fai dei test perché non l'ho provato, ho verificato solo la sintassi.

[xp2400]

Vedi se così funziona:
SELECT IF(attivita.inizio <= w_int30.inizio, attivita.data, date_add(attivita.data, INTERVAL 1 DAY)) AS data,
w_int30.inizio AS intervallo,
attivita.attivita,
LEAST(TIME_TO_SEC(TIMEDIFF(
TIMESTAMP(IF(attivita.inizio <= attivita.fine, attivita.data, DATE_ADD(attivita.data, INTERVAL 1 DAY)), attivita.fine),
TIMESTAMP(IF(attivita.inizio <= w_int30.inizio, attivita.data, DATE_ADD(attivita.data, INTERVAL 1 DAY)), w_int30.inizio)
)), 1800) AS durata
FROM attivita
INNER JOIN w_int30 ON (attivita.inizio <= w_int30.inizio AND w_int30.inizio < attivita.fine)
OR (
attivita.inizio > attivita.fine AND
(attivita.inizio <= w_int30.inizio OR w_int30.inizio < attivita.fine)
)
ORDER BY data, w_int30.inizio
Fai dei test perché non l'ho provato, ho verificato solo la sintassi.

grazie! collaudo e vediamo....

[xp2400]

Questi i record

5528 CC_Roma 08:00:00 2014-06-16 00:00:00 09:40:00 01:40:00 Help Line - Cartasi' Inbound - Telefono
5528 CC_Roma 09:40:00 2014-06-16 00:00:00 09:55:00 00:15:00 Pausa
5528 CC_Roma 09:55:00 2014-06-16 00:00:00 12:00:00 02:05:00 Help Line - Cartasi' Inbound - Telefono
5528 CC_Roma 12:00:00 2014-06-16 00:00:00 12:30:00 00:30:00 Pranzo_NR
5528 CC_Roma 12:30:00 2014-06-16 00:00:00 14:30:00 02:00:00 Help Line - Cartasi' Inbound - Telefono
5528 CC_Roma 14:30:00 2014-06-16 00:00:00 14:45:00 00:15:00 Pausa
5528 CC_Roma 14:45:00 2014-06-16 00:00:00 16:30:00 01:45:00 Help Line - Cartasi' Inbound - Telefono


questa la query.


select Id,Pu, Data, A.Inizio,A.Fine, Durata, Attivita,
W.Inizio, W.Fine,
IF(A.inizio <= W.inizio, A.data,
date_add(A.data, INTERVAL 1 DAY)) AS dataReale,
LEAST(TIME_TO_SEC(TIMEDIFF(
TIMESTAMP(IF(A.inizio <=A.fine, A.data, DATE_ADD(A.data, INTERVAL 1 DAY)), A.fine),
TIMESTAMP(IF(A.inizio <= W.inizio, A.data, DATE_ADD(A.data, INTERVAL 1 DAY)),W.inizio)
)), 1800) AS duratareale




from Attivita A INNER join
W_Int30 W

ON (A.inizio <=W.inizio AND W.inizio < A.fine)
OR (
A.inizio > A.fine AND
(A.inizio <= W.inizio OR W.inizio < A.fine)
)


where Data ='2014-06-16'
and Pu = 'CC_Roma'
and Id = 5528

order by id, DataReale,W.Inizio


mi fa come risultato

5528 CC_Roma 2014-06-16 00:00:00 08:00:00 09:40:00 01:40:00 Help Line - Cartasi' Inbound - Telefono 08:00:00 08:30:00 2014-06-16 00:00:00 1800
5528 CC_Roma 2014-06-16 00:00:00 08:00:00 09:40:00 01:40:00 Help Line - Cartasi' Inbound - Telefono 08:30:00 09:00:00 2014-06-16 00:00:00 1800
5528 CC_Roma 2014-06-16 00:00:00 08:00:00 09:40:00 01:40:00 Help Line - Cartasi' Inbound - Telefono 09:00:00 09:30:00 2014-06-16 00:00:00 1800
5528 CC_Roma 2014-06-16 00:00:00 08:00:00 09:40:00 01:40:00 Help Line - Cartasi' Inbound - Telefono 09:30:00 10:00:00 2014-06-16 00:00:00 600
5528 CC_Roma 2014-06-16 00:00:00 09:55:00 12:00:00 02:05:00 Help Line - Cartasi' Inbound - Telefono 10:00:00 10:30:00 2014-06-16 00:00:00 1800
5528 CC_Roma 2014-06-16 00:00:00 09:55:00 12:00:00 02:05:00 Help Line - Cartasi' Inbound - Telefono 10:30:00 11:00:00 2014-06-16 00:00:00 1800
5528 CC_Roma 2014-06-16 00:00:00 09:55:00 12:00:00 02:05:00 Help Line - Cartasi' Inbound - Telefono 11:00:00 11:30:00 2014-06-16 00:00:00 1800
5528 CC_Roma 2014-06-16 00:00:00 09:55:00 12:00:00 02:05:00 Help Line - Cartasi' Inbound - Telefono 11:30:00 12:00:00 2014-06-16 00:00:00 1800
5528 CC_Roma 2014-06-16 00:00:00 12:00:00 12:30:00 00:30:00 Pranzo_NR 12:00:00 12:30:00 2014-06-16 00:00:00 1800
5528 CC_Roma 2014-06-16 00:00:00 12:30:00 14:30:00 02:00:00 Help Line - Cartasi' Inbound - Telefono 12:30:00 13:00:00 2014-06-16 00:00:00 1800
5528 CC_Roma 2014-06-16 00:00:00 12:30:00 14:30:00 02:00:00 Help Line - Cartasi' Inbound - Telefono 13:00:00 13:30:00 2014-06-16 00:00:00 1800
5528 CC_Roma 2014-06-16 00:00:00 12:30:00 14:30:00 02:00:00 Help Line - Cartasi' Inbound - Telefono 13:30:00 14:00:00 2014-06-16 00:00:00 1800
5528 CC_Roma 2014-06-16 00:00:00 12:30:00 14:30:00 02:00:00 Help Line - Cartasi' Inbound - Telefono 14:00:00 14:30:00 2014-06-16 00:00:00 1800
5528 CC_Roma 2014-06-16 00:00:00 14:30:00 14:45:00 00:15:00 Pausa 14:30:00 15:00:00 2014-06-16 00:00:00 900
5528 CC_Roma 2014-06-16 00:00:00 14:45:00 16:30:00 01:45:00 Help Line - Cartasi' Inbound - Telefono 15:00:00 15:30:00 2014-06-16 00:00:00 1800
5528 CC_Roma 2014-06-16 00:00:00 14:45:00 16:30:00 01:45:00 Help Line - Cartasi' Inbound - Telefono 15:30:00 16:00:00 2014-06-16 00:00:00 1800
5528 CC_Roma 2014-06-16 00:00:00 14:45:00 16:30:00 01:45:00 Help Line - Cartasi' Inbound - Telefono 16:00:00 16:30:00 2014-06-16 00:00:00 1800

e si perde quindi il record

5528 CC_Roma 09:40:00 2014-06-16 00:00:00 09:55:00 00:15:00 Pausa


AAARGH!!!

[Daniels118]

Puoi mettere i nomi dei campi della tabella? Ci sono più colonne "ora" di quelle che mi aspetterei, non riesco a distinguere quelle che ci interessano, poi per favore, inserisci le query nei tag code in modo che vengano preservati gli spazi, altrimenti la lettura risulta difficoltosa.
Non avendo ben capito quali sono le colonne non ho ancora analizzato il problema, però ti ripeto, la query che ho proposto funziona solo se la durata delle attività è sempre maggiore o uguale a quella degli intervalli (in altre parole deve esserci la garanzia che l'intervallo inizi durante l'attività), in caso contrario bisogna modificare la condizione di join affinché vengano selezionati gli intervalli che comprendono del tutto o in parte l'attività.

[xp2400]

Puoi mettere i nomi dei campi della tabella? Ci sono più colonne "ora" di quelle che mi aspetterei, non riesco a distinguere quelle che ci interessano, poi per favore, inserisci le query nei tag code in modo che vengano preservati gli spazi, altrimenti la lettura risulta difficoltosa.
Non avendo ben capito quali sono le colonne non ho ancora analizzato il problema, però ti ripeto, la query che ho proposto funziona solo se la durata delle attività è sempre maggiore o uguale a quella degli intervalli (in altre parole deve esserci la garanzia che l'intervallo inizi durante l'attività), in caso contrario bisogna modificare la condizione di join affinché vengano selezionati gli intervalli che comprendono del tutto o in parte l'attività.

ho trovato...... :D :cool: :cool: mi hai illuminato lo stesso...

[Daniels118]

ho trovato...... :D :cool: :cool: mi hai illuminato lo stesso...
Illumina anche noi! :D