Conditional Calculated Field

[Lightwave]

Hi all

I am trying to create a computed column as per

Computed columns

But I would like to use a conditional statement to create the value

IF datefieldvalue is null or >= Today
computedfieldvalue = Future
Else
computedfieldvalue = Past
END IF

Has anyone any pointers ?

 

[Lightwave]

Postscript

I guess I can use something like the case statement put it in a function and then reference the field to the function?

https://www.w3schools.com/sql/sql_case.asp

 

[Lightwave]

Just solved my own problem. Its great that articulating an issue helps to more clearly define the problem leading to a better investigation towards a solution. One of the reasons why forums are soooo good.

https://blog.sqlauthority.com/2016/04/27/sql-server-computed-column-conditions-case-statement/

 

[SQL_Hell]

Hi,

Why not put your case statement in a query? Why does it need to be computed column?

 

[Lightwave]

In this particular case I will be referring to it in multiple views and forms so if I design it once I can reduce the complexity of a number of queries.

You wouldn't happen to know?

If you persist a computed column will it re-calculate when the record is updated?

 

[isladogs]

It will only update when you move away from that record.
I'd also advise against using calculated fields unless the calculation is very complex and therefore likely to take some time to perform

 

[SQL_Hell]

In this particular case I will be referring to it in multiple views and forms so if I design it once I can reduce the complexity of a number of queries.

You wouldn't happen to know?

If you persist a computed column will it re-calculate when the record is updated?

Ok, seems like a sensible reason. I am not massively against computed columns but I normally find some other way of doing these type of things.

A persisted computed column will update when you update the data.

I found a good article on persisted computed columns and some of their caveats, here:

https://sqlperformance.com/2017/05/sql-plan/properly-persisted-computed-columns

 

[MajP]

If you wrap it in a UDF that will make it extremely simple to reuse in queries or calculated controls. Most people will recommend to avoid calculated fields.

Public Function FP(TheDate as variant) as string
  if not isdate(TheDate) or TheDate >= Date()then
    FP = "Future"
  elseif isDate(theDate) AND TheDate < Date then
    FP = "Past"
  end if
End Function

Then in a query

Select FP([SomeDateField]) as FuturePast, otherFields,.. from someTable...

In a calculated controls

  =FP([SomeDateField])

 

[Lightwave]

Thanks Maj - didn't really make my question clear. Its an SQL Azure database linked to an ASP.NET application. The amount of data is insignificant - we are talking about 5000 records growing at about 100 records a month.

I am just getting to grips with functions in SQL and find computed columns easier at the moment. Tight deadline so probably will do something half way between full function and computed column namely implement an embedded case statement within an SQL query and in my ASP.NET I will be able to reference the alias of the resulting field name in the ASP.NET front end.

For the number of records the compute cost really shouldn't be an issue.

 

[Minty]

I've found computed columns are pretty handy in SQL Server if applied properly.
As you identified it's normally about the scale of use and persistence.

Indexing can be an issue - I'm not sure if it's been addressed in later versions, but you used to have to jiggle around with indexing a view based on the column to make it handy in large datasets.

 

[Galaxiom]

If you wrap it in a UDF that will make it extremely simple to reuse in queries or calculated controls. Most people will recommend to avoid calculated fields.

Public Function FP(TheDate as variant) as string
  if not isdate(TheDate) or TheDate >= Date()then
    FP = "Future"
  elseif isDate(theDate) AND TheDate < Date then
    FP = "Past"
  end if
End Function

The context is SQL Server where User defined functions are generally written in SQL.

For best efficiency construct them as inline functions rather than multi statement functions if possible.

VB code can be used in a CLR function but it needs to be compiled and implemented through an Assembly. Being truly compiled, this construct can be vastly faster than SQL unless the functionality was actually more suited to processing by the SQL engine.

 

[Lightwave]

I'm probably not going to implement this computed field but I managed to work out the syntax so for reference. TSQL - SQL Azure

ALTER TABLE ProjectManagement
ADD FutureorPast AS CAST
(
CASE
WHEN TargetDate > GetDate() or TargetDate is Null THEN 'FUTURE' WHEN TargetDate <= GetDate() THEN 'PAST'
ELSE ''
END as nvarchar(6)
)
GO