-1

I have a tabl pay_time_period with following detail

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

Now i need to find the number of periods in the month of the parameter date passed. How to tweak the below query

(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
)

The outcome should come as 4. since in may there are are 4 period_num

SSA_Tech124
  • 577
  • 1
  • 9
  • 25

2 Answers2

0

Try this:

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
gotqn
  • 42,737
  • 46
  • 157
  • 243
0
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')
Vikrant singh
  • 433
  • 1
  • 7
  • 25