Computed column sql server with if else statement

trying to compute table column 'attendancestatus'

if (actualtime IS NOT NULL & arrivedtime IS NULL) then attendancestatus = 'absense'
if (lateduration IS NOT NULL) then attendancestatus = 'late'
else
attendancestatus = 'ontime'

LVL 1
doramail05Asked:
Who is Participating?
 
kamindaCommented:
You can create the computed column using the below syntax
ALTER TABLE [TableName]
ADD ['attendancestatus']  AS 
(
CASE WHEN 
actualtime IS NOT NULL  AND arrivedtime IS NULL 
THEN 'absense' 
ELSE  
CASE WHEN lateduration IS NOT NULL THEN 'late' END
END)

Open in new window

0
 
Om PrakashCommented:
see if this works

      CASE
            WHEN actualtime IS NOT NULL  
            THEN  WHEN arrivedtime IS NULL
                  THEN 'absense'
                  ELSE 'ontime'
                  END
            ELSE  WHEN lateduration IS NOT NULL
                  THEN 'late'
                  END
       END as 'field'
also check the following thread:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_22709566.html
0
 
Daniel_PLDB Expert/ArchitectCommented:
Try this:
SELECT CASE 
WHEN (actualtime IS NOT NULL AND arrivedtime IS NULL) THEN 'absense'
WHEN (lateduration IS NOT NULL) THEN 'late'
ELSE 'ontime'
END AS attendancestatus FROM <table_name>

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
doramail05Author Commented:
figure out the conditon has bit different in the lateduration
if the lateduration is '1900-01-01 01:00:00.000' then it is late
if the lateduration is '1900-01-01 00:00:00.000' then it is on time
0
 
doramail05Author Commented:
tried to copy and paste in the computed column's formula , but it only copy 'SELECT CASE', is there any where it could write the whole script in the formula?

SELECT CASE
WHEN (actualtime IS NOT NULL AND arrivedtime IS NULL) THEN 'absense'
WHEN (lateduration IS NOT NULL) THEN 'late'
ELSE 'ontime'
END AS attendancestatus FROM <table_name>
0
 
Alpesh PatelAssistant ConsultantCommented:
SELECT CASE
WHEN (actualtime IS NOT NULL AND actualtime <> "1900-01-01 00:00:00.000" AND (arrivedtime IS NULL or arrivedtime = "1900-01-01 00:00:00.000")) THEN 'absense'
WHEN (lateduration IS NOT NULL AND lateduration <> "1900-01-01 00:00:00.000") THEN 'late'
ELSE 'ontime'
END AS attendancestatus FROM <table_name>
0
 
Daniel_PLDB Expert/ArchitectCommented:
I'm sorry, please take a look at the create table code:

create table test (
actualtime datetime,
arrivedtime datetime,
lateduration datetime,
attendancestatus AS
CASE
WHEN (actualtime IS NOT NULL AND arrivedtime IS NULL) THEN 'absense'
WHEN lateduration = '1900-01-01 01:00:00.000' THEN 'late'
WHEN lateduration = '1900-01-01 00:00:00.000' THEN 'ontime'
END
)

Open in new window

0
 
Daniel_PLDB Expert/ArchitectCommented:
I also suggest you to read about persisted computed columns:
Using Computed Columns in SQL Server with Persisted Values
0
 
doramail05Author Commented:
still finding a way to input large amount of code in the computed formula. ~~ o.o
0
 
kamindaCommented:
You TSQL to Alter table and Add the column as I have mentioned if you can do it from SSMS.
0
 
kamindaCommented:
Sorry few typos in previous comment

Use TSQL to Alter table and Add the column as I have shown in my previous post. This will solve your issue if you cant do it from SSMS.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.