[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Computed column sql server with if else statement

Posted on 2011-04-21
11
Medium Priority
?
815 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'

0
Comment
Question by:doramail05
  • 3
  • 3
  • 3
  • +2
11 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 35446103
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35446112
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
 
LVL 1

Author Comment

by:doramail05
ID: 35446160
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 1

Author Comment

by:doramail05
ID: 35446172
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
 
LVL 9

Accepted Solution

by:
kaminda earned 2000 total points
ID: 35446173
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35446178
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35446236
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35446239
I also suggest you to read about persisted computed columns:
Using Computed Columns in SQL Server with Persisted Values
0
 
LVL 1

Author Comment

by:doramail05
ID: 35446631
still finding a way to input large amount of code in the computed formula. ~~ o.o
0
 
LVL 9

Expert Comment

by:kaminda
ID: 35446748
You TSQL to Alter table and Add the column as I have mentioned if you can do it from SSMS.
0
 
LVL 9

Expert Comment

by:kaminda
ID: 35446854
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question