# How to Get Employees Period as per Employee Joining Date

--How to Get Employees Period as per Employee Joining Date
Example, I have a field

The Period Start and Period End will be the o/p

Then what will be the query

Thanks
Basit

``````SELECT
Emp_Name,
[Period Start],
[Period End]
FROM
YourTable
WHERE
JoiningDate = '20130714'``````

I assume that you're trying to generate Period Start and Period End for the current year based on the month and day of the JoiningDate... is that correct?

If so, what do you want to do about a JoiningDate like 29-02-2016 (leap year day)

You can generate the period start/end dates use DATEFROMPARTS - but you have to account for the leap day. Here is one option:

`````` Select *
, datefromparts(year(getdate()), month(x.JoiningDate), iif(month(x.JoiningDate) = 2 And day(x.JoiningDate) = 29, 28, day(x.JoiningDate))) As PeriodStart
, datefromparts(year(getdate()) + 1, month(x.JoiningDate), iif(month(x.JoiningDate) = 2 And day(x.JoiningDate) = 29, 28, day(x.JoiningDate))) As PeriodEnd
From (
Values ('A-1', 'Alex', '2013-07-14')
, ('A-2', 'Sachin', '2017-08-14')
) As x(Emp_No, Emp_Name, JoiningDate)
``````
``````Select
PeriodStart = JoinDate,
From (
Values
('1', 'Alex', '2013-07-14'),
('2', 'Sachin', '2017-08-14')
) x(ID, Name, JoinDate)
Order by
JoinDate

--select dbo.DaysInMonth('6/15/2001')

CREATE FUNCTION dbo.DaysInMonth
(
@date datetime
) RETURNS int
AS
begin
set @date = dateadd(m, 1, @date)

return day(dateadd(d, -1, convert(varchar(20), month(@date)) + '/1/' + convert(varchar(20), year(@date))))
end``````

Hi,
example below i have data.

then require output is

Employee Alex has joined in 2013 then he will be show in all year until he resign.
and employee sachin will show/count from 2013 to 2018 becoz he resign on 2018.

then what will be the query.

Thanks
Basit.

Thanks, but how to do it in SQL query?

Thanks
Basit.

Man... you've gotta stop using recursive CTEs even for this simple stuff. It 1) sets a bad example for bigger stuff and 2) it takes tens of times longer and is hundreds of times more resource intensive even on the small stuff.

As a bit of a sidebar, I think it very interesting that the original post was more than 2 years ago, many possible solutions were offered and the Op is just now clarifying and hasn't actually solved this problem in over two years and then wants someone to write the actual code?

hi

As suggested by Jeff Moden I did not use recursive cte for tally table
I came up with the solution .. it may need to b reduced in size !!
My naming conventions are also NOT OK ...
Anyhow Here it goes

please click arrow to the left ofr drop create data script
``````drop table #data
go

create table #data
(
empno int ,
name varchar(100),
status varchar(100),
joining_date  date ,
resign_date date null
)
go

set dateformat dmy
go
insert into #data select 1,'Alex','On Board','14-07-2013',null
insert into #data select 2,'Sachin','Resgined','20-04-2013','05-04-2018'
go

select * from #data
go
``````
please click arrow to the left for SQL script
``````;WITH joiningdateyearcte
AS (SELECT Min(Year (joining_date)) AS minyear
FROM   #data),
tallycte
AS (SELECT TOP 100 N = Row_number()
OVER (
ORDER BY (SELECT NULL)) - 1
FROM   sys.all_columns st1
CROSS JOIN sys.all_columns st2),
fin1_cte
AS (SELECT n,
minyear + n     AS ok,
Year(Getdate()) AS yearok
FROM   tallycte,
joiningdateyearcte),
year_cte
AS (SELECT *
FROM   fin1_cte
WHERE  ok <= yearok),
fin_cte
AS (SELECT a.NAME                            AS aname,
b.NAME                            AS bname,
a.resign_date                     AS aresigndate,
Isnull(b.resign_date, '1-1-9999') AS bresigndate
FROM   #data a
JOIN #data b
ON a.empno = b.empno + 1)
SELECT ok,
Isnull(CASE WHEN fin_cte.aname = 'Sachin' AND Year(fin_cte.aresigndate)
>=
year_cte.ok THEN 1 END, 0) + Isnull(CASE WHEN fin_cte.bname = 'Alex' AND
Year(
fin_cte.bresigndate) >= year_cte.ok THEN 1 END, 0)
FROM   fin_cte,
year_cte

go
``````

That's better but, as you suggested, there's an "over calculation" on the "tally" part of the CTE that isn't necessary. Let's take a look at a slightly different method.

First, here's the test data and test table I used. I matched the column names up with the OP's latest post.

``````--===================================================================================
--      Create and populate a test table for this problem.
--      This is NOT a part of the solution.
--===================================================================================
--===== If it exists, drop the temp table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable;
GO
--===== Set the date format to match the OP's.
SET DATEFORMAT dmy
;
--===== Create a suitable test table for the given data.
CREATE TABLE #TestTable
(
EmpNo          INT             NOT NULL
,EmpName        VARCHAR(100)    NOT NULL
,Status         VARCHAR(20)     NOT NULL
,JoiningDate    DATE            NOT NULL
,ResignDate     DATE            NULL
)
;
--===== Populate the test table with some test data.
INSERT INTO #TestTable
(EmpNo,EmpName,Status,JoiningDate,ResignDate)
VALUES  (1,'Alex'  ,'On Board','14-07-2013',null        )
,(2,'Sachin','Resigned','20-04-2013','05-04-2017')
,(3,'Homer' ,'Resigned','01-01-2013','31-12-2017')
,(4,'Lisa'  ,'Resigned','01-01-2013','01-01-2017')  --just 1 day in final year
,(5,'Bart'  ,'Resigned','31-12-2016','01-01-2017')  --just 1 day in each year
,(6,'Marge' ,'Resigned','01-01-2017','01-01-2017')  --just 1 day in 1 year
;
--===== Display the contents of the test table.
SELECT * FROM #TestTable
;
GO
``````

Here's the code I used to solve this problem. You'll need to get the "fnTally" function from one of the links I've provided in the code. As you can see, it make life real simple for these types of things. I also made it so that the code is parameterized instead of instead of basing the range of desired years on what's in the table so that different years or ranges of years could be returned.

``````--===================================================================================
--      One of many possible solutions.
--      This one uses a high performance "Tally" function, which is available at the
--      following URL:
--      https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
--      Jeff Moden - 12 Oct 2019
--===================================================================================
--===== Declare the start and end date parameters.
-- These could be paramaters for a stored procedure or in iTVF
-- (inline Table Valued Function)
DECLARE  @pStartYear    DATE = '2012'
,@pEndYear      DATE = '2019'
;
WITH
ctePeriod AS
(--==== Create yearly period boundaries
FROM dbo.fnTally(0,DATEDIFF(yy,@pStartYear,@pEndYear))t
)--==== Return active number of employees per year even if active only 1 day.
-- This uses a join-trick from the following URL:
-- https://www.sqlservercentral.com/articles/finding-%e2%80%9cactive%e2%80%9d-rows-for-the-previous-month-sql-spackle-1
SELECT  Year           = DATENAME(yy,p.LoDate)
,EmployeeCount  = COUNT(DISTINCT tt.empno)
FROM  ctePeriod p
LEFT JOIN #TestTable tt
ON tt.JoiningDate < p.HiDate
AND ISNULL(tt.ResignDate,'9999') >= p.LoDate
GROUP BY DATENAME(yy,p.LoDate)
;
``````

Here's the output from the run above...

Thanks Jeff