Conditional Calculated Field (2 Viewers)

Lightwave

Ad astra
Local time
Today, 09:38
Joined
Sep 27, 2004
Messages
1,521
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 ?
 

SQL_Hell

SQL Server DBA
Local time
Today, 09:38
Joined
Dec 4, 2003
Messages
1,360
Hi,

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

Lightwave

Ad astra
Local time
Today, 09:38
Joined
Sep 27, 2004
Messages
1,521
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

MVP / VIP
Local time
Today, 09:38
Joined
Jan 14, 2017
Messages
18,186
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

SQL Server DBA
Local time
Today, 09:38
Joined
Dec 4, 2003
Messages
1,360
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

You've got your good things, and you've got mine.
Local time
Today, 05:38
Joined
May 21, 2018
Messages
8,463
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.

Code:
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
Code:
Select FP([SomeDateField]) as FuturePast, otherFields,.. from someTable...

In a calculated controls
Code:
  =FP([SomeDateField])
 
Last edited:

Lightwave

Ad astra
Local time
Today, 09:38
Joined
Sep 27, 2004
Messages
1,521
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

AWF VIP
Local time
Today, 09:38
Joined
Jul 26, 2013
Messages
10,353
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

Super Moderator
Staff member
Local time
Today, 20:38
Joined
Jan 20, 2009
Messages
12,849
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.

Code:
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

Ad astra
Local time
Today, 09:38
Joined
Sep 27, 2004
Messages
1,521
I'm probably not going to implement this computed field but I managed to work out the syntax so for reference. TSQL - SQL Azure

Code:
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
 

Users who are viewing this thread

Top Bottom