Solved

computed column - DataPresent

Posted on 2013-06-13
7
204 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 40
backups - Strategies 1 13
Scheduling Jobs for Execution: 4 15
Pivot not using aggregate yield error 3 18
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now