MySql Query

UPDATE ordermanagementservice.order o 

join retailerservice.firm f on f.user_id = o.user_id and o.gstin = f.gstin

set o.firm_name = f.trade_name WHERE f.is_verified is TRUE AND f.status is TRUE and o.firm_name != f.trade_name and o.order_type = 'CONSUMER' ;

SELECT cd.user_id as CID, from_unixtime(cd.created_date/1000, '%d-%m-%Y')as 'Date of Sign-up',cd.name_from_pan as 'Consumer Name', cd.pan_no as 'PAN', cld.bureau_ref_id as 'Bureau Ref ID', cd.pin_code as Pincode,cd.city as City,cd.state as State,

cd.eligibility as 'Limit Value', nld.transaction_id as 'Txn ID',cd.lender as 'NBFC Partner',lrr.source as 'Rejection Source', 

SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT lrr.error),',',-1) as 'Reason', COUNT(DISTINCT lrr.error) as '# of Rejection Parameters', 

GROUP_CONCAT(DISTINCT lrr.error) as 'Rejection Parameters',GROUP_CONCAT(DISTINCT CONCAT(lrr.reason,'=',  lrr.value) SEPARATOR ';') as 'Errors & Values' 

FROM customerservice.customer_details cd 

LEFT JOIN ordermanagementservice.nbfc_loan_detail nld ON nld.user_id = cd.user_id

LEFT JOIN customerservice.lender_rejection_reason lrr ON lrr.user_id = cd.user_id

LEFT JOIN customerservice.customer_lender_detail cld ON cld.user_id = cd.user_id

WHERE cd.nbfc_stage IN ('AGREEMENT','STP','E_MANDATE') and FROM_UNIXTIME(cd.created_date/1000, '%Y-%m-%d') >= '2022-02-01' 

group by nld.transaction_id order by cd.created_date asc;

SELECT n.lender,n.loan_id,n.user_id, e.payment_date,e.amount,IFNULL(e.gateway_ref_no,t.reference_id) as reference_id ,e.payment_source

FROM ordermanagementservice.nbfc_loan_detail n

LEFT JOIN ordermanagementservice.emi_payment_new e on e.transaction_id = n.transaction_id and e.emi_status = 'COMPLETED' 

LEFT JOIN ordermanagementservice.transaction_response t on t.order_id = e.payment_id and t.tx_status = 'SUCCESS'

and e.payment_source not in ('QR_CODE', 'NACH') ORDER BY e.id DESC ;