I have a policy for the distribution of salary. Let's take an example of emp table of SQL-ORACLE: If an employee...
hired between 1-15 of a month, then he will get salary on last Friday of that month. But, if an employee hired between 16 up to end of any month, then he will get salary on the last Friday of the month. Please help me. This seems to me impossible!
I'll start with a solution and finish with an explanation.
select ename, hiredate, next_day( last_day( add_months( hiredate, case when extract (day from hiredate) > 15 then 1 else 0 end ) )-7, 'FRIDAY' ) as paydate from empThe CASE statement gives us either a 1 if the pay date should be next month or a zero if it should be this month. The resulting number of months is added to the HIREDATE using the ADD_MONTHS function. LAST_DAY gives us the last day of the month, from which we subtract 7 (number of days in a week) to give us a starting point for the NEXT_DAY function. The NEXT_DAY function finds the next occurrence of a given day after a given date. In this case, we are finding the next Friday. This nesting of functions always results in the last Friday of the month.
Here are the resulting rows:
ENAME HIREDATE PAYDATE ---------- ----------- ----------- SMITH 12/17/1980 1/30/1981 ALLEN 2/20/1981 3/27/1981 WARD 2/22/1981 3/27/1981 JONES 4/2/1981 4/24/1981 MARTIN 9/28/1981 10/30/1981 BLAKE 5/1/1981 5/29/1981 CLARK 6/9/1981 6/26/1981 SCOTT 4/19/1987 5/29/1987 KING 11/17/1981 12/25/1981 TURNER 9/8/1981 9/25/1981 ADAMS 5/23/1987 6/26/1987 JAMES 12/3/1981 12/25/1981 FORD 12/3/1981 12/25/1981 MILLER 1/23/1982 2/26/1982
For More Information
- Dozens more answers to tough SQL questions from Jason Law are available here.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.