question

rsj74 avatar image
rsj74 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
calculations
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
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... ;-))
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.