Solved

computed column - DataPresent

Posted on 2013-06-13
7
214 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

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 learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

860 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