SQL Select Query For All Data In Single Column where date is getting increased by one by one month

Question

I am trying to fetch data from table where DateOfJoining is less than 2021-01-01 and i get the value.

Then I write another query to fetch the data from same table and now DateOfJoininng is less than 2021-02-01

I get the data but for 1 year I have to write it again and again but I want all data in single query in single output table where date of joining in that month is getting change till the date I specify assume till 5 month so how will query look like please help!

Here is the query that i write for five months

select count() 
from STAREdee221f287.EmployeeDetail 
where EmpStatus = 1 and CompanyId =1 and DateOfJoining <'2021-01-01' 

select count() 
from STAREdee221f287.EmployeeDetail 
where EmpStatus = 1 and CompanyId = 1 and DateOfJoining <'2021-02-01' 

select count() 
from STAREdee221f287.EmployeeDetail 
where EmpStatus = 1 and CompanyId = 1 and DateOfJoining <'2021-03-01' 

select count() 
from STAREdee221f287.EmployeeDetail 
where EmpStatus = 1 and CompanyId = 1 and DateOfJoining <'2021-04-01' 

select count(*) 
from STAREdee221f287.EmployeeDetail 
where EmpStatus = 1 and CompanyId = 1 and DateOfJoining <'2021-05-01'

and here is the result I get after executing above query result of query

Answer 1

You can use a calendar table. For the short list of dates you can use just VALUES to enumerate dates of interest. Alternatively it could be generated on the fly

with t0(n) as (
 select n 
 from (
    values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    ) t(n)
),ns as(
   select row_number() over(order by t1.n) - 1 n
   from t0 t1, t0 t2, t0 t3
),calendar as (
   -- replace 5 and '2021-01-01' with nmbr of months and starting date as needed
   select top(5) DATEADD(month, n, '2021-01-01' ) dt
   from ns
   order by n
)
select 
    (SELECT COUNT(*)
     FROM EmployeeDetail 
     WHERE EmpStatus = 1 and CompanyId = 1 and DateOfJoining < calendar.dt) AS CNT,
    'Before '+ cast(dt as varchar(10)) AS BeforeDateOfJoining
from calendar
order by dt