我有一個名為tblDueDetails的表，其中包含我的客戶的所有到期詳細信息。這是我的表格：洛安諾（查爾）到期日期（日期）DueCode（字符）啟動（日期）結(jié)束時間 （日期）DueAMT （Double）DuePD （Double）以下是我到目前為止制作的代碼：$duesquery = "SELECT DUEDT, DUECD, STARTDT, ENDDT, DUEAMT, DUEPD FROM LLDUEDET WHERE LOANNO = '". $loanno ."' AND DUEAMT != DUEPD";$duessql = oci_parse($conn, $duesquery);$duesexec = oci_execute($duessql);if($duesexec){ while($duesrow = oci_fetch_assoc($duessql)){ $table .= ' <tr> <td>'. date('m/d/Y', strtotime($duesrow['DUEDT'])) .'</td> <td>'. trim($duesrow['DUECD']) .'</td> <td>'. date('m/d/Y', strtotime($duesrow['STARTDT'])) .'</td> <td>'. date('m/d/Y', strtotime($duesrow['ENDDT'])) .'</td> <td>'. number_format(trim($duesrow['DUEAMT']), 2) .'</td> <td>'. number_format(trim($duesrow['DUEPD']), 2) .'</td> </tr>'; }}這是我的解釋：- 當 DUEAMT 不等于 DUEPD 時，我需要獲取所有數(shù)據(jù)。- 但是，每次有一個DUEKD等于“PRI”和返回“INT”時，我需要通過添加PRI的DUEMT和INT的DUEMT來計算懲罰（請參閱表格）上面我的SQL的輸出： Due Date | Due | Start Date | End Date | Due Amount | Due Paid 11/30/2019 PRI 10/30/2019 11/30/2019 19,546.62 0.0011/30/2019 INT 10/30/2019 11/30/2019 10,808.38 1,062.8611/30/2019 PEN 10/30/2019 11/30/2019 1,500.00 1,500.00------- Insert Penalty Row Here ---------12/30/2019 PRI 11/30/2020 12/30/2019 20,158.62 0.0012/30/2019 INT 11/30/2020 12/30/2019 11,498.38 3,212.46------- Insert Penalty Row Here ---------1/03/2020 CCF 01/03/2020 01/03/2020 200.00 0.0001/03/2020 CFR 01/03/2020 01/03/2020 729.17 0.00我的問題是，當返回數(shù)據(jù) DUECD = PRI 和 IND 時，如何計算每個懲罰
1 回答
森林海
TA貢獻2011條經(jīng)驗 獲得超2個贊
類似這樣的東西應該有效（您可以在子查詢中更改懲罰計算）：
我假設只有當兩者都存在并且存在記錄時，才需要計算罰款。如果此假設不正確，并且您需要計算懲罰（如果存在任何一條或記錄），則可以從下面的查詢中刪除 。PRIINTPRIINThaving count(*) = 2
select LOANNO, DUEDT, DUECD, STARTDT, ENDDT, DUEAMT, DUEPD
from
(
SELECT LOANNO, DUEDT, DUECD, STARTDT, ENDDT, DUEAMT, DUEPD
, case
when DUECD = 'PRI' then 1
when DUECD = 'INT' then 2
else 10
end ord
FROM LLDUEDET
WHERE DUEAMT != DUEPD
union all
select pc.LOANNO, pc.DUEDT, pc.DUECD, pc.STARTDT, pc.ENDDT, pc.DUEAMT, coalesce(pa.DUEPD, 0.0) DUEPD
,3 ord
from (
select LOANNO, DUEDT, 'PEN' DUECD, STARTDT, ENDDT, ((SUM(DUEAMT)-SUM(DUEPD)) * 30) * .1 DUEAMT
from LLDUEDET
WHERE DUEAMT != DUEPD
and DUECD in ('PRI', 'INT')
group by LOANNO, DUEDT, STARTDT, ENDDT
having count(*) = 2
) pc
left join (
select LOANNO, DUEDT, SUM(DUEPD) DUEPD
from LLDUEDET
where DUECD = 'PEN'
group by LOANNO, DUEDT
) pa on pa.loanno = pc.loanno and pa.duedt = pc.duedt
) s
order by LOANNO, DUEDT, ord
- 1 回答
- 0 關注
- 169 瀏覽
添加回答
舉報
0/150
提交
取消