[Query per calcolo del Tempo] Esclusione Tempi

[Aires]

Ciao a tutti, avrei bisogno di aiuto con una query, trattandosi di puro Sql ho deciso di postare qua, tra l'altro sono già state affrontate altre query in questa sezione.

Allora devo fare una query particolare e mi sto perdendo da circa due giorni, spiego il tutto con un esempio.


Nome Tabella: Impiegati
Campi: Nome, Presenza, Orario

Allora mettiamo caso che la tabella sia già compilata così:

Giovanni - 14:00 - 0 [0=entra in ufficio]
Giovanni - 14:30 - 1 [1=sta lavorando]
Giovanni - 14:45 - 2 [2= va a fare un giro]
Giovanni - 14:50 - 3 [3=sta lavorando]
Giovanni - 15:00 - 5 [5=chiude tutto e va a casa]

Da questo esempio Giovanni è stato 1 ora fuori casa, però in ufficio ha lavorato solo 55 minuti perchè i 5 minuti con Presenza = 2 non era in ufficio.

Il mio problema sorge qui, io riesco tranquillamente a calcolare i tempi impiegati nelle operazioni, il tempo tra entrata e uscita eccetera.
Quindi riesco a ricavare il tempo che passa tra Presenza = 5 e Presenza = 0 però non so come fare per far calcolare il tempo tra Presenza=2 (va in giro) e lo stato successivo in modo da non calcolare questo tempo

Quindi:
Calcolo tempo tra stato= 0 e stato = 5 e ci tolgo il tempo che passa da quando imposto lo stato 4 a quando lo tolgo.

Il codice usato per ora per il calcolo del tempo totale era questo:

Select Sec_to_time(unix_timestamp(max(data))-unix_timestamp(min(data))) from nome_tabella where nomecampo="valore"

Grazie a tutti per eventuali consigli. Se qualcosa non è chiaro chiedete pure

[Aires]

Uppuccio

[nicola75ss]

Per le domande relative a mysql esiste la sezione php.

L'idea di massima dovrebbe essere questa:

select *, 
sec_to_time(unix_timestamp(stato_5) - unix_timestamp(stato_0) - if(stato_2 is not null,unix_timestamp(stato_3) - unix_timestamp(stato_2),0)) as tempo
from (
select nome,
max(case when stato = 0 then orario else null end ) as stato_0,
max(case when stato = 1 then orario else null end ) as stato_1,
max(case when stato = 2 then orario else null end ) as stato_2,
max(case when stato = 3 then orario else null end ) as stato_3,
max(case when stato = 5 then orario else null end ) as stato_5
from tabella
group by nome) as tab

Presumo che i tuoi campi siano di tipo datetime e non time come nel tuo esempio e che debba raggruppare non solo per nome.

[Aires]

Ciao!
Grazie mille per la risposta ora mi studio un po il codice che mi hai dato, comunque avevo postato qua perchè il problema non lo avevo con php ma solo con sql, la prossima volta posterò la
Per i campi data in realtà sono così inseriti: 2010-02-12 09:26:17
Ora comunque vedo se risolvo con ciò da te postato. Grazie ancora

[nicola75ss]

Il campo è di tipo datetime. Era ovvio visto che nella query usavi la funzione unix_timestamp.

[Aires]

Ciao, grazie ancora.
Allora ho provato il codice e sono arrivato a questo punto:

Select sec_to_time(unix_timestamp(stato_1) - unix_timestamp(stato_0) - 
if(stato_5 is not null,unix_timestamp(stato_2) - unix_timestamp(stato_5),0)) 
as tempooooooooooo 
from ( select campo1, 
max(case when stato_intervento = 0 then data else null end ) as stato_0, 
max(case when stato_intervento = 1 then data else null end ) as stato_1, 
max(case when stato_intervento = 2 then data else null end ) as stato_2, 
max(case when stato_intervento = 3 then data else null end ) as stato_3, 
max(case when stato_intervento = 5 then data else null end ) as stato_5 
from tabella where campo2="29" group by campo2) as tab 

Bene allora:
stato 1 = chiuso
stato 0 = aperto
stato 5 = attesa

Allora in questo caso funziona perchè non mi calcola il tempo 'in attesa' in quanto a un certo punto faccio stato 2 - stato 5 e in database abbiamo dopo l'operazione con stato 5 una con stato 2.

Quindi se in db avevo
stato: 5 azione:14:00
stato: 3 azione:14:01

avrei dovuto modificare e fare stato 3 - stato 5.

Quindi fin qui il codice funge perfettamente, unico problema è che io non posso sapere a priori lo stato successivo al 5, ossia l'azione può essere in ogni stato ed essendoci migliaia di record dovrei trovare una soluzione che sottragga il tempo che va dallo stato 5 fino al successivo (sempre che quello dopo sia diverso da 5)

quindi il codice sarebbe (credo) così

Select sec_to_time(unix_timestamp(stato_1) - unix_timestamp(stato_0) - 
if(stato_5 is not null,[?????????]- unix_timestamp(stato_5),0)) 
as tempooooooooooo 
from ( select campo1, 
max(case when stato_intervento = 0 then data else null end ) as stato_0, 
max(case when stato_intervento = 1 then data else null end ) as stato_1, 
max(case when stato_intervento = 2 then data else null end ) as stato_2, 
max(case when stato_intervento = 3 then data else null end ) as stato_3, 
max(case when stato_intervento = 5 then data else null end ) as stato_5 
from tabella where campo2="29" group by campo2) as tab 

Dove in [?] andrebbe la specifica per fargli capire stato successivo

Se non sono stato chiaro chiedete pure.
Grazie mille

[nicola75ss]

Senza conoscere le varie casistiche è difficile darti una risposta.
Quanti sono gli stati possibili?
Dovresti postare un dump della tabella con diversi record di esempio che coprano le varie eventualità e il risultato atteso.

[Aires]

Ok ora faccio un esempio completo:


Id - Data - Stato_intervento
1 - 10:00 - 0
1 - 10:20 - 3
1 - 10:30 - 5
1 - 14:00 - 2
1 - 14:40 - 5
1 - 15:00 - 4
1 - 16:00 - 1
2 - 09:00 - 0
2 - 10:00 - 5
2 - 10:30 - 0
2 - 11:00 - 1

Dove 0 = Aperto, 1 = Chiuso, 5= Attesa, altri numeri= altri stati

A me serve:
Tempo Totale dell'intervento =

Per l'id 1 --> Apro alle 10, chiudo alle 16 --> tempo totale 6 ore, qua però mi serve sapere quanto tempo ho impiegato senza contare l'attesa quindi togliere le 3ore e 50 che sono in attesa
( dalle 10e30 alle 15 ) e (dalle 14.40 alle 15).

Il problema è che lavoro con 14.000 record circa, in continuo aumento e avrei bisogno di fare in modo che venga calcolato il tempo tra apertura min(data) per ogni id e data chiusura (quindi max(data) where stato=1) e da questa differenza toglierci i tempi per i quali è in attesa, quindi calcolare il tempo che passa tra impostazione stato 5 e fine di questo

purtroppo sono bloccato davvero, stò provando a cambiare ecc e mi pare che la soluzione più vicina è quella postata sopra, però anche la non funge

Grazie mille per la pazienza

[Aires]

up

[nicola75ss]

Speriamo sia la volta buona

CREATE TABLE `presenze` (
  `prog` int(11) NOT NULL auto_increment,
  `id` int(11) default NULL,
  `data` datetime default NULL,
  `stato` int(11) default NULL,
  PRIMARY KEY  (`prog`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

/*Data for the table `presenze` */

insert into `presenze` (`prog`,`id`,`data`,`stato`) values (1,1,'2010-01-01 10:00:00',0);
insert into `presenze` (`prog`,`id`,`data`,`stato`) values (2,1,'2010-01-01 10:20:00',3);
insert into `presenze` (`prog`,`id`,`data`,`stato`) values (3,1,'2010-01-01 10:30:00',5);
insert into `presenze` (`prog`,`id`,`data`,`stato`) values (4,1,'2010-01-01 14:00:00',2);
insert into `presenze` (`prog`,`id`,`data`,`stato`) values (5,1,'2010-01-01 14:40:00',5);
insert into `presenze` (`prog`,`id`,`data`,`stato`) values (6,1,'2010-01-01 15:00:00',4);
insert into `presenze` (`prog`,`id`,`data`,`stato`) values (7,1,'2010-01-01 16:00:00',1);
insert into `presenze` (`prog`,`id`,`data`,`stato`) values (8,2,'2010-01-01 09:00:00',0);
insert into `presenze` (`prog`,`id`,`data`,`stato`) values (9,2,'2010-01-01 10:00:00',5);
insert into `presenze` (`prog`,`id`,`data`,`stato`) values (10,2,'2010-01-01 10:30:00',0);
insert into `presenze` (`prog`,`id`,`data`,`stato`) values (11,2,'2010-01-01 11:00:00',1);


select tab.id2 as id,sec_to_time(sum(time_to_sec(diff))) as totale from (
select p1.*, p2.id as id2,p2.data as data2,p2.stato as stato2,
if(p1.stato<>5,sec_to_time(unix_timestamp(p2.data) - unix_timestamp(p1.data)),0) as diff
from presenze as p1, presenze as p2
where p1.data < p2.data and p1.id = p2.id
group by p1.prog
order by p1.prog) as tab
group by tab.id2

edit. Come tu stesso puoi vedere ho provato la query su un numero misero di record. Fai le opportune verifiche su un campione rappresentativo per accertarti che fili liscio in tutte le circostanze.