TYPE=VIEW
query=select \'2026-02-16\' AS `tdate`,`lc`.`head` AS `head`,`lc`.`sub_head` AS `sub_head`,`lc`.`nsc` AS `nsc`,`lc`.`acc_no` AS `acc_no`,max(`lc`.`fianl_date`) AS `fianl_date`,`lc`.`duration` AS `duration`,`lc`.`duration_time` AS `duration_type`,`lc`.`no_inst` AS `loan_inst`,`lc`.`ins_type` AS `ins_type`,`lc`.`interest` AS `cc_int`,`lc`.`od_int` AS `od_int`,(select `lt`.`cc_int_due` from `co-operativebank-purbadekhali`.`loan_deposit` `lt` where ((`lt`.`Acc_No` = `ld`.`Acc_No`) and (`lt`.`Date` <= \'2026-02-16\')) order by `lt`.`No` desc limit 1) AS `cc_int_due`,(select `lt`.`od_int_due` from `co-operativebank-purbadekhali`.`loan_deposit` `lt` where ((`lt`.`Acc_No` = `ld`.`Acc_No`) and (`lt`.`Date` <= \'2026-02-16\')) order by `lt`.`No` desc limit 1) AS `od_int_due`,(select `lt`.`jer_amt` from `co-operativebank-purbadekhali`.`loan_deposit` `lt` where ((`lt`.`Acc_No` = `ld`.`Acc_No`) and (`lt`.`Date` <= \'2026-02-16\')) order by `lt`.`No` desc limit 1) AS `jer_amt`,`lc`.`amt` AS `loan_amt`,(`lc`.`amt` - sum(`ld`.`Total_amt`)) AS `clear_amt`,sum(`ld`.`Total_amt`) AS `total_pay`,max(`ld`.`Date`) AS `pay_date`,timestampdiff(DAY,max(`ld`.`Date`),\'2026-02-16\') AS `days`,if((`lc`.`fianl_date` > max(`ld`.`Date`)),timestampdiff(DAY,max(`ld`.`Date`),max(`lc`.`fianl_date`)),0) AS `cc_days`,if((`lc`.`fianl_date` > max(`ld`.`Date`)),timestampdiff(DAY,`lc`.`fianl_date`,\'2026-02-16\'),timestampdiff(DAY,max(`ld`.`Date`),\'2026-02-16\')) AS `od_days`,if((`lc`.`ins_type` = \'One Time (1)\'),timestampdiff(DAY,`lc`.`date_open`,`lc`.`fianl_date`),if((`lc`.`ins_type` = \'Days (365)\'),1,if((`lc`.`ins_type` = \'Monthly (12)\'),30.41,if((`lc`.`ins_type` = \'Quaterly (4)\'),90,if((`lc`.`ins_type` = \'Halfyearly (2)\'),183,if((`lc`.`ins_type` = \'Yearly (1)\'),365,0)))))) AS `dur_day`,`lc`.`date_open` AS `issue_date`,if((\'2026-02-16\' < (`lc`.`fianl_date` + interval 1 day)),floor(if((`lc`.`ins_type` = \'One Time (1)\'),0,if((`lc`.`ins_type` = \'Days (365)\'),timestampdiff(DAY,`lc`.`date_open`,\'2026-02-15\'),if((`lc`.`ins_type` = \'Monthly (12)\'),timestampdiff(MONTH,`lc`.`date_open`,\'2026-02-15\'),if((`lc`.`ins_type` = \'Quaterly (4)\'),timestampdiff(QUARTER,`lc`.`date_open`,\'2026-02-15\'),if((`lc`.`ins_type` = \'Halfyearly (2)\'),(timestampdiff(MONTH,`lc`.`date_open`,\'2026-02-15\') / 6),if((`lc`.`ins_type` = \'Yearly (1)\'),timestampdiff(YEAR,`lc`.`date_open`,\'2026-02-15\'),0))))))),floor(if((`lc`.`ins_type` = \'One Time (1)\'),0,if((`lc`.`ins_type` = \'Days (365)\'),timestampdiff(DAY,`lc`.`date_open`,(`lc`.`fianl_date` + interval 1 day)),if((`lc`.`ins_type` = \'Monthly (12)\'),timestampdiff(MONTH,`lc`.`date_open`,(`lc`.`fianl_date` + interval 1 day)),if((`lc`.`ins_type` = \'Quaterly (4)\'),timestampdiff(QUARTER,`lc`.`date_open`,(`lc`.`fianl_date` + interval 1 day)),if((`lc`.`ins_type` = \'Halfyearly (2)\'),(timestampdiff(MONTH,`lc`.`date_open`,(`lc`.`fianl_date` + interval 1 day)) / 6),if((`lc`.`ins_type` = \'Yearly (1)\'),timestampdiff(YEAR,`lc`.`date_open`,(`lc`.`fianl_date` + interval 1 day)),0)))))))) AS `ins_no` from `co-operativebank-purbadekhali`.`loan_acc` `lc` join `co-operativebank-purbadekhali`.`loan_deposit` `ld` where ((`lc`.`id` = `ld`.`loan_id`) and (`lc`.`sub_head` = \'23101B\') and (`lc`.`acc_no` = \'111941000550\') and (`ld`.`Date` <= \'2026-02-16\')) group by `lc`.`id`
md5=cc841e1f4a54876406af47b61bb80e8b
updatable=0
algorithm=0
definer_user=admin
definer_host=%
suid=2
with_check_option=0
timestamp=2026-02-16 05:29:25
create-version=1
source=select \'2026-02-16\' as tdate,head,sub_head,nsc,lc.acc_no,max(lc.fianl_date) as fianl_date,lc.duration,lc.duration_time as duration_type,lc.no_inst AS loan_inst ,lc.ins_type,lc.interest as cc_int,lc.od_int,( select (`cc_int_due`) from loan_deposit lt where lt.acc_no=ld.acc_no and `lt`.`Date` <= \'2026-02-16\'  order by lt.no DESC limit 1 ) as cc_int_due,( select (`od_int_due`) from loan_deposit lt where lt.acc_no=ld.acc_no and `lt`.`Date` <= \'2026-02-16\'  order by lt.no DESC limit 1 ) as od_int_due,( select (`jer_amt`) from loan_deposit lt where lt.acc_no=ld.acc_no and `lt`.`Date` <= \'2026-02-16\'  order by lt.no DESC limit 1 ) as jer_amt,amt as loan_amt,(amt-sum(total_amt)) as clear_amt, sum(total_amt) as total_pay,max(date) as pay_date,timestampdiff(DAY,max(ld.Date),\'2026-02-16\') AS days,if(((lc.fianl_date) > max(ld.Date) ),timestampdiff(DAY,max(ld.date),max(lc.fianl_date)),0) AS cc_days,if(((lc.fianl_date) > (max(ld.Date))),timestampdiff(DAY,lc.fianl_date,\'2026-02-16\'),timestampdiff(DAY,max(ld.Date),\'2026-02-16\')) AS od_days,if((lc.ins_type = \'One Time (1)\'),timestampdiff(DAY,lc.date_open,lc.fianl_date),if((lc.ins_type = \'Days (365)\'),1,if((lc.ins_type = \'Monthly (12)\'),30.41,if((lc.ins_type = \'Quaterly (4)\'),90,if((lc.ins_type = \'Halfyearly (2)\'),183,if((lc.ins_type = \'Yearly (1)\'),365,0)))))) as dur_day,lc.date_open AS issue_date,if((\'2026-02-16\' < (lc.fianl_date + interval 1 day)),floor(if((lc.ins_type = \'One Time (1)\'),0,if((lc.ins_type = \'Days (365)\'),timestampdiff(DAY,lc.date_open,\'2026-02-15\'),if((lc.ins_type = \'Monthly (12)\'),timestampdiff(MONTH,lc.date_open,\'2026-02-15\'),if((lc.ins_type = \'Quaterly (4)\'),timestampdiff(QUARTER,lc.date_open,\'2026-02-15\'),if((lc.ins_type = \'Halfyearly (2)\'),(timestampdiff(MONTH,lc.date_open,\'2026-02-15\') / 6),if((lc.ins_type = \'Yearly (1)\'),timestampdiff(YEAR,lc.date_open,\'2026-02-15\'),0))))))),floor(if((lc.ins_type = \'One Time (1)\'),0,if((lc.ins_type = \'Days (365)\'),timestampdiff(DAY,lc.date_open,(lc.fianl_date + interval 1 day)),if((lc.ins_type = \'Monthly (12)\'),timestampdiff(MONTH,lc.date_open,(lc.fianl_date + interval 1 day)),if((lc.ins_type = \'Quaterly (4)\'),timestampdiff(QUARTER,lc.date_open,(lc.fianl_date + interval 1 day)),if((lc.ins_type = \'Halfyearly (2)\'),(timestampdiff(MONTH,lc.date_open,(lc.fianl_date + interval 1 day)) / 6),if((lc.ins_type = \'Yearly (1)\'),timestampdiff(YEAR,lc.date_open,(lc.fianl_date + interval 1 day)),0)))))))) AS ins_no from loan_acc lc,loan_deposit ld where lc.id=ld.loan_id and lc.sub_head=\'23101B\' and lc.acc_no=\'111941000550\' and ld.date<=\'2026-02-16\' group by lc.id
client_cs_name=utf8mb4
connection_cl_name=utf8mb4_general_ci
view_body_utf8=select \'2026-02-16\' AS `tdate`,`lc`.`head` AS `head`,`lc`.`sub_head` AS `sub_head`,`lc`.`nsc` AS `nsc`,`lc`.`acc_no` AS `acc_no`,max(`lc`.`fianl_date`) AS `fianl_date`,`lc`.`duration` AS `duration`,`lc`.`duration_time` AS `duration_type`,`lc`.`no_inst` AS `loan_inst`,`lc`.`ins_type` AS `ins_type`,`lc`.`interest` AS `cc_int`,`lc`.`od_int` AS `od_int`,(select `lt`.`cc_int_due` from `co-operativebank-purbadekhali`.`loan_deposit` `lt` where ((`lt`.`Acc_No` = `ld`.`Acc_No`) and (`lt`.`Date` <= \'2026-02-16\')) order by `lt`.`No` desc limit 1) AS `cc_int_due`,(select `lt`.`od_int_due` from `co-operativebank-purbadekhali`.`loan_deposit` `lt` where ((`lt`.`Acc_No` = `ld`.`Acc_No`) and (`lt`.`Date` <= \'2026-02-16\')) order by `lt`.`No` desc limit 1) AS `od_int_due`,(select `lt`.`jer_amt` from `co-operativebank-purbadekhali`.`loan_deposit` `lt` where ((`lt`.`Acc_No` = `ld`.`Acc_No`) and (`lt`.`Date` <= \'2026-02-16\')) order by `lt`.`No` desc limit 1) AS `jer_amt`,`lc`.`amt` AS `loan_amt`,(`lc`.`amt` - sum(`ld`.`Total_amt`)) AS `clear_amt`,sum(`ld`.`Total_amt`) AS `total_pay`,max(`ld`.`Date`) AS `pay_date`,timestampdiff(DAY,max(`ld`.`Date`),\'2026-02-16\') AS `days`,if((`lc`.`fianl_date` > max(`ld`.`Date`)),timestampdiff(DAY,max(`ld`.`Date`),max(`lc`.`fianl_date`)),0) AS `cc_days`,if((`lc`.`fianl_date` > max(`ld`.`Date`)),timestampdiff(DAY,`lc`.`fianl_date`,\'2026-02-16\'),timestampdiff(DAY,max(`ld`.`Date`),\'2026-02-16\')) AS `od_days`,if((`lc`.`ins_type` = \'One Time (1)\'),timestampdiff(DAY,`lc`.`date_open`,`lc`.`fianl_date`),if((`lc`.`ins_type` = \'Days (365)\'),1,if((`lc`.`ins_type` = \'Monthly (12)\'),30.41,if((`lc`.`ins_type` = \'Quaterly (4)\'),90,if((`lc`.`ins_type` = \'Halfyearly (2)\'),183,if((`lc`.`ins_type` = \'Yearly (1)\'),365,0)))))) AS `dur_day`,`lc`.`date_open` AS `issue_date`,if((\'2026-02-16\' < (`lc`.`fianl_date` + interval 1 day)),floor(if((`lc`.`ins_type` = \'One Time (1)\'),0,if((`lc`.`ins_type` = \'Days (365)\'),timestampdiff(DAY,`lc`.`date_open`,\'2026-02-15\'),if((`lc`.`ins_type` = \'Monthly (12)\'),timestampdiff(MONTH,`lc`.`date_open`,\'2026-02-15\'),if((`lc`.`ins_type` = \'Quaterly (4)\'),timestampdiff(QUARTER,`lc`.`date_open`,\'2026-02-15\'),if((`lc`.`ins_type` = \'Halfyearly (2)\'),(timestampdiff(MONTH,`lc`.`date_open`,\'2026-02-15\') / 6),if((`lc`.`ins_type` = \'Yearly (1)\'),timestampdiff(YEAR,`lc`.`date_open`,\'2026-02-15\'),0))))))),floor(if((`lc`.`ins_type` = \'One Time (1)\'),0,if((`lc`.`ins_type` = \'Days (365)\'),timestampdiff(DAY,`lc`.`date_open`,(`lc`.`fianl_date` + interval 1 day)),if((`lc`.`ins_type` = \'Monthly (12)\'),timestampdiff(MONTH,`lc`.`date_open`,(`lc`.`fianl_date` + interval 1 day)),if((`lc`.`ins_type` = \'Quaterly (4)\'),timestampdiff(QUARTER,`lc`.`date_open`,(`lc`.`fianl_date` + interval 1 day)),if((`lc`.`ins_type` = \'Halfyearly (2)\'),(timestampdiff(MONTH,`lc`.`date_open`,(`lc`.`fianl_date` + interval 1 day)) / 6),if((`lc`.`ins_type` = \'Yearly (1)\'),timestampdiff(YEAR,`lc`.`date_open`,(`lc`.`fianl_date` + interval 1 day)),0)))))))) AS `ins_no` from `co-operativebank-purbadekhali`.`loan_acc` `lc` join `co-operativebank-purbadekhali`.`loan_deposit` `ld` where ((`lc`.`id` = `ld`.`loan_id`) and (`lc`.`sub_head` = \'23101B\') and (`lc`.`acc_no` = \'111941000550\') and (`ld`.`Date` <= \'2026-02-16\')) group by `lc`.`id`
