oracle 计算支付日期

ndasle7k 于 2022-11-28 发布在 Oracle

关注(0)|答案(2)|浏览(345)

我们采用双月工资单，员工在每月的15日和最后一天领取工资。
如果这些日子是星期六、星期天或假日，那么我们在之前的最后一天得到报酬，而不是星期六、星期天或假日。
例如，以本周为例，4月15日是星期五，但它被定义为假日，所以人们应该在4月14日星期四支付工资。
我设法得到了一个部分查询工作，我可以排除周末和假期，但我可以使用一些帮助，找出什么日期的人应该支付。我的输出应该只包括支付日。我想产生一个年输出一月至十二月为今年。
我在想，如果不包括节假日和周末，也许last_day（）是一个月的最后一个发薪日？

CREATE OR REPLACE FUNCTION generate_dates(i_from_date IN DATE, i_end_date IN DATE, i_min_delta IN NUMBER, i_max_delta IN NUMBER, i_num_rows IN NUMBER) 
RETURN VARCHAR2 
SQL_MACRO 
IS 
BEGIN 
RETURN q'{
SELECT start_date, end_date
   FROM ( 
        SELECT pivot_date AS start_date, pivot_date + NUMTODSINTERVAL( i_min_delta + (i_max_delta-i_min_delta) * DBMS_RANDOM.VALUE(), 'hour') AS end_date 
        FROM (
            SELECT pivot_date + DBMS_RANDOM.VALUE() AS pivot_date            
            FROM ( 
                SELECT rownum AS rn, pivot_date AS pivot_date FROM ( 
                    SELECT TRUNC(i_from_date)+level-1 AS pivot_date FROM DUAL 
                    CONNECT BY TRUNC(i_from_date)+level-1<=TRUNC(i_end_date) 
                ) 
            ) 
            CONNECT BY LEVEL <= i_num_rows AND PRIOR rn = rn AND PRIOR sys_guid() IS NOT NULL 
        )
    ) 
}' ; 
END;
/

create table holidays(
          holiday_date DATE not null,
          holiday_name VARCHAR2(20),
          constraint holidays_pk primary key (holiday_date),
          constraint is_midnight check ( holiday_date = trunc ( holiday_date ) )
        );

INSERT into holidays (HOLIDAY_DATE,HOLIDAY_NAME)
        WITH dts as (
          select to_date('15-APR-2022 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'Passover 2022' from dual union all
          select to_date('31-DEC-2022 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'New Year Eve 2022' from dual
        )
        SELECT * from dts;

SELECT
        c.dt,
       to_char(c.dt, 'DY') as dow
FROM generate_dates(
TIMESTAMP '2022-01-01 00:00:00',
TIMESTAMP '2022-04-30 00:00:00',
 1, 'DAY') c
where 
to_char(c.dt, 'DY') NOT IN ('SAT', 'SUN') 
AND NOT EXISTS (
             SELECT 1
             FROM   holidays h
             WHERE  c.dt = h.holiday_date
           );

oracle

来源：https://stackoverflow.com/questions/74577363/calculating-pay-dates

2条答案

按热度按时间

niknxzdl1#

可以使用以下查询生成日期：

WITH pay_dates (dt) AS (
  SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YY'), LEVEL) - INTERVAL '1' DAY
  FROM DUAL
  CONNECT BY LEVEL <= 12
UNION ALL
  SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YY'), LEVEL - 1) + INTERVAL '14' DAY
  FROM DUAL
  CONNECT BY LEVEL <= 12
),
skip_weekends (dt) AS (
  SELECT CASE dt - TRUNC(dt, 'IW')
         WHEN 6 THEN dt - 2 -- Sunday
         WHEN 5 THEN dt - 1 -- Saturday
         ELSE        dt     -- Weekday
         END
  FROM   pay_dates
),
skip_holidays (dt, holiday_date) AS (
  SELECT w.dt, h.holiday_date
  FROM   skip_weekends w
         LEFT OUTER JOIN holidays h
         ON (w.dt = h.holiday_date)
UNION ALL
  SELECT CASE s.dt - TRUNC(s.dt, 'IW')
         WHEN 0
         THEN s.dt - 3 -- Monday
         ELSE s.dt - 1 -- Other weekday
         END,
         h.holiday_date
  FROM   skip_holidays s
         LEFT OUTER JOIN holidays h
         ON ( CASE s.dt - TRUNC(s.dt, 'IW')
              WHEN 0
              THEN s.dt - 3
              ELSE s.dt - 1
              END = h.holiday_date )
  WHERE  s.holiday_date IS NOT NULL
)
SELECT dt
FROM   skip_holidays
WHERE  holiday_date IS NULL
ORDER BY dt;

哪个过节：

CREATE TABLE holidays (holiday_date) AS
SELECT TRUNC(SYSDATE, 'YY') + INTERVAL '12' DAY FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'YY') + INTERVAL '13' DAY FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'YY') + INTERVAL '14' DAY FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'YY') + INTERVAL '45' DAY FROM DUAL;

赞(0）回复(0）举报 2022-11-28

7d7tgy0s2#

我想我会遵循的基本思想是得到所有的日期，没有周末和假期，然后找到每个月的最大一天，每个月的最大一天是〈= 15号。
获取不包含周末与假日得所有日期：

WITH noholidays AS
(
SELECT dt
FROM 
 generate_dates(
  TIMESTAMP '2022-01-01 00:00:00',
  TIMESTAMP '2022-04-30 00:00:00',
  1, 'DAY') c
WHERE to_char(dt, 'DY') NOT IN ('SAT', 'SUN')
 AND dt NOT IN (SELECT holiday_date FROM holidays)
)

然后，从那里，得到每个月的最大日期union ed，最大日期〈=每个月的15号。

SELECT max(dt) payday
 FROM noholidays
 GROUP BY to_char(dt, 'YYYY-MM')
UNION ALL
 SELECT max(dt)
 FROM noholidays
 WHERE to_number(to_char(dt, 'DD')) <= 15
 GROUP BY to_char(dt, 'YYYY-MM')
ORDER BY payday

有一个working example in this fiddle，尽管我使用CTE生成了当前年份的所有日期（borrowed from here），因为您的generate_dates函数没有为我编译（但您可以使用generate_dates）。
我认为您的最终解决方案可能类似于：

WITH 
noholidays AS (
 SELECT dt
 FROM generate_dates(
  TIMESTAMP '2022-01-01 00:00:00',
  TIMESTAMP '2022-04-30 00:00:00',
  1, 'DAY')
 WHERE to_char(dt, 'DY') NOT IN ('SAT', 'SUN')
  AND dt NOT IN (SELECT holiday_date FROM holidays)
)
SELECT max(dt) payday
FROM noholidays
GROUP BY to_char(dt, 'YYYY-MM')
UNION ALL
SELECT max(dt)
FROM noholidays
WHERE to_number(to_char(dt, 'DD')) <= 15
GROUP BY to_char(dt, 'YYYY-MM')
ORDER BY payday

赞(0）回复(0）举报 2022-11-28

我来回答

oracle 计算支付日期

2条答案

相关问题

热门标签

最新问答