sql query to get the minimum date for a payroll id but in from and where clause-CodePudding

I have the below query which I want to use in the valueset or a placeholder in Oracle system. I want to calculate the minimum default_paydate for a payroll_id. The issue is I cannot use aggegrate functions in the select statement as it is one of the restriction of the valueset. I can use in the from clause and then use it in the where condition.

Can i tweak the below query in anyway so that it gives me the least default_paydate but by using subquery in the from clause and where clause

SELECT
papf.payroll_name,
papf.period_type,
pcs.consolidation_set_name,
ptp.period_name,
to_char(ptp.start_date, ‘YYYY/MM/DD’) start_date,
to_char(ptp.end_date, ‘YYYY/MM/DD’) end_date,
to_char(ptp.cut_off_date, ‘YYYY/MM/DD’) cut_off_date,
to_char(ptp.regular_earn_date, ‘YYYY/MM/DD’) date_earned,
to_char(ptp.regular_process_date, ‘YYYY/MM/DD’) payroll_run_date,
to_char(ptp.default_paydate, ‘YYYY/MM/DD’) date_paid
FROM
pay_all_payrolls_f papf,
pay_time_periods ptp,
pay_consolidation_sets pcs
WHERE
papf.payroll_id = ptp.payroll_id
AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.consolidation_set_id = pcs.consolidation_set_id
AND ptp.cut_off_date IS NOT NULL
ORDER BY
papf.payroll_name,
ptp.start_date

CodePudding user response：

This is the way I understood it.

Create a subquery (or a CTE, as I did) which calculates the minimum default paydate; then join that CTE with the rest of your tables.

Simplified:

with min_date as
  (select payroll_id, 
          min(default_paydate) min_dflt_paydate
   from pay_time_periods
   group by payroll_id
  )
select
   md.min_dflt_paydate,            -->  this is a values you're looking for
   papf.payroll_name,
   -- the rest of your SELECT column list goes here,
from pay_time_periods ptp join min_date md on md.payroll_id = ptp.payroll_id
join     -- the rest of tables in FROM clause go here
where    -- conditions go here
order by -- whatever

CodePudding user response：

If you want the minimum for your result set then you can use the analytic version of the aggregation functions:

SELECT papf.payroll_name,
       papf.period_type,
       pcs.consolidation_set_name,
       ptp.period_name,
       to_char(ptp.start_date, ‘YYYY/MM/DD’) start_date,
       to_char(ptp.end_date, ‘YYYY/MM/DD’) end_date,
       to_char(ptp.cut_off_date, ‘YYYY/MM/DD’) cut_off_date,
       to_char(ptp.regular_earn_date, ‘YYYY/MM/DD’) date_earned,
       to_char(ptp.regular_process_date, ‘YYYY/MM/DD’) payroll_run_date,
       to_char(ptp.default_paydate, ‘YYYY/MM/DD’) date_paid,
       TO_CHAR(MIN(ptp.default_paydate) OVER (), 'YYYY/MM/DD')
         AS least_default_paydate,
       TO_CHAR(MIN(ptp.default_paydate) OVER (PARTITION BY papf.payroll_id), 'YYYY/MM/DD')
         AS least_default_paydate_per_id
FROM   pay_all_payrolls_f papf
       INNER JOIN pay_time_periods ptp
       ON (papf.payroll_id = ptp.payroll_id)
       INNER JOIN pay_consolidation_sets pcs
       ON (papf.consolidation_set_id = pcs.consolidation_set_id)
WHERE  trunc(sysdate) BETWEEN papf.effective_start_date
                      AND     papf.effective_end_date
AND    ptp.cut_off_date IS NOT NULL
ORDER BY
       papf.payroll_name,
       ptp.start_date

If you want to do it in the FROM clause, before the JOIN conditions and other filters are applied, then use a sub-query:

SELECT papf.payroll_name,
       papf.period_type,
       pcs.consolidation_set_name,
       ptp.period_name,
       to_char(ptp.start_date, ‘YYYY/MM/DD’) start_date,
       to_char(ptp.end_date, ‘YYYY/MM/DD’) end_date,
       to_char(ptp.cut_off_date, ‘YYYY/MM/DD’) cut_off_date,
       to_char(ptp.regular_earn_date, ‘YYYY/MM/DD’) date_earned,
       to_char(ptp.regular_process_date, ‘YYYY/MM/DD’) payroll_run_date,
       to_char(ptp.default_paydate, ‘YYYY/MM/DD’) date_paid,
       TO_CHAR(least_default_paydate, 'YYYY/MM/DD')
         AS least_default_paydate,
       TO_CHAR(least_default_paydate_per_payroll_id, 'YYYY/MM/DD')
         AS least_default_paydate_per_payroll_id
FROM   pay_all_payrolls_f papf
       INNER JOIN (
         SELECT ptp.*,
                MIN(ptp.default_paydate) OVER ()
                  AS least_default_paydate,
                MIN(ptp.default_paydate) OVER (PARTITION BY papf.payroll_id)
                  AS least_default_paydate_per_id
         FROM   pay_time_periods
         -- WHERE  ptp.cut_off_date IS NOT NULL
       ) ptp
       ON (papf.payroll_id = ptp.payroll_id)
       INNER JOIN pay_consolidation_sets pcs
       ON (papf.consolidation_set_id = pcs.consolidation_set_id)
WHERE  trunc(sysdate) BETWEEN papf.effective_start_date
                      AND     papf.effective_end_date
AND    ptp.cut_off_date IS NOT NULL
ORDER BY
       papf.payroll_name,
       ptp.start_date