CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user4121182
user4121182

Reputation:

SQL query to find employee aniversary

I need to find anniversary date and anniversary year of employees and send email in every 14 days.But I have a problem with last week of December when using the following query if start date and end date are in different years. 

    Select * from Resource
    where (DATEPART(dayofyear,JoinDate)
    BETWEEN DATEPART(dayofyear,GETDATE())
    AND DATEPART(dayofyear,DateAdd(DAY,14,GETDATE())))

Upvotes: 5

Views: 6528

Answers (4)

Jamiec
Jamiec

Reputation: 136164

Instead of comparing to a dayofyear (which resets to zero at jan 1st and is the reason your query breaks within 14 days of the end of the year) you could update the employee's joindate to be the current year for the purpose of the query and just compare to actual dates

Select * from Resource
-- Add the number of years difference between joinDate and the current year
where DATEADD(year,DATEDIFF(Year,joinDate,GetDate()),JoinDate) 
-- compare to range "today"
BETWEEN GetDate()
-- to 14 days from today
AND DATEADD(Day,14,GetDate())
-- duplicate for following year
OR DATEADD(year,DATEDIFF(Year,joinDate,GetDate())+1,JoinDate) -- 2016-1-1
BETWEEN GetDate() 
AND DATEADD(Day,14,GetDate())

Test query:

declare @joindate DATETIME='2012-1-1'
declare @today DATETIME = '2015-12-26'

SELECT @joinDate 
where DATEADD(year,DATEDIFF(Year,@joinDate,@today),@JoinDate) -- 2015-1-1
BETWEEN @today -- 2015-12-26
AND DATEADD(Day,14,@today) -- 2016-01-09
OR DATEADD(year,DATEDIFF(Year,@joinDate,@today)+1,@JoinDate) -- 2016-1-1
BETWEEN @today -- 2015-12-26
AND DATEADD(Day,14,@today) -- 2016-01-09

(H/T @Damien_The_Unbeliever for a simple fix)

The above correctly selects the joinDate which is in the first week of Jan (note I've had to fudge @today as Ive not managed to invent time travel).

The above solution should also solve the issue with leap years that was hiding in your original solution.

Update

You expressed in comments the requirement to select AnniversaryDate and Years of service, you need to apply some CASE logic to determine whether to add 1 (year or date) to your select

select *,
CASE 
    WHEN DATEADD(YEAR,DATEDIFF(Year,JoinDate,GETDATE()),JoinDate) < GetDate()
    THEN DATEDIFF(Year,JoinDate,GETDATE())+1
    ELSE DATEDIFF(Year,JoinDate,GETDATE())
END as [Years],
CASE WHEN DATEADD(YEAR,DATEDIFF(Year,JoinDate,GETDATE()),JoinDate) < GetDate()
    THEN DATEADD(YEAR,DATEDIFF(Year,JoinDate,GETDATE())+1,JoinDate) 
    ELSE DATEADD(YEAR,DATEDIFF(Year,JoinDate,GETDATE()),JoinDate) 
end as [AnniversaryDate] 
.... // etc

Upvotes: 7

Jesuraja
Jesuraja

Reputation: 3844

Try this:

DECLARE @Today DATE = GETDATE() --'12/25/2013'
DECLARE @Duration INT = 14

;WITH Recur AS
(
    SELECT @Today AS RecurDate
    UNION ALL
    SELECT DATEADD(DAY, 1, RecurDate)
    FROM Recur
    WHERE DATEDIFF(DAY, @Today, RecurDate)+1 < @Duration
)
SELECT
    r.*
FROM
    Resource r
    JOIN Recur 
        ON CONVERT(VARCHAR(5), JoinDate, 101) = CONVERT(VARCHAR(5), RecurDate, 101)
WHERE JoinDate <  @Today

Upvotes: 0

Eralper
Eralper

Reputation: 6622

You can use the SQL DATEADD() function with week number parameter Here is how you can use it:

DECLARE @date date = getdate()
Select * from Resource
where
    JoinDate BETWEEN @date AND DATEADD(ww,2,@date)

Upvotes: -2

Luc
Luc

Reputation: 1491

You could do this:

Select * from Resource
where DATEPART(dayofyear,JoinDate)
BETWEEN DATEPART(dayofyear,GETDATE())
AND DATEPART(dayofyear,DateAdd(DAY,14,GETDATE()))
OR
DATEPART(dayofyear,JoinDate)
BETWEEN (DATEPART(dayofyear,GETDATE()) + 365)
AND  (DATEPART(dayofyear,DateAdd(DAY,14,GETDATE())) + 365)

Upvotes: 0

Related Questions