We are storing employee details in database and taking attendance once in every day. So, in reporting, I want to see month wise report on how many are total, absent and present. Now, the challenge is need to find out how many are total employee and among them how many are attended and how many are absent in the given month and year.
OK, let's go to implementation and find the solution.
SELECT [Month],[Year],Count(IsPresent) as [Total], Count(Case When IsPresent='True' Then IsPresent End) as Attended, Count(Case When IsPresent='False' Then IsPresent End) as Absent From [EmployeeAttendance] WHERE [Month] = @Month AND [Year] = @Year GROUP BY [Month],[Year]
This is it!!! Very simple. Hope you understand it on how to apply grouping on bit field to find out the counts.