mercredi 30 décembre 2015

SQLite:Query for a specific time tracking scenario

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

  1. Anytime an inventory is placed into a Freezer, I insert a new record with an in_time
  2. 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