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

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

CodePudding user response：

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

CodePudding user response：

You could use the count function as the following:

select count(case when DateOfJoining <'2021-01-01' then 1 end) m1,
       count(case when DateOfJoining <'2021-02-01' then 1 end) m2,
       count(case when DateOfJoining <'2021-03-01' then 1 end) m3,
       count(case when DateOfJoining <'2021-04-01' then 1 end) m4,
       count(case when DateOfJoining <'2021-05-01' then 1 end) m5
from EmployeeDetail 
where EmpStatus = 1 and CompanyId =1

CodePudding user response：

If you want to be able to customise your first month and last month (e.g., this query is from January to May 2021, the next one is from March to November 2021) then I think the simplest method is

Create a table holding the relevant 'cutoff dates' (e.g., the 1st of every month from the first month to the last)
Join that table to your EmployeeDetail table, but using a 'less than' in the join rather than an equals.

For example, say the table we want is called 'CutoffDates' and has one column

CREATE TABLE #CutoffDates (CutoffDate date PRIMARY KEY);

We could insert the dates 1 Jan 2021, 1 Feb 2021 ... 1 May 2021 into this table.

Then you could do your query as

select #CutoffDates.CutoffDate, COUNT(*) AS Num_Employees
from STAREdee221f287.EmployeeDetail AS ED
    INNER JOIN #CutoffDates ON ED.DateOfJoining < #CutoffDates.CutoffDate
where ED.EmpStatus = 1 and ED.CompanyId = 1
GROUP BY #CutoffDates.CutoffDate;

I believe (I cannot test it because I don't have your data) that the results would look like this

CutoffDate   Num_Employees
--------------------------
2021-01-01   427
2021-02-01   431
2021-03-01   438
2021-04-01   444
2021-05-01   451

To populate the #CutoffDates table, you can use multiple methods. The method below uses a 'numbers table' to aid in using DATEADD(month...) to get the 1st of every relevant month.

The method is also shown in this db<>fiddle. Note that there are two variables used - a date in the first month you want to start with, and a date in the last month (e.g., so for the January to May example above, the DateInFirstMonth should be any date in Jan 2021; the DateInLastMonth should be any date in May 2021).

-- Create a numbers table (0 to as many as you could possible need)
CREATE TABLE #Numbers (n int PRIMARY KEY);
INSERT INTO #Numbers (n) VALUES 
  (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
  (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
  (20),(21),(22),(23),(24);

-- Create a table used for the dates (e.g., 1 Jan 2021, 1 Feb 2021, etc to 1 May 2021)
CREATE TABLE #CutoffDates (CutoffDate date PRIMARY KEY);

-- Populate the table with dates based on 'date in first month' and 'date in last month'
DECLARE @DateInFirstMonth date = '20210112';
DECLARE @DateInLastMonth date = '20210523';

-- Convert 'date in first month' to 1st of the month
SET @DateInFirstMonth = DATEFROMPARTS(YEAR(@DateInFirstMonth), MONTH(@DateInFirstMonth), 1);

-- Use these to create dates in #CutOffDates
INSERT INTO #CutoffDates (CutoffDate)
  SELECT DATEADD(month, #Numbers.n, @DateInFirstMonth)
  FROM  #Numbers
  WHERE #Numbers.n <= DATEDIFF(month, @DateInFirstMonth, @DateInLastMonth);

Note - this is not the most efficient method (as, say, the initial employees are counted many times - once for every row). There are also some tweaks that can be done to improve it (e.g., the numbers table I created as simply as possible to make it easy to understand - @Serg's answer has a technically better way to make a numbers table).