x

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?

more ▼

asked Sep 01, 2012 at 01:33 PM in Default

avatar image

Parthasarathy
60 5 7 9

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

(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 statement

 SELECT Name, DayOfAugust, PresentOrNot
 FROM (SELECT Name, [1 Aug], [2 Aug], ..., [31 Aug] FROM <your table>) 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...

more ▼

answered Sep 01, 2012 at 02:16 PM

avatar image

ThomasRushton ♦♦
40.3k 20 49 53

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1014

asked: Sep 01, 2012 at 01:33 PM

Seen: 481 times

Last Updated: Sep 01, 2012 at 02:17 PM

Copyright 2016 Redgate Software. Privacy Policy