Know COUNT of bit column using Group by in T-SQL

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. Read More...

Group By Date Time without milliseconds in T-SQL

There are lot of requirements which needs some complex logic and put maximum effort to implement them. The below scenario is the one I want to explain in writing T-SQL queries.

The requirement is very simple, I want to apply group by on DateTime field in database which does not includes the milliseconds part from the time. So, on the UI I need to group by and show records with seconds and but not with milliseconds.

In my first try, I just wrote the T-SQL query by directly applying the GROUP BY clause on the DateTime field and found that milliseconds are also including. Which is not matching with what exactly I was looking for. So, tested lot of scenarios and nothing works. I am out of ideas on how to trim the milliseconds and apply group by.

After spent some good time on it, got an idea and not sure this is the best and efficient way of implementing.

SELECT CAST(CAST(CONVERT(Date,Datesent) AS Varchar(20)) + ' ' + CAST(CONVERT(TIME(0),Datesent) AS Varchar(20)) AS Datetime) AS DateSent,

Name, Department,
    FROM [EmployeHistory]
    WHERE EmployeID = @EmployeID
    GROUP BY CAST(CAST(CONVERT(Date,Datesent) AS Varchar(20)) + ' ' + CAST(CONVERT(TIME(0),Datesent) AS Varchar(20)) AS Datetime),
Name, Department
ORDER BY DateSent Desc

So, in the above query, if you observe take the GROUP BY clause out and analyze it.

GROUP BY CAST(CAST(CONVERT(Date,Datesent) AS Varchar(20)) + ' ' + CAST(CONVERT(TIME(0),Datesent) AS Varchar(20)) AS Datetime)

I am doing some way to get  the things work out. :) So, don't be surprise. I am doing nothing here, other than taking the Date part from the date time and taking the Time part from the date time without milliseconds and finally casting it back to date by clubbing those two back...

Do you think, this is the best and efficient way? I am sure this will work without any issues. But, the main thing is even though it is looking like simple it took me time to get the solution for it. Hope this helps and please let me know, if you know any better solution than this. Read More...