I have a table that looks like the following and from it I want to get days remaining of total doses:
USER|PURCHASE_DATE|DOSES 1111|2017-07-27|15 2222|2020-07-17|3 3333|2021-02-01|5
If the doses do not have an expiration and each can be used for 90 days then the SQL I use is:
USER|DAYS_REMAINING 1111|0 2222|6 3333|385
But what if I want to impose an expiration of each dose at a year? What can I do to modify my SQL to get the following desired answer:
USER|DAYS_REMAINING 1111|-985 2222|6 3333|300
It probably involves taking the MIN between when doses expire and how long they would last but I don’t know how to aggregate in the expiry logic.