question

Parthasarathy avatar image
Parthasarathy asked

Sql Server

1 down vote favorite in Sql server ------------------------- name | [1 AUG]| [2AUG]| [3AUG] __________________________ prad | Present| Present| Lop vas | Present| Present| Lop I want to count the number of 'Present' each person's Present Dates in a Month as I mention Above .please help How can I count row wise?
sql
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
(Off the top of my head...) You could do something like: SELECT CASE WHEN [1 aug] = 'Present' THEN 1 ELSE 0 END + CASE WHEN [2 aug] = 'Present' THEN 1 ELSE 0 END + ... CASE WHEN [31 aug] = 'Present' THEN 1 ELSE 0 END AS DaysPresentInAugust An alternative would be to use the [UNPIVOT][1] statement SELECT Name, DayOfAugust, PresentOrNot FROM (SELECT Name, [1 Aug], [2 Aug], ..., [31 Aug] FROM ) p UNPIVOT (PresentOrNot FOR DayOfAugust IN ([1 Aug], [2 Aug], ..., [31 Aug])) AS unp Something like that. Note: all code untested. Suggestions only. I'm not on a machine with SQL Server to test this stuff... [1]: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

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.