Calculating Days With Equivalent String Value Using Datediff Computed Column Specification Formula

Question

I'm using SQL Server 2008 and I made a computed column on my table which is ResidencyStatus. Using datediff in ResidencyStatus I want to know the difference between the current date (now date) and my other date column ResidencyDate.

• Now, if the result is less than or equal to 183 days(less than or equal to 6 months) then I want to put the string 'Transient' in ResidencyStatus.

• If the result is greater than or equal to 184 days(greater than or equal to 6 months and 1 day) AND less than or equal to 1825 days(less than or equal to 4 years and 364 days) then the value will be 'Migrant'.

• If the result is greater than or equal to 1826 days(greater than or equal to 5 years) then the value will be 'Resident'.

I tried using this formula in the computed column:

(case when (datediff(day,[ResidencyDate],getdate()))<=183 then 'Transient' elseif (datediff(day,[ResidencyDate],getdate())>=184 and <=1825 then 'Migrant' elseif (datediff(day,[ResidencyDate],getdate())>=1826 then 'Resident' end

...but this error occurs "Error validating the formula for column ResidencyStatus." Can you give me an idea on how to make this formula work or maybe an alternative solution?

Answer 1

There is no such thing as elseif in a case statement. The case statement takes a series of when clauses, and these are evaluated in order. So, you can express what you want as:

(case when datediff(day, [ResidencyDate], getdate()) <= 183 then 'Transient'
      when datediff(day, [ResidencyDate], getdate()) <= 1825 then 'Migrant' 
      else 'Resident'
 end)

Okay, this does have a slight difference from your logic. The else will include NULL values. This is easy enough to fix:

(case when datediff(day, [ResidencyDate], getdate()) <= 183 then 'Transient'
      when datediff(day, [ResidencyDate], getdate()) <= 1825 then 'Migrant' 
      when datediff(day, [ResidencyDate], getdate()) > 1825 then 'Resident' 
 end)

This will return NULL in those cases.

Answer 2

Try this.

  select 
  ....
  CASE
     WHEN ( Datediff(day, [ResidencyDate], Getdate()) ) <= 183 THEN 'Transient'
     WHEN Datediff(day, [ResidencyDate], Getdate()) >= 184
          AND Datediff(day, [ResidencyDate], Getdate()) <= 1825 THEN 'Migrant'
     ELSE 'Resident'
   END
  .....
  .....

OR use Between

  select 
  ....
  CASE
     WHEN ( Datediff(day, [ResidencyDate], Getdate()) ) <= 183 THEN 'Transient'
     WHEN Datediff(day, [ResidencyDate], Getdate()) Between 184 and 1825 THEN 'Migrant'
     ELSE 'Resident'
   END
  .....
  .....