How to change the database column values with time?

Question

I'm doing a House keeping for hotel management system. I have to implement a system, The house keeper have to clean the room twice everyday (Example: every 12 hours). In my database, I have a column called Room status. I'm using a comboBox in my form for this data.

The comboBox contain 2 type of date (Cleaned, Have to clean). After cleaning the room house keeper update the Room status as Cleaned. But, the Room status automatically have to change as Have to clean twice a day. for example (8.00am and 8.00pm) the status have to change as Have to clean.

Can you please give a solution for this problem? Code based explanation will be more helpful.

Answer 1

In my opinion, instead of Room Status, you should have the column Last Cleaned At. And then whenever you get a room cleaned, you update the time of this column. The logic that a room has to be cleaned can be put in your C# application code which is reading from this database.

Now, if you later on decide that you have to clean the rooms every 8 hours instead of 12, you can change that in your application code and then when displaying the data on screen, if the room isn't cleaned in last 8 hours, you will see Have to clean.

Further, if you just have to display the data on terminal, you can write the queries by means of CASE keyword.

Answer 2

Creating a job in your database should do the trick. See more here