用于计算服务年限的SQL代码

Question

我必须创建一个oracle sql来标识在下周内达到里程碑周年纪念的员工。实际上，我不得不创建两个代码。一个用于标识今年达到里程碑周年纪念日的每个员工，另一个用于标识下周达到里程碑周年纪念日的所有员工。

因此，如果我这周运行代码，它将捕获下周有周年纪念的任何人。对于这段代码，我能够创建一个代码来标识特定date...but上的周年纪念，但我无法创建一个代码来查看下周的所有7天，并告诉我谁都有周年纪念。

以下是用于标识在2013年3月31日周日的任何周年纪念的查询，该查询在今天3月27日运行。

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)

Answer 1

我结合了你们所有人提供的信息，并能够在每个星期一获得code.Here是运行代码的代码。

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)

Answer 2

您真的应该修复您的查询以使用ANSI标准连接和有意义的别名(也许per_people_x的px而不是a。

在任何情况下，解决问题的一种方法是交叉连接一组数字，并在未来几天内使用它们。对服务年限的计算只是年限之间的差额。周年纪念的支票只使用月份和日期。

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)

Answer 3

这可能会有所帮助-基于emp表的通用示例。租用日期与2013年3月最后一天之间的年份和月份。如果我正确理解了这个问题，那么到2013年3月31日，SMITH工作了32年零3个月--而不是周年纪念。琼斯工作了32年零0个月--周年纪念！我希望假设周年纪念是基于年份和月份而不是每周的是合理的。

您可以将月份的最后一天更改为任何日期-从今天起一周等。例如:Last_Day(Trunc(SYSDATE))+7...结果应该是相同的。

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 --
...
...