I have a specific scenario to track the in and out times for an inventory item.
I have a Inventory_track table of the following format
Inv_id in_time out_time
1) I_1111 2015-11-29 21:05:00. 2015-12-01 03:00:00
2) I_1111 2015-12-01 05:00:00. 2015-12-01 09:00:00
3) I_1111 2015-12-01 15:00:00. 2015-12-01 17:00:00
4) I_1111 2015-12-01 21:00:00. NULL
- Anytime an inventory is placed into a Freezer, I insert a new record with an in_time
- Anytime an inventory is taken out, I update the out_time ( not an insert, only an update)
At any given point, I want to find the time that the inventory spent outside the freeze location. Basically what I want to do is :
(record_2's in_time- record_1's out_time) + ( record_3's in_time- record_2's out_time)
and so on for a given inventory. Can someone help me with a query in sqlite for this. How do I go about using self joins for this scenario described above.
Aucun commentaire:
Enregistrer un commentaire