So I'm trying to work out logic for a "count" column that
- Starts with an initial value if the date is today
- That has every row's value less than the previous one by a rate, which is constant for each id
- The date in column is 365 days including today for each id.
I have been able to capture the logic for each of these 3 bullet points, via a table with the Ids and 365 days for each Id.
The interesting part is that when the count column gets to negative, we want to show it as zero. Also, when the "Date to Arrive" Column is not null a fixed quantity (q1) is added to the count. So, when a count is negative and a q1 of 57 is added, it should show 57 and start decreasing by the rate, until it gets to negative, then it shows zero. If a q1 is added before it goes negative (or zero), it should be the current count + q1. So current count of 10 and q1 of 57 should be 67.
The logic I had worked until the negative (zero) counts get q1 added. Any help/ideas on how to go about this problem? Thanks