oracle 查询获取本月期间数

4ioopgfo 于 2023-05-16 发布在 Oracle

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

我有一个pay_time_period表，详细信息如下

PAYROLL_ID              START_DATE          END_DATE                PERIOD_NUM
10                      01-MAY-2023         10-MAY-2023             1
10                      11-MAY-2023         18-MAY-2023             2
10                      19-MAY-2023         25-MAY-2023             3
10                      26-MAY-2023         05-JUN-2023             4

现在我需要找到参数date传递的月份中的期数。如何调整下面的查询

(select distinct 
COUNT(ptp1.PERIOD_NUM) OVER (PARTITION BY ptp1.PAYROLL_ID,papf.person_number) period_count
from pay_time_periods ptp1
where ptp1.TIME_PERIOD_ID = ptp.TIME_PERIOD_ID
and :p_process_date between ptp1.start_Date and ptp1.end_date
)

结果应该是4。由于在5月份有4个period_num

oracle

来源：https://stackoverflow.com/questions/76205840/query-to-get-count-of-periods-in-current-month

2条答案

按热度按时间

6mw9ycah1#

试试这个：

SELECT COUNT(ptp1.PERIOD_NUM)
from pay_time_periods ptp1
where ptp1.TIME_PERIOD_ID = ptp.TIME_PERIOD_ID
    and :p_process_date between ptp1.start_Date and ptp1.end_date
GROUP BY ptp1.PAYROLL_ID

赞(0）回复(0）举报 2023-05-16

rdlzhqv92#

SELECT COUNT(DISTINCT ptp.PERIOD_NUM) AS period_count
FROM pay_time_period ptp
WHERE ptp.START_DATE <= LAST_DAY(:p_process_date)
AND ptp.END_DATE >= TRUNC(:p_process_date, 'MM')

赞(0）回复(0）举报 2023-05-16

我来回答

oracle 查询获取本月期间数

2条答案

相关问题

热门标签

最新问答