CODEWITHSUNDEEP
sqloracle-database
TOM THOMAS
TOM THOMAS

Reputation: 11

SQL code to calculate service years

I have to create a oracle sql that identifies employees who hit a milestone anniversary within the next week. I actually had to to create two codes. One to identify every employee who hits a milestone anniversary this year, and second one to identify anyone who hits the milestone anniversary next week.

So if I run the code this week, it will capture anyone who has an anniversary next week. For this code I was able to create a code that would identify anniversary on a specific date...but I am unable to create a code that look at all 7 days of next week and tells me who all have anniversaries.

Here is the query for identifying anyone with an anniversary on Sunday 03/31/2013 when the query is run today 03/27/2013.

SELECT a.full_name AS Full_Name,
       b.adjusted_svc_date AS Service_Date,
       d.name Job_tittle,
       TRUNC ( (TO_NUMBER (TO_CHAR (SYSDATE + 4, 'mmddyyyy'))) -- TO_NUMBER (TO_CHAR (SYSDATE+4, 'MM/DD/YYYY'))
              - TO_NUMBER (TO_CHAR (b.adjusted_svc_date, 'mmddyyyy')))
          AS Years_OF_Service
  FROM per_people_x a,
       per_periods_of_service b,
       per_assignments_x c,
       per_jobs d
WHERE     a.person_id = c.person_id
       AND c.period_of_service_id = b.period_of_service_id
       AND c.job_id = d.job_id
       AND TRUNC (
                (TO_NUMBER (TO_CHAR (SYSDATE + 4, 'mmddyyyy')))
              - TO_NUMBER (TO_CHAR (b.adjusted_svc_date, 'mmddyyyy'))) IN
              (5, 10, 15, 20, 25, 30)
       AND a.person_type_id IN (6, 7)

Upvotes: 0

Views: 7476

Answers (3)

TOM THOMAS
TOM THOMAS

Reputation: 11

I combined the informations you all had provided and the was able to get the code.Here is the code for runnign the code on every monday.

SELECT ppx.full_name AS Full_Name,
       ps.adjusted_svc_date AS Service_Date,
       pj.name Job_tittle,
         (TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY')))
       - TO_NUMBER (TO_CHAR (ps.adjusted_svc_date, 'YYYY'))
          AS Years_OF_Service
  FROM per_peoples_x ppx,
       per_periods_of_services ps,
       per_assignment_x pax,
       per_jobs pj,
       (SELECT 6 AS d FROM DUAL
        UNION ALL
        SELECT 7 FROM DUAL
        UNION ALL
        SELECT 8 FROM DUAL
        UNION ALL
        SELECT 9 FROM DUAL
        UNION ALL
        SELECT 10 FROM DUAL
        UNION ALL
        SELECT 11 FROM DUAL
        UNION ALL
        SELECT 12 FROM DUAL
        ) days_in_future
 WHERE     ppx.person_id = pax.person_id
       AND pax.period_of_service_id = ps.period_of_service_id
       AND pax.job_id = pj.job_id
       AND   (TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY')))
           - TO_NUMBER (TO_CHAR (ps.adjusted_svc_date, 'YYYY')) IN
              (5, 10, 15, 20, 25, 30)
       AND TO_CHAR (SYSDATE + days_in_future.d, 'mmdd') =
              TO_CHAR (adjusted_svc_date, 'mmdd')
       AND ppx.person_type_id IN (6, 7)

Upvotes: 1

Art
Art

Reputation: 5782

This may help - general example based on emp table. Years and months between hiredate and last day of March 2013. If I understood the problem correctly then SMITH worked 32 years and 3 months by the Mar 31, 2013-not an anniversary. Jones worked 32 years and 0 months-anniversary! I hope it is reasonable to assume that anniversaries based on years and moths, not weekly.

You can change last day of months to any date - one week from today etc... For ex: Last_Day(Trunc(SYSDATE))+7 etc... The result should be the same.

SELECT * FROM 
(
 SELECT ename
      , EXTRACT(YEAR FROM (Last_Day(Trunc(SYSDATE)) - hiredate) YEAR TO MONTH ) years -- years from last day of Mar-2013 --
      , EXTRACT(MONTH FROM (Last_Day(Trunc(SYSDATE)) - hiredate) YEAR TO MONTH ) mnths       -- months from last day of Mar-2013 --
   FROM scott.emp 
 )
WHERE mnths = 0 -- if months = 0 then it is an anniversary --
/

ENAME    YEARS    MNTHS
-------------------------
SMITH    32        3
ALLEN    32        1
WARD     32        1
JONES    32        0  -- Only this row will be returned with Where mnths = 0 --
...
...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You should really fix your query to use ANSI standard joins and meaningful aliases (maybe px for per_people_x rather than a.

In any case, one way to solve your problem is to cross join in a set of numbers and use them for days in the future. The calculation for years of service is just the difference between the years. The check for the anniversary just uses the month and the day.

SELECT a.full_name AS Full_Name,
       b.adjusted_svc_date AS Service_Date,
       d.name Job_tittle,
       year(sysdate+days_in_future.d) - year(adjusted_svc_date) AS Years_OF_Service
  FROM per_people_x a,
       per_periods_of_service b,
       per_assignments_x c,
       per_jobs d,
       (select 0 as d from dual union all select 1 from dual union all select 2 from dual
       ) days_in_future
WHERE     a.person_id = c.person_id
       AND c.period_of_service_id = b.period_of_service_id
       AND c.job_id = d.job_id
       and to_char(sysdate+days_in_future.d, 'mmdd') = to_char(adjusted_svc_date, 'mmdd')
       and year(sysdate+days_in_future.d) - year(adjusted_svc_date) in (5, 10,15, 20, 25, 30)
       AND a.person_type_id IN (6, 7)

Upvotes: 0

Related Questions