1

I have hundreds of records, all with an expiry datetime column. Something like this:

Name         Expires               Status
-----------+---------------------+--------+
Apple1       2017-05-05 10:10:15    Live
Apple2       2017-06-01 18:15:03    Live
Apple3       2017-01-01 23:59:59    Dead
Apple4       2017-01-30 23:00:01    Draft
Apple5       2017-01-30 23:00:01    Queued

I want to change the status of any record that is Live after the expiry datetime has elapsed.

So far I have thought about these possible solutions:

  1. Set up a SQL Server job to run some update script every second
  2. Do it using the application server (ColdFusion 2016) on every page request. So everytime a page is requested it will run this update script in the application's onRequestStart() method.

Both seem a bit "Web 1.0" and possibly very costly. I guess I'm trying to do what Facebook does when it publishes scheduled posts as soon as correct time arrives but can't work out to do it efficently.

Could anyone please suggest what would be a good way to achieve this?

volume one
  • 6,800
  • 13
  • 67
  • 146
  • 6
    Why update it? Just check the datetime compared to sysdatetime (assuming they are sync or based off what you care is the actual dt) and logically make the status instead of constantly updating the table. You are going to have to query the table to get the Expires dt... which at this point you know if it is expired or not, so no need to update a column that you will then read/use for some logic.Just use the dt itself as the logic. – S3S May 02 '17 at 19:17
  • @scsimon Because sometimes people like to expire the records early manually. So there is an option in the application to expire the record. Therefore just doing a date comparison as a way to determine if the record is live or dead won't work. – volume one May 02 '17 at 19:24
  • That's fine, but that's just an IF check. Pseudo: `If(Status = 'Dead' or Expires < SysDateTime()) 'Your record is not live'` – S3S May 02 '17 at 19:25
  • I don't know how this would scale but you could use something like [Quartz.NET](https://www.quartz-scheduler.net/features.html) to schedule a trigger for each of your expiration times. It's action would then update the row in your db. Seems like @scsimon solution is simpler, but this may be an option for you. – Rick S May 02 '17 at 19:40
  • Have you considered a computed column? – Dave C May 02 '17 at 19:42
  • @scsimon okay I probably should have added more examples. Live and Dead are not the only statuses. There are ones which are Draft and Queued as well. So each record moves through 'stages'. Would this affect your suggestion? – volume one May 02 '17 at 20:11
  • @volumeone no it wouldn't since the stage you only care about Dead unless i'm mistaken. Only Dead means that it is expired (or the date time is < current datetime) so the other statuses are irrelevant. However, this is for you to decide, but I can't see it changing the suggestion. You could create race conditions using the method of updating the table. – S3S May 02 '17 at 20:22
  • @scsimon Well each status brings with it different features on the application screen. So if the record is in Live status, then you can't edit certain parameters. If its in queue status then you can. – volume one May 03 '17 at 10:10
  • 1
    Not following how that impacts the suggestion... – Leigh May 03 '17 at 19:26
  • @Leigh lets say that you created a record with yesterdays date as the expiry. however it is saved as a Draft. When you go to the "edit" screen, the page checks what stage the record is in. Just becaues its got yesterdays expiry date in doesn't mean its actually expired, because its in Draft stage. Only a Live record which went past the expiry would be set to Dead. – volume one May 04 '17 at 10:13
  • 2
    Still sounds like it is just a matter of calculating a certain status based on the record's current status and/or expiration date. It might be more than one condition, but not seeing why that couldn't be handled with a CASE as @scsimon and Andrew suggested. For example, the *"only a live records..."* scenario might start with something like this in psuedo-sql: `CASE WHEN Status = 'Live' AND Expires < SysDateTime THEN 'Dead' ELSE Status END AS FinalStatus`. That would only affect "Live" records. Everything else would keep the existing status. – Leigh May 04 '17 at 15:06

1 Answers1

4

I would either use a view along the lines of

SELECT
    [Name]
    ,[Expires]
    ,CASE
        WHEN GETUTCDATE() > [Expires] THEN 'Dead'
        ELSE [Status]
        END AS [view_Status]
FROM Table

or use a computed column for the status column that does the same thing. Either way, if it has expired it will list as 'Dead', and if it has not then it will grab the status column.

Andrew O'Brien
  • 1,793
  • 1
  • 12
  • 24