samedi 7 mars 2015

SQLite query select between 2 records on same condition

i am trying to count (in minutes) between two records on 1 table and WHERE clause is the same condition.



_id | venue_id | act_time | status |
1 | 1 | 13:30 | 0 |
2 | 1 | 15:40 | 1 |
3 | 2 | 13:03 | 0 |
4 | 2 | 16:06 | 1 |


when i exec query like this :



SELECT _id, venue_id, status, (julianday(act_time IN (SELECT act_time FROM reports WHERE venue_id='1' AND status='1')) - julianday(act_time))*1440 AS duration FROM reports WHERE venue_id='1' AND status='0'


but, the result show the wrong calculation


Please help me what is the correct query for this problem?


so, if i count the duration between 15:40 - 13:30 (at venue_id='1') = 130 minutes.


thank you.


Aucun commentaire:

Enregistrer un commentaire