Date differences by record based on another field

[Jason Beasley]

Evening all.

I am relatively new to using MySQL and I had a question about how to properly write out a statement to essentially correctly calculate date differences based on when product (Invoice #) checks into our warehouse (StatusCD = I) and checks out (StatusCD = O).

An example:

I have a table with three rows and three columns as follows, row one is: Invoice #: ABC123, StatusCD: I, Date: '2021-04-01'. The second row is: Invoice #: ABC123, StatusCD: O, Date '2021-04-10. The third row is: Invoice #: ABC130,StatusCD: O, Date: '2021-04-10'.

I am trying to create a case statement, or something similar which would do the following:

If the Invoice# is the same, and the StatusCD is in both I and O then give me the date difference (in days), basically 2021-04-10 (StatusCD O) - 2021-04-01 (Status I). It will always be StatusCD O date - StatusCD I date. I would expect to see 10 days as the result.

To add to this, I also need to bucket any Invoice # that has a StatusCD = O but NOT equal to I as I do not want to include those occurrences in the age bucket noted above . I was thinking that could be a separate case statement just with a 0/1 flag that I could just filter out the invoice #'s that fall under this situation, I just can't seem to get it working correctly.

Lastly, if an Invoice # has only a StatusCD = I but has no record with an O, I want to show the Date difference from Today's date - the date the record was recorded with a StatusCD of I. Ultimately once the Invoice# gets a new record added with a StatusCD of O, it would shift to just showing the date difference between the two record entries.

I ultimately am trying to track the # of days a product sits in our warehouse by showing the # of days from checking in (I) to checking out (O). Unfortunately we have some records that only have a check out (O) and no check in (I) so I want to ignore those records since I cannot accurately provide a "Time in warehouse." And those records that are still in the warehouse (I) but have not been checked out, I need to give the date difference from Today's date (curdate()) - Date where StatusCD = I.

I realize I have explained the ask twice but I wanted to do so to hopefully not cause confusion.

Currently I have this logic built and the date difference works but it doesn't include the statusCDs as I am a bit lost as to how to approach that piece.

Any help would be fantastic.

This is for work and I am trying to show a proof of concept. It's late and could just be having a brain fart but I figured I would try a forum.

Thank you again.

Jason