SQLTeam.com | Weblogs | Forums

How to Get Employees Period as per Employee Joining Date


#1

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

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

Then what will be the query

Thanks
Basit


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

#3

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)


#4

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)

#6
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))))
end