Value if condition is met in SQL

Question

In SQL server, how can I affect a value to an attribute given a condition, and check the condition everyday?

Let's say an order was recorded on a certain date. According to my business rule the delivery should be made no later than 10 days after the order date.

In my Delivery table, I have a status attribute.

In my Order table, I have an orderDate attribute.

I would like the status value to be set based on the following logic:

if deliveryDate is null:
     if today's date < orderDate + 10:
          'in progress'
     else : 
          'late'
else :
     if deliveryDate > orderDate + 10:
          'late'
     else:
          'on time'

Is there a way I can do that, and update the status value everyday to check again if the order is late?

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Oct 08 '21 at 20:21
The passage of time does not change the contents of a column. You could use a view or a computed column for this purpose if it is purely visual. A **periodic job** (hint) to change the value is another possibility. But I suggest you consider a much more precise business process that will "alert" in some fashion those deliveries that are "late" — SMor, Oct 08 '21 at 20:25

score 0 · Answer 1 · answered Oct 08 '21 at 20:52

Use the case/when statement.


-- if deliveryDate is null: 
--   if today's date < orderDate + 10: 
--     'in progress' 
--   else : 
--     'late' 
-- else : 
--   if deliveryDate > orderDate + 10: 
--     'late' else: 'on time'
    select
    case
    when tableName.deliveryDate is null 
        case 
        when datetimeutc() <= dateadd(tableName.orderDate, 10, 'dd')
            then 'in progress'
        else 'late'
        end
     else 
         case 
         when tableName.deliveryDate > dateadd(tableName.orderDate, 10, 'dd')
             then 'late'
         else 'on time'
         end
      end

     as "Status"
     from -- ...

The T-SQL case/when is a direct translation of if .. then return ... elif ... then return ... else return ... endif

Value if condition is met in SQL

1 Answers1