Published on : June 09, 2020
Backend URL : https://loantap.in/wp-admin/post.php?post=758515&action=edit
Template :multi_dealer_disburse_block
Frontend URL : https://loantap.in/banking
Query :
with q0 as ( /* Get all records grouped by txn_set_id whose success_ref column is 'NULL' and head is 'Multi Dealer' and subgroup='Dealer Payment Account' and (credit-debit) greater then 0 */ SELECT sublan_id, field1 FROM loan_entries WHERE head = 'Multi Dealer' AND (credit-debit) > 0 AND success_ref IS NULL AND txn_ref IS NOT NULL AND subgroup='Dealer Payment Account' GROUP BY txn_set_id ), q1 as ( /*Get scheme_id from sublan where meta_key in ('scheme_id','product_id')*/ SELECT field1, meta_value as scheme_id FROM q0 JOIN sublan ON q0.sublan_id=sublan.object_id WHERE meta_key in ('scheme_id','product_id') ), q2 as ( /* Add two new columns to count ready and suspense Ready: If Field1 empty column value is 1 else 0 Suspense: If Field1 not empty column value is 1 else 0 */ SELECT scheme_id, IF(field1 IS NULL or field1 = '', 1, 0) as ready, IF(field1 IS NOT NULL or field1 != '', 1, 0) as suspense FROM q1 ) /* Group by scheme_id to get counts Count of all records as total Sum of ready columns as ready Sum of suspense columns as suspense */ SELECT scheme_id, count(1) as total, sum(ready) as ready, sum(suspense) as suspense FROM q2 GROUP BY scheme_id