There are a few things wrong / could be better with your solution.
1. Do not use convert! Your columns should be of type
date
or
datetime
and all operations on them should use dates not strings.
If you are concerned about the impact of time on these calculations
either ensure that the time is stored as 00:00:00.0000 when entering data into the database
or cater for the time in your query - i.e. use "start of day" or "end of day" as appropriate - see Lynn Pettis'
examples[
^]. Upgrading to SQL 2016 or later would be even better as the
date
column type was introduced
2. An alternative to using and/or with brackets (which often causes errors when the brackets get mismatched) you can using the
ISNULL function [
^] E.g.
select * from [dbo].[tblEmployeeMaster]
where doj <= '2020-04-01' and ISNULL(dol, '2020-04-01') >= '2020-04-01')
3. The logic for employees at the start of quarter shouldn't really include the first day of the quarter (it's that whole start of day / end of day thing again), so shouldn't that be
select * from [dbo].[tblEmployeeMaster]
where doj < '2020-04-01' and ISNULL(dol, '2020-04-01') >= '2020-04-01')
4. Instead of using hard-coded dates you probably want to introduce some parameters so you can vary the year or quarter (or any dates to define the period). For example
declare @startOfYear datetime = '2020-01-01'
declare @QuarterRequired int = 1
declare @startOfPeriod datetime = dateadd(qq, @QuarterRequired - 1, @startofYear)
declare @endOfPeriod datetime = dateadd(d,-1,dateadd(q, 1, @startOfPeriod))
4. Finally, the original requirement was to have the counts of employees (although listing them as you have in your solution is a good way of checking the results before getting to the final query). You probably also want those figures returned in a single query so you are going to want to understand PIVOT - see this CP article
Simple Way To Use Pivot In SQL Query[
^]. Or for example
select * from
(
select 'Beginning' as [when], count(*) as empcount from dbo.[tblEmployeeMaster]
where doj < @startOfPeriod and (dol is null or dol > @startofPeriod)
union
select 'End', count(*) from dbo.[tblEmployeeMaster]
where doj <= @endOfPeriod and isnull(dol, dateadd(d,1,@endOfPeriod)) > @endOfPeriod
) anchor
PIVOT
(
max(empcount) for [when] in ([Beginning], [End])
) PVT
which (for my test data) gave
Beginning End
3 4