I have db table called subscription and a column called duration. I want the column duration to be incremented by +1 in every 24 hours until it gets to 30 then stop. I would love the query to start execution if the column duration is = 1.

Edited by David_133: For quick Comprehension

3 Months
Discussion Span
Last Post by prof php

I think you're approaching this from the wrong direction. You should be storing the start date and calculating the increment using datediff in a computed column.

If you need more complicated logic (like zero days being displayed as one day), take care of that in the application and just store the start date in the database.


I'm assuming that >30 means expired so why not just do a case in the query that returns a value for Expired (or unexpired) of True or False. That way if the subscription period changes, all that would need to be modified is a stored query. For that matter, instead of a stored query the OP could just create a view which calculates the True/False value as a field named Expired.

Votes + Comments
Can I have an example as illustration of what you just said ?

Right, you could have your class that is pulled from the table implementing a calculated method based on that date diff (a getter). I use object relational mappers. If you need to query such values you use sql functionality to calculate that date diff.


you can add 2 columns subscription_start_date and subscription_end_date to your table. Then use SELECT CASE WHEN DATEDIFF(subscription_start_date, subscription_end_date) > 0 THEN 0 ELSE 1 END AS expired FROM ... If expired is 0 then you know it's still active, and inactive if expired is 1

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.