Aiuto Query difficile

dux86

Utente Attivo
20 Lug 2007
97
0
0
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

Utente Attivo
11 Gen 2008
370
0
0
40
Sanremo
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

Utente Attivo
20 Lug 2007
97
0
0
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

Utente Attivo
11 Gen 2008
370
0
0
40
Sanremo
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!
 
Discussioni simili
Autore Titolo Forum Risposte Data
R Aiuto con le query MS Access 2
I Aiuto query MySQL 8
D aiuto con query PHP 1
A [risolto] aiuto integrazione query per db calcistico MySQL 2
S aiuto con le query PHP 0
S aiuto con query MySQL 10
P Aiuto con query "SELECT" PHP 6
J Aiuto per una query di eliminazione PHP 5
A Aiuto con Funzione/Query PHP 3
B Aiuto query mysql in php PHP 1
F aiuto per query UNIONE mysql MySQL 2
I Aiuto su query PHP 1
D aiuto query mysql MySQL 2
C AIUTO Query Database MySql PHP 5
I Aiuto query PHP 2
R Aiuto query mysql PHP 14
E Aiuto per query PHP 8
X eseguire due query aiuto PHP 1
D Aiuto Query PHP 5
T Aiuto sub query Database 0
S aiuto x query... PHP 0
A Aiuto, query per Access 2003 MS Access 5
S aiuto in query di aggiornamento Database 0
R Aiuto. come modificare il contenuto di una query a campi incrociati Database 0
M Un aiuto da chi ha apple Mac e Software 0
P Richiesta di aiuto Presentati al Forum 1
A Aiuto per pagina php PHP 0
M Questa pagina non carica correttamente Google Maps: aiuto!! HTML e CSS 1
I Aiuto php Dependent Lookup PHP 0
M AIUTO ESERCIZIO JAVA Javascript 1
G Aiuto con htaccess e rewriterule PHP 0
T cercasi aiuto per file d1 (open-edge db) Database 0
M Aiuto con inserimento immagini WordPress 6
D aiuto schermata photoshop Photoshop 0
L Aiuto per programma web php/mySQL PHP 2
A Aiuto php colore diverso PHP 10
L Aiuto creazione menu mancante WordPress 0
C Aiuto compiuto scuola PHP/MySQL PHP 2
G Insert into select - Aiuto MySQL 0
I Aiuto bash linux Programmazione 1
F Aiuto java script Javascript 2
R Cerco aiuto Offerte e Richieste di Lavoro e/o Collaborazione 7
G Aiuto HTML, collegamento con un bottone a una <div> di un' altra pagna HTML e CSS 5
R Aiuto sito html Offerte e Richieste di Lavoro e/o Collaborazione 3
claudio_lorenzo [Javascript] aiuto su jquery per calcolo altezze dom Javascript 1
G Mi dite come faccio? Aiuto photoshop ? Photoshop 1
C prestashop paragamento contrassegno aiuto modulo E-Commerce 0
Michelebozzo [PHP] Rimuovere pubblicazione nome pagina ... aiuto! PHP 7
Caldus richiesta aiuto Mac e Software 0
R [Javascript] Aiuto su questo script Javascript 2

Discussioni simili