Categories
Mastering Development

How to build in product expiration in SQL?

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:

SUM(DOSES)*90-DATEDIFF(DAY,MIN(DATE),GETDATE())
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.

Leave a Reply

Your email address will not be published. Required fields are marked *