Total days present of an employee in SQL Server

Hi ravi.icsil,

Please refer below query

DECLARE @Table AS TABLE(Staff_id INT,Name VARCHAR(30),Dept VARCHAR(20),Design VARCHAR(30),C_date DATETIME,Attendance_status VARCHAR(30))

INSERT INTO @Table VALUES(1,'ABC','Ambedkar University','DEO','09-19-2016','Present')
INSERT INTO @Table VALUES(2,'DEF','Ambedkar University','DEO','09-19-2016','Half Day Present')
INSERT INTO @Table VALUES(1,'ABC','Ambedkar University','DEO','09-20-2016','Half Day Present')
INSERT INTO @Table VALUES(2,'DEF','Ambedkar University','DEO','09-20-2016','Present')
INSERT INTO @Table VALUES(1,'ABC','Ambedkar University','DEO','09-21-2016','Half Day Present')
INSERT INTO @Table VALUES(2,'DEF','Ambedkar University','DEO','09-21-2016','Half Day Present')
INSERT INTO @Table VALUES(1,'ABC','Ambedkar University','DEO','09-22-2016','Absent')
INSERT INTO @Table VALUES(2,'DEF','Ambedkar University','DEO','09-22-2016','Absent')


SELECT Staff_id,Name,Dept,Design
	  ,SUM(CASE WHEN Attendance_status = 'Present' THEN 1 
			    WHEN Attendance_status = 'Half Day Present' THEN 0.5
			    WHEN Attendance_status = 'Absent' THEN 0
	       END) as [Total Days Present] 	  
FROM @Table
GROUP BY Staff_id,Name,Dept,Design

Output

Hope this works for you