Solved

computed column - DataPresent

Posted on 2013-06-13
7
219 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
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 350 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 150 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 48

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
push and Pull replication 31 47
sql server cross db update 2 20
How to trim a value in SQL 2 26
Can a Trigger trigger a Trigger? 4 18
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

733 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