Question

I m试图获得一份总付者名单,2000年。我只需要列入最近所附的“pymt_amt”,由该笔贷款中的每一笔贷款的顺序定出。下面的法典几乎给我提供了我所需要的东西,但“行为总金额”除外;“2000年”包括每一张ched子_pymt_amt,而不只是最近的ched子_pymt_amt_eff_dt。我如何把我的成果限制在只使用最近所附的“pymt_amt_eff_dt”的后面。

select 
    person_id, sum(sched_pymt_amt) as total_pay_amt
from
    (select 
         p.person_id, lp.loan_id, lp.sched_pymt_amt, lp.sched_pymt_amt_eff_dt, 
         row_number() over (partition by l.loan_id order by sched_pymt_amt_eff_dt desc) rn
     from
         person p 
     inner join
         loan_dtl l on p.person_id = l.brwr_person_id 
     inner join
         loan_repymt_plan lp ON l.loan_id = lp.loan_id
     where 
         1 = 1) a
where 
    rn = 1
having 
    sum(sched_pymt_amt) > 2000 --this is the problem line
group by 
    person_id

Answer 1

我建议你将内部质问减少到只需要1张表格。将<代码>rn=1作为该次舱的合并条件。而且,通过条款将保留条款移至集团之后。我还建议对贷款进行计算,因为一个人可能拥有超过1笔贷款。

SELECT
      p.person_id
    , sum(lp.sched_pymt_amt) AS total_pay_amt
    , count(distinct lp.loan_id) AS num_loans
FROM person p
INNER JOIN loan_dtl l ON p.person_id = l.brwr_person_id
INNER JOIN  (
            select
                  loan_id
                , sched_pymt_amt
                , row_number() OVER (
                    PARTITION BY loan_id 
                    ORDER BY sched_pymt_amt_eff_dt DESC
                    ) rn
            from loan_repymt_plan
        ) lp ON l.loan_id = lp.loan_id AND lp.rn = 1
-- where there is more then 0 to pay, details unknown
GROUP BY
      p.person_id
HAVING sum(lp.sched_pymt_amt) > 2000 --"having" occurs AFTER group by

友情链接