We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Computed column sql server with if else statement

Medium Priority
2,858 Views
Last Modified: 2012-05-11
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'

Comment
Watch Question

Top Expert 2010

Commented:
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:
https://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_22709566.html
Daniel_PLDB Expert/Architect
Top Expert 2011

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

Author

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

Author

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>
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
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>
Daniel_PLDB Expert/Architect
Top Expert 2011

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

Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
I also suggest you to read about persisted computed columns:
Using Computed Columns in SQL Server with Persisted Values

Author

Commented:
still finding a way to input large amount of code in the computed formula. ~~ o.o

Commented:
You TSQL to Alter table and Add the column as I have mentioned if you can do it from SSMS.

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.