A friend of mine recently asked me the following question: "I have a table with names and birthdays. How do I find who's birthday is coming up in the next 15 days". Initially this it appears to be a simple question but it's a bit more complex than I originally thought.
I was going to post my solution here, then James Murtagh, from Red Gate, offered me two five-user licenses for their new product called Source Control for Oracle to give away to the readers of this blog. Instead, I'm going to run an informal contest to give away these sets of licenses (each valued at $1475). As with all contests, please read the terms and conditions from Redgate.
So here's the question (similar to my friend's question about birthday's but on the common "EMP" table): Suppose that I'm the HR manager and am planning to recognize the anniversary date that each employee was hired on. I'd like to know all the employees who's anniversary hire date is in the next 30 days.
Please post your solution in the comments section below. Every answer with a correct solution will have their name entered into the draw. Answers must be submitted by end of day on Friday March 15th. I'll announce the winners next week.
Notes:
- Use SYSDATE for today's date. I'll just alter the FIXED_DATE setting in oracle to set the SYDATE value for my testing.
- Write your query for the default EMP table. If you don't have the EMP table in your schema this article contains the scripts to generate it.
I'm looking forward to everyone's solutions!
Update: Please read the follow up post to see how I tested this solution.
Tricky - very enjoyable!
ReplyDeleteAt first I thought about using calendar arithmetic -- (SYSDATE - hiredate) modulo 365 -- but realized that leap years would make that calculation a lot trickier. Probably solvable, but too clever.
Then I thought about creating a 'synthetic' version of hiredate - swapping out the year hired with this calendar year (02-APR-81 would become 02-APR-13), which would let you do some easy filtering on the synthetic hiredate. But I that wouldn't cover the Dec/Jan window, where someone could be inside the 30 days window, but in a different YYYY...
I settled on using a row generator to get the next 30 days, and then joining this back to the EMP table on date parts.
SELECT look_ahead.*
,emp.empno
,emp.ename
,emp.hiredate
FROM
--look ahead 30 days, including today
(SELECT TRUNC(SYSDATE)-1 + n AS date_value
FROM
--row generator to get numbers 1-30 on
--from dual
(SELECT ROWNUM n
FROM ( SELECT 1 just_a_column
FROM dual
GROUP BY CUBE(1,2,3,4,5) )
WHERE ROWNUM <= 30
)
) look_ahead
--join on date parts to EMP table
JOIN emp
ON TO_CHAR(look_ahead.date_value, 'MON') = TO_CHAR(emp.hiredate, 'MON')
AND TO_CHAR(look_ahead.date_value, 'DD') = TO_CHAR(emp.hiredate, 'DD')
This SQL should do it:
ReplyDeleteSELECT *
FROM emp e
WHERE ADD_MONTHS(hiredate, (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM hiredate)) * 12)
BETWEEN TRUNC(SYSDATE) + 1 AND TRUNC(SYSDATE) + 31
SELECT hiredate
ReplyDeleteFROM (SELECT hiredate,
TRUNC(SYSDATE) today,
CASE
-- person was hired on a leap-day, but this is not a leap year
-- then treat the person as having been hired on Mar 1
WHEN TO_CHAR(hiredate, 'mmdd') = '0229'
AND TO_CHAR(TRUNC(SYSDATE, 'yyyy') + 59, 'mmdd') != '0229'
THEN
-- if you want to treat leap-day as Feb 28
-- TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '0228', 'yyyymmdd')
TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '0301', 'yyyymmdd')
ELSE
TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || TO_CHAR(hiredate, 'mmdd'), 'yyyymmdd')
END
birthdaythisyear
FROM scott.emp)
WHERE birthdaythisyear BETWEEN today AND today + 30
my query only returns the actual anniversary dates within the 30 day window. Simply add columns to pull additional EMP info as needed
ReplyDeleteSELECT *
FROM (SELECT emp.*,
TRUNC(SYSDATE) today,
CASE
-- person was hired on a leap-day, but this is not a leap year
-- then treat the person as having been hired on Mar 1
WHEN TO_CHAR(hiredate, 'mmdd') = '0229'
AND TO_CHAR(TRUNC(SYSDATE, 'yyyy') + 59, 'mmdd') != '0229'
THEN
-- if you want to treat leap-day as Feb 28
-- TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '0228', 'yyyymmdd')
TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '0301', 'yyyymmdd')
ELSE
TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || TO_CHAR(hiredate, 'mmdd'), 'yyyymmdd')
END
birthdaythisyear
FROM scott.emp)
WHERE birthdaythisyear BETWEEN today AND today + 30
Another version that assumes leap-day hiredates in non-leap years will be considered Feb 28
ReplyDeleteSELECT x.*
FROM (SELECT TRUNC(SYSDATE) today,
emp.*,
TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(hiredate, 'yyyy') - 1 years
FROM scott.emp) x
WHERE ADD_MONTHS(hiredate, 12 * years) BETWEEN today AND today + 30
OR ADD_MONTHS(hiredate, 12 * years + 12) BETWEEN today AND today + 30
ORDER BY hiredate
Hi Martin this is my solution:
ReplyDeleteselect empno,
ename,
hiredate,
to_date(to_char(hiredate,'DD/MM')||to_char(sysdate,'YYYY'),'DD/MM/YYYY') Anniversary
from emp
where to_date(to_char(hiredate,'DD/MM')||to_char(sysdate,'YYYY'),'DD/MM/YYYY')-trunc(sysdate) between 0 and 30;
Regards.
Eddie Molina
select ename from emp where hiredate between sysdate and sysdate + 15
ReplyDeleteIt ain't pretty but it works!
ReplyDeleteSELECT t.ename,
t.hiredate,
to_date(TO_CHAR(t.hiredate, 'DD-MON') || '-' || TO_CHAR(SYSDATE + 30, 'YYYY'), 'DD-MON-YYYY') AS anniv_date
FROM emp t
WHERE to_date(TO_CHAR(t.hiredate, 'DD-MON') || '-' || TO_CHAR(SYSDATE + 30, 'YYYY'), 'DD-MON-YYYY') BETWEEN
SYSDATE AND SYSDATE + 30
Don't know how to make a decent formatting here in the comment - it won't accept PRE tags ;-)
ReplyDeleteBut here's a piece of SQL that should do the desired thing ;-)
select empno
, ename
, hiredate
, add_months(
hiredate
, 12 * ceil(months_between(sysdate, hiredate) / 12)
) next_anniversary
from scott.emp
where add_months(
hiredate
, 12 * ceil(months_between(sysdate, hiredate) / 12)
) <= trunc(sysdate) + 30
order by next_anniversary
/
Ok, here goes:
ReplyDeletedefine no_of_days=30
SELECT ename
, hiredate
FROM emp
WHERE (ADD_MONTHS(hiredate
,12 * GREATEST ((EXTRACT (YEAR FROM SYSDATE)
-
EXTRACT (YEAR FROM hiredate)
)
,1
)
)
-
TRUNC(SYSDATE)
) BETWEEN 0 AND &no_of_days
;
Explanation:
- no_of_days is your 'n'. In your question you define it as 30
- I calculate the number of years between the year a person was hired and the current year
- Then I add months to the hiredate. The amount is 12 times the calculated number of years.
This gives me the aniversary date of this year.
- That aniversary date minus trunc(sysdate) is nmber of days between now and aniversary, which should be between 0 (it shouldn't be in the past) and no_of_days
- The 'greatest' is in there to rule out people who start today or in the next 30 days.
In that case the greatest makes sure I'm given next-years aniversary, which is more then 30 days away
By the way: the answer to the original question "Who's Birthday is it in "n" number of days?"
SELECT case n
when 1 then 'mine'
else 'I don''t know'
end "Answer"
FROM dual
;
Congrats on winning the contest! Please send me an email with your contact information so that I can pass it along to Red Gate.
DeleteHello Martin,
DeleteI think this solution will not work in cases when the next aniversary is in the next year. For instance, if sysdate is Dec 25, 2013, and the aniversary is on Jan 07, the solution will not show that employee.
Iudith's solution(s) work well in all cases.
Here is my solution:
ReplyDeleteselect * from (
select emp.*,
to_date(to_char(HIREDATE,'dd.mm.')||to_char(sysdate,'YYYY'),'dd.mm.yyyy') a_date
from emp)
where case when a_date<trunc(sysdate) then ADD_MONTHS(a_date,12) else a_date end between trunc(sysdate) and trunc(sysdate)+30
cu
Frank
select *
ReplyDeletefrom emp e
where to_date(to_char(e.hiredate,'ddmm')||extract(year from sysdate),'ddmmyyyy') between trunc(sysdate) and sysdate+30
or to_date(to_char(e.hiredate,'ddmm')||(extract(year from sysdate)+1),'ddmmyyyy') between trunc(sysdate) and sysdate+30
kovac.dalibor@gmail.com
--Get all the people in the next 15 days including today
ReplyDeleteSELECT * FROM scott.emp WHERE TRUNC(hiredate) BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 15 ORDER BY hiredate;
--Get all the people in the next 15 days excluding today
SELECT * FROM scott.emp WHERE TRUNC(hiredate) BETWEEN TRUNC(SYSDATE) + 1 AND TRUNC(SYSDATE) + 15 ORDER BY hiredate;
Create a table called integers:
ReplyDeleteCREATE TABLE integers
( i INTEGER NOT NULL PRIMARY KEY );
Load the values as follows:
INSERT INTO integers (i) VALUES (0);
INSERT INTO integers (i) VALUES (1);
INSERT INTO integers (i) VALUES (2);
INSERT INTO integers (i) VALUES (3);
INSERT INTO integers (i) VALUES (4);
INSERT INTO integers (i) VALUES (5);
INSERT INTO integers (i) VALUES (6);
INSERT INTO integers (i) VALUES (7);
INSERT INTO integers (i) VALUES (8);
INSERT INTO integers (i) VALUES (9);
Now, using the above created table,
we can retrieve the needed data as given below:
select e.* from (SELECT to_char(sysdate
+ 10*t.i+u.i,'mm-dd') AS next_30_dates
FROM integers u
CROSS
JOIN integers t
WHERE 10*t.i+u.i BETWEEN 0 AND 29
) date_range, emp e
where to_char(e.hiredate,'mm-dd') = date_range.next_30_dates;
This makes use of the cross join effect to get a range of dates(here using only the 'mm-dd' format) for the next 30 days and then map the same with the emp data to get the desired result.
Hi Martin,
ReplyDeletehere is my solution:
SELECT
tmp.empno
,tmp.ename
,tmp.job
,tmp.hiredate
,tmp.hiresary
FROM (SELECT
tb.empno
,tb.ename
,tb.job
,tb.hiredate
,TRUNC(SYSDATE) AS actual_date
,TO_DATE(TO_CHAR(SYSDATE
,'YYYY'
)
|| TO_CHAR(tb.hiredate
,'MMDD'
)
,'YYYYMMDD'
) AS hiresary
FROM emp tb
WHERE 0 = 0
) tmp
WHERE 0 = 0
AND tmp.hiresary BETWEEN tmp.actual_date
AND tmp.actual_date + 30
ORDER BY tmp.hiresary ASC
,tmp.hiredate ASC
;
Hi,
ReplyDeleteHere is my script, it has today as day number 1.
SELECT ename, hiredate
FROM emp
WHERE to_date(TO_CHAR(hiredate, 'dd-mm') ||'-2013', 'dd-mm-yyyy')
BETWEEN to_date(TO_CHAR(sysdate, 'dd-mm') ||'-2013', 'dd-mm-yyyy')
AND to_date(TO_CHAR(sysdate + 29, 'dd-mm') ||'-2013', 'dd-mm-yyyy')
Regards,
Ingimundur K. Gudmundsson
select *
ReplyDeletefrom emp
where to_date(to_char(hiredate,'ddmm')||to_char(sysdate,'yyyy'),'ddmmyyyy')
between trunc(sysdate) and trunc(sysdate)+30
order by to_date(to_char(hiredate,'ddmm')||to_char(sysdate,'yyyy'),'ddmmyyyy')
Hello Martin,
ReplyDeleteHere are two solutions, with a substitutable variable &N
for choosing the number of days ( 30 for the problem presented )
SELECT e.empno,
e.ename,
e.hiredate,
d.myday,
EXTRACT(YEAR FROM (d.myday - e.hiredate) YEAR(3) TO MONTH) AS hire_age
FROM
emp e,
( SELECT TRUNC(SYSDATE) + ROWNUM - 1 AS myday
FROM dual
CONNECT BY LEVEL <= &N ) d
WHERE
TO_CHAR(d.myday,'MMDD') = TO_CHAR(e.hiredate,'MMDD')
/
If however, we want to be fair towards somebody who was hired on Feb 29 and celebrate him however on Feb 28,
then the following solution will accomplish this:
SELECT e.empno,
e.ename,
e.hiredate,
ADD_MONTHS(e.hiredate, 12 * y.num_years) AS myday,
y.num_years AS hire_age
FROM
emp e,
( SELECT ROWNUM num_years
FROM dual
CONNECT BY LEVEL <= 100 ) y
WHERE
ADD_MONTHS(e.hiredate, 12 * y.num_years) BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + &N - 1
/
Thanks a lot & Best Regards,
Iudith Mentzel
ZIM Integrated Shipping Services Ltd.
Haifa, Israel
Congrats on winning the contest! Please send me an email with your contact information so that I can pass it along to Red Gate.
DeleteHello Martin,
DeleteThis was a surprise for me, there were indeed many interesting solutions submitted.
congratulations to all the participants :) :)
Oracle is indeed tricky when dealing with Feb 29, so for being strictly correct,
it looks that for very specific SYSDATE dates and hiredates of Feb 28 or Feb 29,
probably solutions that use MONTHS_BETWEEN will work correctly,
while solutions using ADD_MONTHS will not, and this is due to the specific end-of-month logic
of those two functions.
My full contact info is:
Mentzel Iudith
ZIM Integrated Shipping Services Ltd.
Haifa, Israel
e-mail: mentzel.iudith@il.zim.com
I already did one fairly simple solution - but it requires full table scan. So here is a second solution that allows index use:
ReplyDeletecreate or replace function fixdate(
p_date in date
, p_year in varchar2
)
return date deterministic
is
begin
return to_date(p_year||to_char(p_date,'MMDD'),'YYYYMMDD');
end fixdate;
/
create index emp_fixhiredate on emp (
fixdate(hiredate,'2000')
)
/
select empno
, ename
, hiredate
from emp
where fixdate(hiredate,'2000') between fixdate(sysdate,'2000')
and fixdate(sysdate,'2000') + 30
or fixdate(hiredate,'2000') between fixdate(sysdate,'1999')
and fixdate(sysdate,'1999') + 30
order by fixdate(hiredate,'2000')
/
The two different "between" clauses are for cases where we "wrap around" years. Try for example with sysdate=2013-12-30 :-)
Hello Martin again,
ReplyDeleteIn continuation of my previous post, here is yet another variant of my first solution above,
that will also cover the case of Feb 29 :
SELECT e.empno,
e.ename,
e.hiredate,
d.myday,
EXTRACT(YEAR FROM (d.myday - e.hiredate) YEAR(3) TO MONTH) AS hire_age
FROM
emp e,
( SELECT TRUNC(SYSDATE) + ROWNUM - 1 AS myday
FROM dual
CONNECT BY LEVEL <= &N ) d
WHERE
MOD( MONTHS_BETWEEN(d.myday, e.hiredate), 12) = 0
/
Thanks again & Best Regards,
Iudith Mentzel
ZIM Integrated Shipping Services Ltd.
Haifa, Israel
It will certainly not be the most beautifull solution, I guess, but here it is:
ReplyDeleteYou can add another where clause to limit the employees to be the ones you're the manager of by adding: and emp.mgr = .
select emp.ename
, emp.hiredate
, trunc(sysdate) -- test purpose
, to_date(to_char(emp.hiredate,'DD-MON')||'-'||to_char(sysdate,'YYYY')) -- test purpose
from emp
where to_date(to_char(emp.hiredate,'DD-MON')||'-'||to_char(sysdate,'YYYY')) - trunc(sysdate) between 0 and 30
Kind regards, Marga
select ename, hiredate
ReplyDeletefrom gbb_emp e
where to_char(hiredate, 'MM/DD') between to_char(sysdate, 'MM/DD') and to_char(sysdate+30, 'MM/DD')
oops, both of my submissions yesterday had end-of-year bugs in them. I think these should fix both
ReplyDeleteSELECT *
FROM (SELECT emp.*,
TRUNC(SYSDATE) today,
CASE
-- person was hired on a leap-day, but this is not a leap year
-- then treat the person as having been hired on Mar 1
WHEN TO_CHAR(hiredate, 'mmdd') = '0229'
AND TO_CHAR(TRUNC(SYSDATE, 'yyyy') + 59, 'mmdd') != '0229'
THEN
-- if you want to treat leap-day as Feb 28
-- TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '0228', 'yyyymmdd')
TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '0301', 'yyyymmdd')
ELSE
TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || TO_CHAR(hiredate, 'mmdd'), 'yyyymmdd')
END
birthdaythisyear,
CASE
-- person was hired on a leap-day, but this is not a leap year
-- then treat the person as having been hired on Mar 1
WHEN TO_CHAR(hiredate, 'mmdd') = '0229'
AND TO_CHAR(TRUNC(SYSDATE, 'yyyy') + 59, 'mmdd') != '0229'
THEN
-- if you want to treat leap-day as Feb 28
-- TO_DATE(TO_CHAR(SYSDATE, 'yyyy') || '0228', 'yyyymmdd')
TO_DATE((TO_CHAR(SYSDATE, 'yyyy') + 1) || '0301', 'yyyymmdd')
ELSE
TO_DATE((TO_CHAR(SYSDATE, 'yyyy') + 1) || TO_CHAR(hiredate, 'mmdd'),
'yyyymmdd'
)
END
birthdaynextyear
FROM scott.emp)
WHERE birthdaythisyear BETWEEN today AND today + 30
OR birthdaynextyear BETWEEN today AND today + 30;
SELECT x.*
FROM (SELECT TRUNC(SYSDATE) today,
emp.*,
TO_CHAR(SYSDATE, 'yyyy') - TO_CHAR(hiredate, 'yyyy') years
FROM scott.emp) x
WHERE ADD_MONTHS(hiredate, 12 * years) BETWEEN today AND today + 30
OR ADD_MONTHS(hiredate, 12 * years + 12) BETWEEN today AND today + 30
ORDER BY hiredate;
SELECT ename, next_bday
ReplyDeleteFROM (
-- Determine next birthday
SELECT ename, hiredate
, CASE WHEN cy > TRUNC( SYSDATE ) THEN cy ELSE ny END next_bday
FROM (
-- Calculate birthdays in current and next years
SELECT ename, hiredate
, ADD_MONTHS( hiredate, ( EXTRACT( YEAR FROM SYSDATE ) - EXTRACT( YEAR FROM hiredate ) ) * 12 ) cy
, ADD_MONTHS( hiredate, ( EXTRACT( YEAR FROM SYSDATE ) - EXTRACT( YEAR FROM hiredate ) + 1 ) * 12 ) ny
FROM emp
)
ORDER BY 3
)
WHERE ROWNUM <= 15 ;
Martin,
ReplyDeletethere is no solution to this problem, because the default EMP table contains no birthday.
Since this answer doesn't really satisfies me, I will assume for the rest of this comment, that the column HIREDATE contains the birthday of the employee. Furthermore, I read "who's birthday is coming up in the next 15 days" as "who's birthday is tomorrow or the day after tomorrow or ..." but not today.
Under these assumptions,
select empno, ename from scott.emp where trunc(hiredate) between trunc(sysdate+1) and trunc(sysdate+15);
will answer the question
Matthias
There are two simple solutions (I used the first solution, the second would have worked well):
ReplyDelete1. Use TRUNC(MONTHS_BETWEEN(SYSDATE, emp.birthdate) / 12), 0). Determinate the difference between employee's age at the begin and the end of interval.
2. Extract the year value from sysdate and sysdate + 30 and replace it in birthdate. Replace birthdate november, 29 with november, 28 if it necessary. Check that this values fall within the interval of 30 days from sysdate.
P.S. 365.25 - incorrect solution.
With best regards, Anton Sibiryakov.
SELECT v.AnniversaryDate,
ReplyDeletev.DeptNo,
v.EName,
v.HireDate
FROM (SELECT ADD_MONTHS(TRUNC(E.HireDate), 12 * (CurYear - TO_NUMBER(TO_CHAR(e.HireDate, 'YYYY')))) AnniversaryDate, -- for leap years
e.DeptNo,
e.EName,
e.HireDate,
Today
FROM emp e,
(SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) CurYear,
TRUNC(SYSDATE) Today
FROM DUAL
) y
) v
WHERE v.AnniversaryDate BETWEEN Today AND Today + 29
ORDER BY v.AnniversaryDate, v.DeptNo, v.EName
/
SELECT *
ReplyDeleteFROM emp
WHERE to_date(to_char(SYSDATE - 20, 'yyyy') ||
CASE to_char(hiredate, 'ddmm')
WHEN '2902' THEN
CASE
WHEN MOD(to_number(to_char(SYSDATE, 'yyyy')), 400) = 0 THEN
to_char(hiredate, 'ddmm')
WHEN MOD(to_number(to_char(SYSDATE, 'yyyy')),4) = 0 THEN
to_char(hiredate, 'ddmm')
ELSE
to_char(hiredate - 1, 'ddmm')
END
ELSE
to_char(hiredate, 'ddmm')
END, 'yyyyddmm') BETWEEN trunc(SYSDATE) AND trunc(SYSDATE) + 30
Maybe I didn't understand the question, because It's seem so simple. Given sysdate=date'1982-02-19', the employees who's anniversary hire date is in the next 30 days is:
ReplyDeleteselect * from emp where hiredate between add_months(date'1982-02-19',-12) and add_months(date'1982-02-19',-12)+30 order by hiredate;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ----- -------- ----- ------------------- ----- ---- ------
7499 ALLEN SALESMAN 7698 20/02/1981 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 22/02/1981 00:00:00 1250 500 30
2 rows selected.
Hello,
ReplyDeleteFirst of all, I would like to say that, the year parameter have to be ignored in this case. Because, we are not interested in year. So, the important thing is day and month. More importantly, when sysdate corresponds to december, the special case is arisen. Due to the year is ignored. When 30 days will be added to the sysdate. Sysdate will become to the beginning of the year. So, I have to take into account this special case.
Here is the query:
select * from emp where
(to_date(to_char(sysdate, 'DDMM'), 'DDMM') > to_date(to_char(sysdate+30, 'DDMM'), 'DDMM') and
((to_date(to_char((hiredate), 'DDMM'), 'DDMM') < to_date(to_char(sysdate+30, 'DDMM'), 'DDMM') and
to_date(to_char((hiredate), 'DDMM'), 'DDMM') > to_date('0101', 'DDMM'))
or
(to_date(to_char((hiredate), 'DDMM'), 'DDMM') > to_date(to_char(sysdate, 'DDMM'), 'DDMM') and
to_date(to_char((hiredate), 'DDMM'), 'DDMM') < to_date('3112', 'DDMM')))) or
to_date(to_char(hiredate, 'DDMM'), 'DDMM') BETWEEN to_date(to_char(sysdate, 'DDMM'), 'DDMM') AND to_date(to_char(sysdate+30, 'DDMM'), 'DDMM');
Also, the test script has been provided in order to be sure that the query works correctly for every period of the year.
--test
set serveroutput on;
declare
my_date date := '10/01/2013';
stmt varchar2(50);
cursor my_cur is
select * from emp where
(to_date(to_char(sysdate, 'DDMM'), 'DDMM') > to_date(to_char(sysdate+30, 'DDMM'), 'DDMM') and
((to_date(to_char((hiredate), 'DDMM'), 'DDMM') < to_date(to_char(sysdate+30, 'DDMM'), 'DDMM') and
to_date(to_char((hiredate), 'DDMM'), 'DDMM') > to_date('0101', 'DDMM'))
or
(to_date(to_char((hiredate), 'DDMM'), 'DDMM') > to_date(to_char(sysdate, 'DDMM'), 'DDMM') and
to_date(to_char((hiredate), 'DDMM'), 'DDMM') < to_date('3112', 'DDMM')))) or
to_date(to_char(hiredate, 'DDMM'), 'DDMM') BETWEEN to_date(to_char(sysdate, 'DDMM'), 'DDMM') AND to_date(to_char(sysdate+30, 'DDMM'), 'DDMM');
begin
for rec in 1..13
loop
stmt := 'alter system set fixed_date = '''||my_date||'''';
execute immediate stmt;
dbms_output.put_line('For sysdate: '||sysdate);
for rec2 in my_cur
loop
dbms_output.put_line(rec2.empno||' '||rec2.ename||' '||rec2.job||' '||rec2.mgr||' '||rec2.hiredate||' '||rec2.sal);
end loop;
dbms_output.put_line(null);
my_date := my_date + 30;
end loop;
end;
Warm Regards,
Caglar Polat (From Istanbul, Turkey)
unless i am missing something , that was really straight forward...
ReplyDeleteSELECT e.ename,
e.hiredate,
to_number(to_char(SYSDATE , 'yyyy')) - to_number(to_char(e.hiredate , 'yyyy')) years_service
FROM emp e
WHERE to_char(e.hiredate ,'mmdd')
BETWEEN to_char(SYSDATE,'mmdd' )
AND to_char(( SYSDATE+30 ),'mmdd' )
select * from emp,
ReplyDelete(
select TRUNC(SYSDATE)+level-1 AS dates
from dual
connect by level <= 15
)aniversery
where to_number(to_char(hiredate,'MM')||to_char(hiredate,'DD')) = to_number(to_char(aniversery.dates,'MM')||to_char(aniversery.dates,'DD'))
Regards
Intro
select * from emp,
ReplyDelete(
select TRUNC(SYSDATE)+level-1 AS dates
from dual
connect by level <= 15
)aniversery
where to_number(to_char(hiredate,'MM')||to_char(hiredate,'DD')) = to_number(to_char(aniversery.dates,'MM')||to_char(aniversery.dates,'DD'))
Regards
Intro
on a second thought ...
ReplyDeleteSELECT e.ename, e.hiredate
FROM emp e
WHERE
to_char(e.hiredate ,'mmdd') BETWEEN to_char(SYSDATE,'mmdd' ) AND to_char(( SYSDATE+30 ),'mmdd' )
OR
(
trim(to_char((sysdate + 30), 'month')) = 'january' AND
to_char(e.hiredate ,'mmdd') BETWEEN '0101' AND to_char(( SYSDATE+30 ),'mmdd' )
)
Hello Martin,
ReplyDeletethe problem is to find the next anniversary. So I simply add the difference of whole years between hire_date and SYSDATE (mind the boundary of sysdate = anniversary!)
WITH anniversary AS (
SELECT employees.*
,ADD_MONTHS (
hire_date
,(FLOOR (
MONTHS_BETWEEN (TRUNC(SYSDATE) - 1, hire_date) / 12
)
+ 1)
* 12
) next_anniversary
FROM employees
)
SELECT *
FROM anniversary
WHERE next_anniversary - TRUNC(SYSDATE) <= 30
ORDER BY next_anniversary
I had to test against the hr.employee table, because at the moment I work with a low (really low) bandwidth connection and the link to the emp script is still loading (more then 30 min now) while I finished the statement.
BTW: don't put me into the lottery, because I have no use for the Red Gate tool.
Regards
Marcus
P.S.: Sorry, don't know how to add correct tags to preserve code formatting
WITH days AS (SELECT LEVEL-1 NUM
ReplyDeleteFROM dual
CONNECT BY LEVEL <= 30)
SELECT *
FROM emp
,days
WHERE REMAINDER(MONTHS_BETWEEN(TRUNC(hiredate)
,TRUNC(SYSDATE) + days.num) / 12
, 1) = 0;
On the first look:
ReplyDeleteselect *
from emp
where add_months(hiredate,12*(to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy')) between trunc(sysdate) and trunc(sysdate) + 30
But it doesn't look too good...
My answer is as follows:
ReplyDeleteselect emp.* from emp
Inner Join ( select rownum-1 as rn from all_objects where rownum<=31 ) on mod(months_between(sysdate+rn,hiredate),12)=0
SELECT *
ReplyDeleteFROM emp
WHERE to_date(to_char(hiredate,'ddmm')||to_char(sysdate,'yyyy'),'ddmmyyyy') BETWEEN trunc(sysdate) AND trunc(sysdate) + 30
Sorry my first post was too soon:-)
ReplyDeleteSELECT *
FROM emp
WHERE to_date(to_char(hiredate,'ddmm')||to_char(sysdate,'yyyy'),'ddmmyyyy') BETWEEN trunc(sysdate) AND trunc(sysdate) + 30
OR to_date(to_char(hiredate,'ddmm')||to_char(to_number(to_char(sysdate,'yyyy'))+1),'ddmmyyyy') BETWEEN trunc(sysdate) AND trunc(sysdate) + 30
Hi Martin,
ReplyDeletehere is my solution. The most complicated thing was to consider the leap-year. The constant of 59 is the 28.02.
select * from (
select emp.*,
to_char(trunc(ADD_MONTHS(sysdate,12),'YYYY')-1,'DDD')-to_char(trunc(ADD_MONTHS(HIREDATE,12),'YYYY')-1,'DDD') leap_correct,
to_char(HIREDATE,'DDD') hireday,
to_char(sysdate,'DDD') sysday
from emp)
where hireday-sysday+case when hireday > 59 or hireday < sysday then leap_correct else 0 end+ case when hireday < sysday then 365 else 0 end BETWEEN 0 and 30
The result is the anniversary hire date between today and the next 30 days.
-di.ko-
Depending on what floats your boat, eh?
ReplyDeleteinclude trunc to include "today"
select e.empno, ename, hiredate
from emp e
where to_date(to_char(sysdate,'YYYY')||to_char(hiredate,'MMDD'),'YYYYMMDD')-trunc(sysdate) between 0 and 30;
select * from (
select e.empno, ename, hiredate
,to_date(to_char(sysdate,'YYYY')||to_char(hiredate,'MMDD'),'YYYYMMDD') anniversary
from emp e)
where anniversary >= trunc(sysdate)
and anniversary < sysdate + NUMTODSINTERVAL(30, 'day');
Here is a simple solution
ReplyDeleteselect name, birthdate
from person
where birthdate between trunc(sysdate) and trunc(sysdate+15)
order by birthdate
Regards
Fabrice Perotto
Not the most elegant solution but I believe it gets the job done:
ReplyDeleteWITH e AS
(SELECT
to_date(to_char(sysdate,'YYYY') || to_char(hiredate,'MMDD'),'YYYYMMDD') current_year_anniversary,
emp.*
FROM emp
)
SELECT
empno,
ename,
next_anniversary_date
FROM
(
SELECT
CASE
WHEN e.current_year_anniversary < trunc(SYSDATE) THEN
add_months(e.current_year_anniversary,12)
ELSE
e.current_year_anniversary
END next_anniversary_date,
e.*
FROM e
)
WHERE
next_anniversary_date - trunc(SYSDATE) <= 30;
Martin,
ReplyDeleteYour comment about it not being as easy as it looks intrigues me. I generated a quick solution and used your fixed_date hint to test different scenarios - thanks! Much easier than modifying dates to accomplish the same thing.
Here's my solution:
SELECT ENAME,
TO_DATE(TO_CHAR(HIREDATE,'DD-MON')||'-'||TO_CHAR(SYSDATE,'YYYY')) ANNIV,
TRUNC(SYSDATE)+30 NEXT30
FROM EMP
WHERE
TO_DATE(TO_CHAR(HIREDATE,'DD-MON')||'-'||TO_CHAR(SYSDATE,'YYYY'))
BETWEEN TRUNC(SYSDATE)+1 AND TRUNC(SYSDATE)+30
ORDER BY 2;
This SQL Works by bringing the Hire Date up to the next anniversary via the add_months, treating the current month as a reference point. This means that the search-window will be reliable up to a max. of 11 months.
ReplyDeleteUsed the with-clause just so I could quickly alter the reference date and search window.
WITH d AS
(SELECT trunc(sysdate) focus_date
,15 search_days
FROM dual)
SELECT d.focus_date
,emp.*
FROM emp
,d
WHERE add_months
(hiredate
,12*
(EXTRACT (YEAR FROM d.focus_date)
-EXTRACT (YEAR FROM hiredate)
+ CASE
WHEN EXTRACT (MONTH FROM d.focus_date)
> EXTRACT (MONTH FROM hiredate)
THEN 1
ELSE 0
END
)
)
BETWEEN trunc(d.focus_date)
AND trunc(d.focus_date)+d.search_days
Hi Martin,
ReplyDeleteI’ve tried to use your emp table (with “hiredate” instead of birthday) and I’ve edited a bit the dates.
Here is my attempt:
select e.ename, e.hiredate as "instead-of-birthday"
from emp e
where extract (month from e.hiredate) = extract(month from sysdate)
and extract (day from e.hiredate) between extract(day from sysdate) and extract(day from sysdate + 15)
My input to sqlfiddle was like this:
CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7, 2),COMM NUMBER(7, 2),DEPTNO NUMBER(2))
/
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20)
/
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30)
/
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30)
/
INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839,TO_DATE('12-MAR-1981', 'DD-MON-YYYY'), 2975, NULL, 20)
/
INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30)
/
INSERT INTO EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('14-MAR-1981', 'DD-MON-YYYY'), 2850, NULL, 30)
/
INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10)
/
INSERT INTO EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('19-MAR-1982', 'DD-MON-YYYY'), 3000, NULL, 20)
/
INSERT INTO EMP VALUES(7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10)
/
INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30)
/
INSERT INTO EMP VALUES(7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20)
/
INSERT INTO EMP VALUES(7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30)
/
INSERT INTO EMP VALUES(7902, 'FORD', 'ANALYST', 7566,TO_DATE('23-MAR-1981', 'DD-MON-YYYY'), 3000, NULL, 20)
/
INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10)
/
Commit
/
And here are the results:
ENAME INSTEAD-OF-BIRTHDAY
BLAKE March, 14 1981 00:00:00+0000
SCOTT March, 19 1982 00:00:00+0000
FORD March, 23 1981 00:00:00+0000
Kind Regards Alexander
alexander.andris@gmail.com
SELECT a.empno, a.ename, a.hiredate
ReplyDeleteFROM employee a
WHERE (TO_CHAR(a.hiredate, 'DDD') - TO_CHAR(SYSDATE, 'DDD') between 0 and 29)
SELECT a.empno, a.ename, a.hiredate
ReplyDeleteFROM employee a
WHERE (TO_CHAR(a.hiredate, 'DDD') - TO_CHAR(SYSDATE, 'DDD') between 0 and 29) OR
(TO_CHAR(a.hiredate, 'DDD') - TO_CHAR(SYSDATE, 'DDD') < 29 - 365) -- for next year
Hi Martin,
ReplyDeletemy second attempt is:
with dte as
( select extract(month from sysdate) as crt_mth
, extract(day from sysdate) as crt_day
from dual)
select e.ename, e.hiredate as "instead-of-birthday"
from emp e, dte
where extract (month from e.hiredate) = dte.crt_mth
and extract (day from e.hiredate) between dte.crt_day and (dte.crt_day + 15)
Kind Regards
Alexander,
where
ReplyDeletetrunc(months_between(sysdate + 30, birthday)/12) =
trunc(months_between(sysdate + 0, birthday)/12)
-- I'm not contesting :) It's my solution from
-- http://www.sql.ru/forum/actualthread.aspx?tid=735298#8348493
of cause, inequality :(
Deletetrunc(months_between(sysdate + 30, birthday)/12) != /* different full years */
trunc(months_between(sysdate + 0, birthday)/12)
select hiredate, ename, dz anniversery
ReplyDeletefrom
(select a3.*, case
when d0 >= sysdate
then d0
else add_months(d0,12)-delta1*deel_next
end dz
from
(
select a2.*, deel_now, deel_next,
to_date( to_char(sysdate,'yyyy')
||'-'
||to_char(hiredate-delta,'mm-dd')
,'yyyy-mm-dd')+delta*deel_now d0
from
(select a1.*,
case to_char(hiredate,'ddmm')
when '2902' then 1
else 0 end delta,
case to_char(hiredate,'ddmm')
when '2802' then 1
else 0 end delta1
from scott.emp a1
) a2
,
( select
case mod(year_now,4)
when 0 then 1
else 0 end deel_now,
case mod(year_now+1,4)
when 0 then 1
else 0 end deel_next
from
( select to_char(sysdate,'yyyy') year_now
from dual
)
)
) a3
)
where dz between sysdate and sysdate+30
order by dz
/
select e.ename form emp e join (
ReplyDeleteselect trunc(sysdate)-numtoyminterval(level-1) as sdate,
trunc(sysdate+15)-numtoyminterval(level-1) as edate,
from dual connect by level <= (select extract(year from sysdate)
- extract(year from min(ee.hirdate))
from emp ee) + 1) a
where e.hiredate between a.sdate and e.edate
As the HR manager I would use the follow query:
ReplyDeleteSELECT *
SELCT *
FROM emp
WHERE FLOOR (MONTHS_BETWEEN (SYSDATE, hiredate) / 12) <
FLOOR (MONTHS_BETWEEN (SYSDATE + 30, hiredate) / 12);
Explanation: all employees, who will have their
annyversary date whithin the next 30 days,
will then have more WHOLE(that is why FLOOR) service years
than they have now.
Actually they will have exact one whole year more, but
it doesn't matter in this case.
WBR
Jewgenij Moldawski
I assume people hired on Feb 29 would have this anniversary every four years, I also assume the list you want is between today and today+30.
ReplyDelete---- by James Su
SELECT empno,ename,hiredate
FROM (SELECT e.*
,DECODE(hire_mmdd
,'0229'
,CASE WHEN TO_CHAR(LAST_DAY(TO_DATE(this_year||'0201','YYYYMMDD')),'DD')='29' THEN this_year||'0229' END
,this_year||hire_mmdd
) AS this_anni
,DECODE(hire_mmdd
,'0229'
,CASE WHEN TO_CHAR(LAST_DAY(TO_DATE(next_year||'0201','YYYYMMDD')),'DD')='29' THEN next_year||'0229' END
,next_year||hire_mmdd
) AS next_anni
FROM (SELECT emp.*,TO_CHAR(HIREDATE,'MMDD') AS hire_mmdd
,TO_CHAR(SYSDATE,'YYYY') this_year
,TO_CHAR(TO_CHAR(SYSDATE,'YYYY')+1) next_year
,TO_CHAR(TRUNC(SYSDATE),'YYYYMMDD') today
,TO_CHAR(TRUNC(SYSDATE)+30,'YYYYMMDD') nextday
FROM emp
) e
)
WHERE this_anni BETWEEN today AND nextday
OR next_anni BETWEEN today AND nextday;
Following is a solution, yeilding expected results:
ReplyDeletewith inp as
(
select to_date('&i', 'DD-Mon-YYYY') dt from dual
),
get_anniv_msg as
(
select e.*, dt,
case
when to_char(hiredate, 'MMDD') between to_char(trunc(dt), 'MMDD') and to_char(trunc(dt) + 15, 'MMDD')
then 'Anniversary due within 15 days'
else 'No Anniversary yet.'
end ann_msg
from emp e, inp
)
select empno, ename, mgr, hiredate,
'Anniversary is due in ' || to_char(add_months(hiredate, round(months_between(dt, hiredate))) - dt) || ' days.' ann_msg
from get_anniv_msg
where ann_msg = 'Anniversary due within 15 days';
Hi Martin,
ReplyDeleteyesterday i wrote you twice but afterwards i've realized that my solution
was not complete (i was ignoring birthdays from the next month but within 15
days from now). So I've amended my query, now it looks like this:
WITH dte AS
(
SELECT extract(MONTH FROM sysdate) AS crt_mth
, extract(DAY FROM sysdate) AS crt_day
FROM dual
)
SELECT e.ename, e.hiredate AS "instead-of-birthday"
FROM emp e, dte
WHERE ( EXTRACT (MONTH FROM e.hiredate) = dte.crt_mth AND
EXTRACT (DAY FROM e.hiredate) BETWEEN dte.crt_day AND (dte.crt_day + 15)
)
OR (
((EXTRACT (MONTH FROM e.hiredate) = dte.crt_mth + 1
) OR (dte.crt_mth=12 and EXTRACT(MONTH FROM e.hiredate) =1 )) AND
(EXTRACT (DAY FROM LAST_DAY(e.hiredate)) - dte.crt_day + 1 + EXTRACT(DAY FROM e.hiredate) <= 15) )
or, an equivalent form is:
WITH dte AS
(
SELECT extract(MONTH FROM sysdate) AS crt_mth
, extract(DAY FROM sysdate) AS crt_day
FROM dual
)
SELECT e.ename, e.hiredate AS "instead-of-birthday"
FROM emp e, dte
WHERE ( EXTRACT (MONTH FROM e.hiredate) = dte.crt_mth AND
EXTRACT (DAY FROM e.hiredate) BETWEEN dte.crt_day AND (dte.crt_day + 15)
)
OR (
((EXTRACT (MONTH FROM e.hiredate) = dte.crt_mth + 1
) OR (dte.crt_mth=12 and EXTRACT(MONTH FROM e.hiredate) =1 )) AND
(EXTRACT (DAY FROM LAST_DAY(e.hiredate)) - dte.crt_day + EXTRACT(DAY FROM e.hiredate) < 15))
( still there's the question if you mean 15 days including today's date or today + NEXT 15 days - because between a and b there are b -a + 1 days but i supposed you mean today + 15 days
...)
I haven't tested this query thoroughly but i hope now it returns correct results.
Thank You for the puzzle/question.
Kind Regards
Alexander, Prague
SQL> select *
ReplyDelete2 from (select e.empno
3 ,e.ename
4 ,to_date(to_char(e.hiredate, 'dd.mm.') || to_char(sysdate, 'yyyy'), 'dd.mm.yyyy') d
5 from emp e)
6 where d between sysdate and sysdate + interval '30' day
7 /
EMPNO ENAME D
----- ---------- -----------
7566 JONES 02.04.2013
Hello!
ReplyDeleteI am not sure if my first post was correct enough, so I checked it today and here is my solution. Sorry for this mess.
select ename
from emp e join
(select TRUNC(SYSDATE) - numtoyminterval( level-1, 'YEAR') as sdate
,TRUNC(SYSDATE) - numtoyminterval(level-1,'YEAR')+15 as edate
from dual connect by level <= (select extract(year from SYSDATE) - extract(year from min(ee.hiredate))+1 from emp ee)) a
on e.hiredate between a.sdate and a.edate
/
WITH
ReplyDeleteData_SYSDATE AS (
SELECT To_Char( SYSDATE, 'MMDD') AS NowMMDD,
To_Char( SYSDATE, 'YYYY') AS ThisYear,
To_Char( Add_Months( SYSDATE, 12), 'YYYY') AS NextYear
FROM Dual
),
Data_Employees AS (
SELECT e.EmpNo AS EmpNo,
To_Char( e.HireDate, 'MMDD') AS HireMMDD
FROM Emp e
),
Data_Prepared AS (
SELECT e.EmpNo AS EmpNo,
To_Date( (CASE WHEN (e.HireMMDD < s.NowMMDD)
THEN s.NextYear
ELSE s.ThisYear END)
|| e.HireMMDD,
'YYYYMMDD') AS DateToCheck
FROM Data_SYSDATE s,
Data_Employees e
)
SELECT p.DateToCheck AS "Anniversary date",
Extract( YEAR FROM ((p.DateToCheck - e.HireDate)
YEAR(3) TO MONTH)) AS ".th Anniversary",
e.EName AS "Employee",
e.Job AS "Job",
d.DName AS "Department",
d.Loc AS "Location",
e.HireDate AS "Hire date"
FROM Data_Prepared p,
Emp e,
Dept d
WHERE (p.DateToCheck BETWEEN Trunc( SYSDATE) AND Trunc( SYSDATE + 30))
AND (e.EmpNo = p.EmpNo)
AND (d.DeptNo = e.DeptNo)
ORDER BY p.DateToCheck, e.HireDate;
/*
my email-address: niels.hecker@mt-ag.com
*/
The tricky situation is if someone was hired on the 29th of Feb one year, or you run the code on Feb 29th (but probably not both). If you try straight year substitutions, you'll get errors.
ReplyDeleteI'll opt for this.
select hiredate, add_months(hiredate, 12* (1+(extract (year from (sysdate - hiredate) year to month))))
from emp
where add_months(hiredate, 12* (1+(extract (year from (sysdate - hiredate) year to month)))) between sysdate and sysdate + 30;
Hello Martin,
ReplyDeleteI have already submitted the answer, but of course I made a beginner's mistake by not taking into account hire dates that, in regard to sysdate, come next year.
So here is the correct solution:
select *
from emp
where add_months(hiredate,12*(to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy')+case when to_char(sysdate,'mmdd') > to_char(hiredate,'mmdd') then 1 else 0 end)) between trunc(sysdate) + 1 and trunc(sysdate) + 30
It looks nasty!
Here is a little bit better version:
select *
from ( select e.*,
to_char(sysdate, 'yyyy')-to_char(e.hiredate, 'yyyy') years_between,
case
when to_char(sysdate, 'mmdd') > to_char(e.hiredate, 'mmdd') then
1
else
0
end correction
from emp e)
where add_months(hiredate, 12*(years_between + correction)) between trunc(sysdate) + 1 and trunc(sysdate) + 30
This code treats February 29th as "the end of February", so it will be treated as February 28th in common years. If it should have been treated as "the day after February 28th" (that is, as March 1st in common years) then another correction should be made:
select *
from ( select e.*,
to_char(sysdate, 'yyyy')-to_char(e.hiredate, 'yyyy') years_between,
case
when to_char(sysdate, 'mmdd') > to_char(e.hiredate, 'mmdd') then
1
else
0
end correction,
case
when to_char(e.hiredate, 'mmdd') = '0229' then
case
when to_char(sysdate, 'mmdd') > to_char(e.hiredate, 'mmdd') and
mod(to_char(sysdate, 'yyyy') + 1,4) = 0 and
(mod(to_char(sysdate, 'yyyy') + 1,100) != 0 or mod(to_char(sysdate, 'yyyy') + 1,400) = 0) then
0
when to_char(sysdate, 'mmdd') <= to_char(e.hiredate, 'mmdd') and
mod(to_char(sysdate, 'yyyy'),4) = 0 and
(mod(to_char(sysdate, 'yyyy'),100) != 0 or mod(to_char(sysdate, 'yyyy'),400) = 0) then
0
else
1
end
else
0
end corr_feb29
from emp e )
where add_months(hiredate, 12*(years_between + correction)) + corr_feb29 between trunc(sysdate) + 1 and trunc(sysdate) + 30
With this correction when sysdate is January 29th employees with the hire date February 29th will not be shown whether the year is leap or it is common.
Hi Martin,
ReplyDeletefor most of the case this will work:
WITH next_days AS
(
SELECT TO_CHAR(SYSDATE + level, 'MMDD') dt
FROM DUAL
CONNECT BY LEVEL <= 30
)
SELECT e.*
FROM emp e
JOIN next_days nd
ON (TO_CHAR(e.hiredate, 'MMDD') = nd.dt);
However there is an exception to consider. What if the hiredate is on 29-Feb and current year is not leap year?
In this case the solution becomes more complex. I have assumed that in case an employee is hired on 29-Feb and current year is not a leap year we celebrate the anniversary on 28-Feb.
Here is the query:
WITH leap_year AS
(
SELECT CASE
WHEN ( MOD(EXTRACT(YEAR FROM SYSDATE), 4) = 0
AND MOD(EXTRACT(YEAR FROM SYSDATE), 100) != 0
)
OR MOD(EXTRACT(YEAR FROM SYSDATE), 400) = 0
THEN
1
ELSE
0
END AS flag
FROM DUAL
)
, next_days AS
(
SELECT TO_CHAR(SYSDATE + level, 'MMDD') dt
FROM DUAL
CONNECT BY LEVEL <= 30
)
SELECT e.*
FROM emp e
CROSS JOIN leap_year l
JOIN next_days nd
ON ((CASE WHEN l.flag = 0 AND TO_CHAR(e.hiredate, 'MMDD') = '0229'
THEN '0228'
ELSE
TO_CHAR(e.hiredate, 'MMDD')
END
) = nd.dt);
Regards.
Alberto Faenza
afaenza@gmail.com
select ename, hiredate
ReplyDeletefrom emp
where to_char(hiredate, 'MMDD') between to_char(sysdate, 'MMDD') and to_char(sysdate+30, 'MMDD')
Hi Martin.
ReplyDeleteHere's my effort. Getting the correct answer following a year end required some thought!
SELECT e.*
FROM emp e
WHERE TO_DATE(TO_CHAR(hiredate,'mm/dd') ||
CASE WHEN TO_CHAR(hiredate,'mm/dd') < TO_CHAR(SYSDATE,'mm/dd')
THEN extract(year from sysdate) + 1
ELSE extract(year from sysdate)
END, 'mm/dd/yyyy')
BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 30;
Graham
I have two more solutions without the need to check if the year is leap or common... The problem is really interesting since it can be solved using completely different approaches (the first solution adds months to the hire date, the second solution "injects" day and month from hire date into the appropriate year).
ReplyDeleteBoth solutions treat February 29 as March 1 in common years. Queries do not check if the sysdate is less than hire date, which is probably a drawback since we do not want to celebrate someone's anniversary in the case of time travel. Or do we?
select *
from ( select e.*,
to_char(sysdate, 'yyyy')-to_char(e.hiredate, 'yyyy') years_between,
case
when to_char(sysdate, 'mmdd') > to_char(e.hiredate, 'mmdd') then
1
else
0
end correction,
decode(to_char(e.hiredate,'mmdd'),'0229',hiredate-1,hiredate) new_hiredate,
decode(to_char(e.hiredate,'mmdd'),'0229',1,0) corr_feb29
from emp e)
where add_months(new_hiredate, 12*(years_between + correction))+corr_feb29 between trunc(sysdate) + 1 and trunc(sysdate) + 30;
select *
from ( select e.*,
decode(to_char(e.hiredate,'mmdd'),'0229','0228',to_char(e.hiredate,'mmdd')) month_day,
case
when to_char(sysdate,'mmdd') > to_char(e.hiredate,'mmdd') then
to_char(to_char(sysdate,'yyyy')+1)
else
to_char(sysdate,'yyyy')
end year,
decode(to_char(e.hiredate,'mmdd'),'0229',1,0) corr_feb29
from emp e)
where to_date(month_day || year,'mmddyyyy')+corr_feb29 between trunc(sysdate) + 1 and trunc(sysdate) + 30;
Here are three options I came up with in the time I allotted to myself for this quiz.
ReplyDeleteselect ename, hiredate
from emp
where to_char(hiredate, 'MMDD') BETWEEN to_char(SYSDATE,'MMDD') AND to_char(SYSDATE + 30, 'MMDD');
select ename, hiredate
from emp
where trunc(MONTHS_BETWEEN(SYSDATE-1, hiredate)/12) != trunc(MONTHS_BETWEEN(SYSDATE+30, hiredate)/12);
select ename, hiredate
from emp
where to_date(to_char(SYSDATE, 'YYYY') || to_char(hiredate, '-MM-DD'), 'YYYY-MM-DD') BETWEEN TRUNC(SYSDATE) AND SYSDATE + 30;
Hi Martin :-D
ReplyDeleteMy name is Roberto and my email is munoz.gonzalez.roberto@gmail.com
Here is my solution,
thank you very much!
select empno,
ename,
hiredate
from (
select empno,
ename,
CASE WHEN to_date(to_char(sysdate,'YYYY')||to_char(hiredate,'MM')||to_char(hiredate,'DD'),'YYYYMMDD')<SYSDATE THEN
to_date(to_char(sysdate,'YYYY')-1||to_char(hiredate,'MM')||to_char(hiredate,'DD'),'YYYYMMDD')
ELSE
to_date(to_char(sysdate,'YYYY')||to_char(hiredate,'MM')||to_char(hiredate,'DD'),'YYYYMMDD')
END refdate,
hiredate
from emp
)
where refdate between sysdate and sysdate + 30
/
There are some nice solutions up here.
ReplyDeleteBut some really scare me!
I mean: how can one consider that "birthdate" or "hiredate" will ever again be "between trunc(sysdate) and trunc(sysdate + whatever)"
Can only mean that the solution is considered correct without even running (read testing) it once.
SELECT empno, ename, hiredate
ReplyDeleteFROM emp
WHERE to_date(TO_CHAR(hiredate, 'dd mon')
||extract(YEAR FROM sysdate))<= SYSDATE+30
AND to_date(TO_CHAR(hiredate, 'dd mon')
||EXTRACT(YEAR FROM SYSDATE))>= SYSDATE;
My (independent !) solution is like Iudith's but doesn't assume maximum length of service
ReplyDeleteselect e.ename,e.hiredate,add_months(e.hiredate,12 * years.year) anniversary, years.year
from emp e,
(select level year from dual connect by level < months_between(sysdate, (select min(hiredate) from emp)) / 12) years
where add_months(hiredate, 12 * years.year) between to_date(:today_date) and to_date(:today_date) + :no_days
Based on the EMP data in the link, setting today_date=01-FEB-2013, no_days=60 returns 3 employees, but with 01_FEB-2012, only 2 are returned
Hello Martin,
ReplyDeleteMy name is Hamid Talebian and my email is H.Talebian@pz.agro.nl
I know that I am too late, I misread 15 march as 25 march. Any how this is my solution:
There are two scenario's for an emlployee with hiredate 29-feb:
1- Anniversary is 28-feb or 29-feb (anniversary in every years)
2- Anniversary is allways 29-feb (anniversary only in leap years!!)
For scenario 1:
with
hr_emp as
(select empno, ename, job, mgr, hiredate, sal, comm, deptno
, case when to_char(sysdate, 'MMDD') <= to_char(hiredate, 'MMDD') then 0 else 1 end y_ind
,to_char(sysdate, 'RRRR') - to_char(hiredate, 'RRRR') years
-- ,to_char(hiredate, 'MMDD') MM_DD
from emp)
, emp_anniv as
(select empno, ename, job, mgr, hiredate, sal, comm, deptno, y_ind, years
, case to_char(hiredate, 'MMDD')
when '0228' then to_date(to_char((to_char(hiredate, 'RRRR') + years + y_ind))||'0228', 'rrrrmmdd')
else add_months(hiredate, 12 * (y_ind + years))
end anniversary
from hr_emp
)
select * from emp_anniv
where anniversary between trunc(sysdate) and trunc(sysdate) +30
For scenario 2:
with
hr_emp as
(select empno, ename, job, mgr, hiredate, sal, comm, deptno
, case when to_char(sysdate, 'MMDD') <= to_char(hiredate, 'MMDD') then 0 else 1 end y_ind
,to_char(sysdate, 'RRRR') - to_char(hiredate, 'RRRR') years
-- ,to_char(hiredate, 'MMDD') MM_DD
from emp)
, emp_anniv as
(select empno, ename, job, mgr, hiredate, sal, comm, deptno, y_ind, years
, case to_char(hiredate, 'MMDD')
when '0228' then to_date(to_char((to_char(hiredate, 'RRRR') + years + y_ind))||'0228', 'rrrrmmdd')
else add_months(hiredate, 12 * (y_ind + years))
end anniversary
, add_months(hiredate, 12 * (4 * ceil( (y_ind + years) / 4))) anniversary_spec
from hr_emp
)
select * from emp_anniv
where
(to_char(hiredate, 'MMDD') = '0229' and anniversary_spec between trunc(sysdate) and trunc(sysdate) +30)
or
(to_char(hiredate, 'MMDD') != '0229' and anniversary between trunc(sysdate) and trunc(sysdate) +30)
Warm regards,
Hamid Talebian
Hello, Martin!
ReplyDeleteWhen you plan to publish your solution? Looking forward to compare all answers.
Regards,
Pavel S.Vorontsov
SELECT ename,
ReplyDeletehiredate,
TO_DATE(TO_CHAR(hiredate,'MMDD')||TO_CHAR(SYSDATE,'YYYY'),'MMDDYYYY') anniversary
WHERE TO_DATE(TO_CHAR(hiredate,'MMDD')||TO_CHAR(SYSDATE,'YYYY'),'MMDDYYYY') BETWEEN SYSDATE AND SYSDATE + &no_of_days
Kim Berg Hansen pointed out to me that my query was not as robust as it should be.
ReplyDeleteThanks for that Kim.
He showed me that it had 2 flaws:
1. a person hired on the February 28 in a non-leap year would have anniversaries on February 29 in leap years, because of the way add_months works with last-day-of-the-month dates.
2. It did not list people having a anniversary just after new year if sysdate is just before new year (and the anniversary is in fact within 30 days).
I addressed both issues, and then found out that I liked the solution Kim build for issue 2 a lot better then my own.
So, with his permission, I incorporated that in my query (ceil(months_between())).
Here it is, the updated version:
DEFINE no_of_days=30
WITH raw_dates
AS (SELECT ename
, hiredate
, ADD_MONTHS(hiredate
,12 * GREATEST (CEIL (MONTHS_BETWEEN (SYSDATE,hiredate)/12),1)
) raw_anniversary_date
FROM emp
)
, dates
AS (SELECT ename
, hiredate
, CASE
WHEN EXTRACT (DAY FROM raw_anniversary_date) > EXTRACT (DAY FROM hiredate)
THEN raw_anniversary_date - 1
ELSE raw_anniversary_date
END anniversary_date
FROM raw_dates
)
SELECT ename
, hiredate
, anniversary_date
, anniversary_date - TRUNC(SYSDATE) "days from now"
FROM dates
WHERE (anniversary_date - TRUNC(SYSDATE)) BETWEEN 0 AND &no_of_days
;
explanation:
Query 'raw_dates' calculates the next anniversary date on or after sysdate
It does so by first calculating the number of years between hiredate and now (months_between/12), rounding this up (ceil) and then adding his amount of years times 12 months to hiredate.
If the number of years appears to be 0, hiredate equals today, so next anniversary will be next year. Hence greatest(... , 1)
Note that at this point a hiredate of 28th of February in a non-leap year is the last day of the month, and so for a sysdate in a leap year will return the 29th of February as raw_anniversary_date.
To overcome this, query 'dates' takes the result-set of 'raw_dates', and if the day-of-month of raw_anniversary_date is higher then the one from hiredate, raw_anniversary_date is decreased by 1.
Finally the main query takes the 'dates' result-set and only returns rows for which the anniversary date is between now and 30 days