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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

computed column - DataPresent

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
al4629740
Asked:
al4629740
2 Solutions
 
BAKADYCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
al4629740Author Commented:
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
BAKADYCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
PortletPaulCommented:
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now