Click here to Skip to main content
15,885,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a sql procedure to get a Employee Attendance result Like Below, I wan to Show the No of Days Present in another Column
ID | Day1 | Day2 | Day3   
1  |   p  |   p  |   p
2  |   p  |   A  |   p
3  |   p  |   p  |   p
4  |   p  |   p  |   A
5  |   p  |   p  |   p

Want to show the Below Result

ID | Day1 | Day2 | Day3 |  Noof Days Present 
1  |  p   |  p   |  p   |       3
2  |  p   |  A   |  p   |       2     
3  |  p   |  p   |  p   |       3
4  |  p   |  p   |  A   |       2   
5  |  p   |  p   |  p   |       3
Posted
Updated 29-Aug-13 20:49pm
v3
Comments
ArunRajendra 28-Aug-13 2:44am    
Do you have only 3 days columns in table?

Try this...
SQL
Declare @ColumnName Nvarchar(40) , @Sql Nvarchar(1000),@ColumnCount Int, @I Int
Select @Sql='Select Id,Day1,Day2,Day3, ',@I=0,@ColumnCount=Count(COLUMN_NAME) 
From INFORMATION_SCHEMA.COLUMNS 
Where TABLE_NAME='Temp' and COLUMN_NAME Like 'Day%'

While @I<@ColumnCount 
Begin
     Select @ColumnName=COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS 
     Where TABLE_NAME='Temp' 
     Order by COLUMN_NAME
	OFFSET @I Rows -- If Using Sqlserver 2012 use this else
	Fetch Next 1 Rows only; -- use Cursor or while loop with Top(1)
     Set @Sql= @Sql +' ' +'Case When '+ @ColumnName +' =''P'' Then 1 Else 0 End +'
     Set @I=@I+1
End
Set @Sql = Left(@Sql,len(@Sql)-1) +' as NoofDaysPresent From Temp'
Exec(@Sql)

Replace Temp with your TableName
Tested:
SQL
Create Table Temp (Id Int, Day1 varchar(2),Day2 varchar(2),Day3 varchar(2))
Insert into Temp 
Select 1,'P','P','P' Union all
Select 2,'P','A','P' Union all
Select 3,'P','P','P' Union all
Select 4,'P','P','A' Union all
Select 5,'P','P','P' 

Output:
SQL
Id	Day1	Day2	Day3	NoofDaysPresent
----  ------   -----   -----    ---------------
1	P	P	P	3
2	P	A	P	2
3	P	P	P	3
4	P	P	A	2
5	P	P	P	3

If You Want to type every thing then u can use...
SQL
Select Id,ColumnNames,Case When Day1='P' Then 1 Else 0 End+..... as NoofDaysPresent From TableName
 
Share this answer
 
v10
Comments
Raja Sekhar S 30-Aug-13 2:43am    
Did it help..?
Write a function GetPresent(ID) which takes the employee ID sums the number of days the employee is present and use as below:

select *, GetPresent(ID) as Noof Days Present from Attendance
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900