Aiuto Query difficile

[dux86]

Ciao a tutti..
purtroppo non sono un esperto nelle query, sono soltanto uno smanettone, pertanto volevo chiedervi un grosso aiuto a farmi una query che mi risparmierà ore e ore di fatica e tempo.

Premessa.
Tanto per capire a cosa mi serve.. sono un amministratore di una stradina privata, abbiamo un cancello che storicizza nel DB tutte le entrate e le uscite dei condomini. Il nostro condominio prevede che non si può sostare più di 30 minuti dentro la stradina privata, pertanto chi viola le regole viene multato.
Detto ciò, devo creare una query che mi faccia il lavoro.

Ora al lato pratico.
Tramite un tool di mysql ho importato i dati da MDB(access) a MySQL.

ecco la query per la db/tabella:
CREATE TABLE `controllo`.`Events` (
`Index` INT(10) NULL,
`Date` VARCHAR(255) NULL,
`Time` TIME NULL,
`Access_point` VARCHAR(255) NULL,
`Card` VARCHAR(255) NULL
)
ENGINE = INNODB;


La quale viene popolata

INSERT INTO `controllo`.`Events`(`Index`, `Date`, `Time`, `Access_point`, `Card`) VALUES
(21794, '15/09/2010', '19:33:00', 'entrata', '6C0649'),
(21795, '15/09/2010', '19:33:00', 'uscita', '54FBD5'),
(21796, '15/09/2010', '19:35:00', 'entrata', '223492'),
(21797, '15/09/2010', '19:44:00', 'uscita', '223492'),
(21798, '15/09/2010', '19:48:00', 'uscita', '219F4F'),
(21799, '15/09/2010', '20:07:00', 'uscita', '6C0649'),
(21800, '15/09/2010', '20:19:00', 'entrata', '225645'),
(21801, '15/09/2010', '20:21:00', 'entrata', '54FBFC'),
(21802, '15/09/2010', '20:38:00', 'entrata', '54FBEF'),
(21803, '15/09/2010', '20:45:00', 'uscita', '225645'),
(21804, '15/09/2010', '20:47:00', 'uscita', '54FBFC'),
(21805, '15/09/2010', '20:50:00', 'entrata', '6C437F'),
(21806, '15/09/2010', '20:53:00', 'uscita', '6C437F'),
(21807, '15/09/2010', '20:56:00', 'uscita', '54FBEF');

ovviamente le sono in ordine di qualche migliaio al mese, quindi capite la difficoltà del lavoro..


Quello che mi dovrebbe fare la query è l'operazione seguente(cerchero di essere il più chiaro possibile, come esempio prendo la tessera n.6C0649 evidenziata in grassetto):

In base al numero progressivo dei accessi (Index) e della data (Date) trovarmi solo quelle schede (Card) che dal momento dell'entrata al momento dell'uscita abbiano impiegato più di 30 minuti, ovvero come da esempio:

la card n.6C0649 in data 15/09/2010 ha effettuato l'accesso alle ore 19:33:00 ed effettuato l'uscita alle ore 20:07:00, quindi 20:07:00 - (meno) 19:33:00 = 00:34:00 minuti

 

[Trogo]

Mi sembra che la query:

SELECT *, (uscita - entrata) as delta FROM
(
(
SELECT DISTINCT UNIX_TIMESTAMP(STR_TO_DATE(CONCAT(`Date`, ' ', `Time`),'%d/%m/%Y %H:%i')) as entrata, `Date`, `Time`, `Card`
FROM `controllo`.`Events`
WHERE `Access_point` = 'entrata'
) as t1
INNER JOIN
(
SELECT DISTINCT UNIX_TIMESTAMP(STR_TO_DATE(CONCAT(`Date`, ' ', `Time`), '%d/%m/%Y %H:%i')) as uscita, `Date`, `Time`, `Card`
FROM `controllo`.`Events`
WHERE `Access_point` = 'uscita'
) as t2
ON t1.`Card` = t2.`Card`
)
WHERE (uscita - entrata) > 1800
GROUP BY entrata, t1.`Card`
HAVING MIN(uscita)

vada bene. E' la stessa dell'altra volta, anche se mi sono accorto che mancava una ulteriore conversione in UNIX_TIMESTAMP per poter effettuare correttamente la sottrazione in secondi. Abitualmente non uso il tipo DATETIME e credevo, erroneamente, che nelle operazioni matematiche venisse trattato automaticamente come UNIX_TIMESTAMP, ma così non è!

Prova a fare un test su un set di dati abbastanza corposo e fammi sapere.

 

[dux86]

Carissimo Trogo, lei è un GENIO
mi potrebbe regalare ancora una ciglieggina sulla torta ??!
da come ho capito il "delta" è il tempo calcolato in secondi, mi potrebbe convertirlo in ore/minuti/secondi ??

grazie mille

 

[Trogo]

Basta modificare così:

SELECT *, SEC_TO_TIME(uscita - entrata) as delta FROM
(
(
SELECT DISTINCT UNIX_TIMESTAMP(STR_TO_DATE(CONCAT(`Date`, ' ', `Time`),'%d/%m/%Y %H:%i')) as entrata, `Date`, `Time`, `Card`
FROM `controllo`.`Events`
WHERE `Access_point` = 'entrata'
) as t1
INNER JOIN
(
SELECT DISTINCT UNIX_TIMESTAMP(STR_TO_DATE(CONCAT(`Date`, ' ', `Time`), '%d/%m/%Y %H:%i')) as uscita, `Date`, `Time`, `Card`
FROM `controllo`.`Events`
WHERE `Access_point` = 'uscita'
) as t2
ON t1.`Card` = t2.`Card`
)
WHERE (uscita - entrata) > 1800
GROUP BY entrata, t1.`Card`
HAVING MIN(uscita)

SEC_TO_TIME converte il valore in secondi in un tipo TIME. Per cui funziona benissimo,
ma ha lo stesso range di valori: da '-838:59:59' a '838:59:59'.
Ma credo che siano più che sufficienti!