asked
how to calculate data in sql server row
hi im new in sql server or other database tool..before this, i only using excel, i've create table using sql server 2008 (in VB WD
asp.net) table lay out is like this, Id Name Mon Tue Wed Thu Fri TX Id is the students Id, Name is students name, Mon - Fri are days they're attend to school and the data inside this column are X and Y, X means absent and Y means Attend, TX is the total of X every week, what i need to do is, how do i calculate each row of the student that will calculate X every week and put the data inside column TX..is there any formula to do this? thanks..and sorry for my english..im from malaysia
Hi What you might want to look at is setting the TX column to be a COMPUTED COLUMN - see
http://msdn.microsoft.com/en-us/library/ms191250(v=sql.105).aspx for more information; also see the CREATE TABLE documentation at
http://msdn.microsoft.com/en-us/library/ms174979(v=sql.105).aspx. In your case, I would consider something like: CREATE TABLE MyTableName ( ID int, Name varchar(255), Mon char(1), Tue char(1), Wed char(1), Thu char(1), Fri char(1), TX AS CASE Mon When 'X' THEN 1 ELSE 0 END + CASE Tue WHEN 'X' THEN 1 ELSE 0 END + CASE Wed WHEN 'X' THEN 1 ELSE 0 END + CASE Thu WHEN 'X' THEN 1 ELSE 0 END + CASE Fri WHEN 'X' THEN 1 ELSE 0 END ); (NB: Not tested... ;-))
Thank you sir..i tried and it work.and the data in TX display as read only..may i ask another question? if i modify and insert more columns into this table, let say 26 more columns. what i want to do is to collect the students attendent for 1 month (January=31 days@ 31 columns)..how do i modify this TX colums formula so that it will calculate data for 31 columns? thanks in advance
13 People are following this question.
