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.

Recommended Answers

All 5 Replies

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 think pty has it right. Some formula using datediff and then some if > 30 then 30.

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.

commented: Can I have an example as illustration of what you just said ? +0

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.