--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,
PeriodEnd = DATEADD(d, dbo.DaysInMonth(JoinDate), 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))))
endHi,
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
SELECT LoDate = DATEADD(yy,t.N ,CONVERT(DATE,DATENAME(yy,@pStartYear)))
,HiDate = DATEADD(yy,t.N+1,CONVERT(DATE,DATENAME(yy,@pStartYear)))
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
Appreciate all your help..
As always
And thank you for your kind reply, Harish.