Friday, June 4, 2010

Here is a scenario most of the times we use and look for. I have a bit column in database table and want to filter the count of true count and false count by applying Group By on it. For example, below is the simple scenario I came up with to better present you.

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.

1 comment: