?
Solved

computed column - DataPresent

Posted on 2013-06-13
7
Medium Priority
?
224 Views
Last Modified: 2013-06-17
I would like to put a "1" in the DataPresent column if the Total column is greater than 0

SELECT Month, Total, CASE WHEN TOTAL > 0 THEN 1 END AS DataPresent FROM AttendanceGrid

How can I make a computed column and have it automatically do that when a record changes in SQL?
0
Comment
Question by:al4629740
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 4

Expert Comment

by:BAKADY
ID: 39245061
what do you mean with :  ... ?
How can I make a computed column and have it automatically do that when a record changes in SQL?
do you mean at vb6 at program level???
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39245068
that seems to be almost perfect except the missng else part

SELECT Month, Total, CASE WHEN TOTAL > 0 THEN 1 ELSE 0 END AS DataPresent FROM AttendanceGrid
0
 

Author Comment

by:al4629740
ID: 39245084
I mean have the record produce a "1" in the DataPresent field anytime the Total > 0 for that record.

This would happen in the SQL table.  I would like to enter the SQL statement as a computed column so that it automatically occurs

So if Total > 0 Then Datapresent = 1
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1400 total points
ID: 39245119
ALter table AttendanceGrid  add DataPresent  as CASE WHEN TOTAL > 0 THEN 1 ELSE 0 END
that will create the computed column, but i prefer a persisted computed column

refer this link

http://www.mssqltips.com/sqlservertip/1682/using-computed-columns-in-sql-server-with-persisted-values/
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39245140
aneeshattingal was faster and he is right

ID: 39245119

after that you will be able to call your data with this

SELECT Month, Total, DataPresent FROM AttendanceGrid

Open in new window

0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 600 total points
ID: 39245258
Since the value can be only 1 or 0, I'd explicitly make the data type a bit:


ALTER TABLE dbo.AttendanceGrid
    ADD DataPresent AS CAST(CASE WHEN total > 0 THEN 1 ELSE 0 END AS bit)
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39246451
This computed column relates also to question: Find Starting Month of Activity

@al4629740
please note, this computed column does not assist in the determination of "first month"
all it does is indicate "TOTAL > 0" (or not)

to be honest I think this computed column may be a redundancy - but you may have other purposes for it that I'm unaware of.
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

762 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