Untitled
unknown
plain_text
3 years ago
11 kB
3
Indexable
--Reconciliation --1. Wallet funding --a select * from freedom_proddb.dbo.transactions where tran_date between '2021-07-05 00:00:00.0000000' and '2021-07-06 00:00:00.0000000' and transaction_type = 'Wallet Funding'; select (select username from agents where f.wallet_number = agents.wallet_number) as agent_username,f.* from freedom_wallet_transaction f where remark like 'Fund Wallet%' and tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000' and wallet_number <> '10000000001'; --2. Withdrawal select * from freedom_proddb.dbo.transactions where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000' and transaction_type = 'Withdrawal'; select (select username from agents where f.wallet_number = agents.wallet_number) as agent_username,f.* from freedom_wallet_transaction f where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000' and remark like 'Withdrawal%' and wallet_number <> '10000000001'; --3. Deposit select * from freedom_proddb.dbo.transactions where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000' and transaction_type = 'Deposit'; select (select username from agents where f.wallet_number = agents.wallet_number) as agent_username,f.* from freedom_wallet_transaction f where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000' and remark like 'Deposit%' and wallet_number <> '10000000001'; --4. Recharge select * from freedom_proddb.dbo.transactions where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000' and transaction_type = 'Recharge'; select (select username from agents where f.wallet_number = agents.wallet_number) as agent_username,f.* from freedom_wallet_transaction f where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000' and wallet_number <> '10000000001' and remark like '%Top-up%' Union select (select username from agents where f.wallet_number = agents.wallet_number) as agent_username,f.* from freedom_wallet_transaction f where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000' and wallet_number <> '10000000001' and remark like '%E-Top Up%' select distinct (remark) from freedom_wallet_transaction f where tranid = '10939667' and tran_date between '2021-07-02 00:00:00.0000000' and '2021-07-03 00:00:00.0000000' --5. Bill payment select * from freedom_proddb.dbo.transactions where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000' and transaction_type = 'Bill payment'; select (select username from agents where f.wallet_number = agents.wallet_number) as agent_username,f.* from freedom_wallet_transaction f where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000' and remark not like '%top up%' and remark not like '%top-up%' and remark like 'Bill payment%' and wallet_number <> '10000000001'; --6. Transfer to Account select * from freedom_proddb.dbo.transactions where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000' and description = 'Transfer to Settlement Account'; select (select username from agents where f.wallet_number = agents.wallet_number) as agent_username,f.* from freedom_wallet_transaction f where tran_date between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000' and remark like 'TRANSFER REQUEST FROM%' and wallet_number <> '10000000001'; select * from medusa.dbo.transaction_logs where institutionid = 'FREE444942' and tran_date_time between '2021-07-04 00:00:00.0000000' and '2021-07-05 00:00:00.0000000' --Transfer_to_Settlement_Account_12pm-512pm_24th_May_2021_Batch_9 select *, (select phone_number from agents where username = f.agent_name) as phone_number from freedom_proddb.dbo.transactions f where tran_date between '2021-05-24 12:00:00.0000000' and '2021-05-24 17:12:00.0000000'and description = 'Transfer to Settlement Account' order by tran_id desc --Medusa responses for Freedom Select count(*) as count, response_code,response_desc from medusa.dbo.transaction_logs where tran_date_time between '2021-06-13 00:00:00.0000000' and '2021-06-20 00:00:00.0000000' and institutionid = 'FREE444942' group by response_code, response_desc order by response_code asc --Agents Performance summary select * from ( select concat(a.first_name,' ',a.last_name) as names, a.username, a.agent_type, a.terminal_id, a.phone_number, a.email, a.datecreated, a.activated, a.state, (select username from agents where id = a.parent_agent_id) as aggregator, (select username from agents where id = a.sub_parent_agent_id) as subaggregator, d.dep_val, d.dep_vol, w.with_val, w.with_vol, x.bill_val, x.bill_vol, y.airtime_val, y.airtime_vol from agents a left JOIN ( select agent_name,sum(amount) as dep_val ,count(0) as dep_vol from transactions where transaction_type = 'Deposit' and (tran_date BETWEEN '2021-05-28 00:00:00.0000000' and '2021-07-05 00:00:00.0000000') and status = 1 group by agent_name,transaction_type ) as d ON a.username = d.agent_name left JOIN ( select agent_name,sum(amount) as with_val,count(0) as with_vol from transactions where transaction_type = 'Withdrawal' and (tran_date BETWEEN '2021-05-28 00:00:00.0000000' and '2021-07-05 00:00:00.0000000') and status = 1 group by agent_name,transaction_type ) as w ON a.username = w.agent_name left JOIN ( select agent_name,sum(amount) as bill_val,count(0) as bill_vol from transactions where transaction_type = 'Bill Payment' and (tran_date BETWEEN '2021-05-28 00:00:00.0000000' and '2021-07-05 00:00:00.0000000') and status = 1 group by agent_name,transaction_type ) as x ON a.username = x.agent_name left JOIN ( select agent_name,sum(amount) as airtime_val,count(0) as airtime_vol from transactions where transaction_type = 'Recharge' and (tran_date BETWEEN '2021-05-28 00:00:00.0000000' and '2021-07-05 00:00:00.0000000') and status = 1 group by agent_name,transaction_type ) as y ON a.username = y.agent_name)b --Sanef Agent Onboarding select agent_id, username, bvn, account_no, bank_code, state, lga, wards, address, email, phone_number, datecreated, (select username from agents where id = a.parent_agent_id ) as aggregator, (select username from agents where id = a.sub_parent_agent_id ) as sub_aggregator from agents a where datecreated between '2021-06-12' and '2021-06-26' --Regent Performance SELECT _3line_commission, agent_commission, agent_name, bank_commission, created_on, parent_agent_commission, transaction_amount, tran_date, CASE WHEN transaction_type =0 THEN 'SAVING' WHEN transaction_type =1 THEN 'WITHDRAWAL' WHEN transaction_type =2 THEN 'TRANSFER' WHEN transaction_type =3 THEN 'THRIFT' WHEN transaction_type =4 THEN 'BILL_PAYMENT' else 'STAMP_DUTY_WITHDRAWAL' END as tran_type, transaction_channel FROM freedom_proddb.dbo.gravity_daily_commission WHERE created_on between '2021-06-01' AND '2021-07-01' and agent_name in (select username from freedom_proddb.dbo.agents where parent_agent_id = 21746); -- (select id from agents where agent_type = 'SUBAGGREGATOR'); select wallet_number, income_wallet_number from agents where username = 'Ahmed.Haruna036' select * from freedom_wallet_transaction where wallet_number = '53147664427' and created_on between '2021-06-01' AND '2021-06-21' --PTSP Transactions select * from ptsp_model where rrn = '162550156549'; select * from transactions where itex_tran_id = '162550156549'; select top(2) * from freedom_wallet_transaction where tranid = (select tran_id from transactions where itex_tran_id = '13032560') select s.created_on, s.amount, s.balance_after, s.balance_before, s.channel, s.remark, s.tran_date, s.tranid, s.tran_type from freedom_wallet_transaction s where wallet_number = (select wallet_number from agents where username = 'CHIOMA.Onwuchekwa') and created_on between '2021-05-28 00:00:00' and '2021-05-29 00:00:00' ORDER BY created_on desc --Activated but inactive agents select a.agent_id, a.username, a.bvn, a.account_no, a.bank_code, a.state, a.lga, a.wards, a.address, a.email, a.phone_number, a.datecreated, (select username from agents where id = a.parent_agent_id ) as aggregator, (select username from agents where id = a.sub_parent_agent_id ) as sub_aggregator from agents a where wallet_number in (select wallet_number from freedom_wallet where last_tran_date < '2021-05-01') and activated = 1 select sum(amount) as transaction_amount, count(*) as count, a.state from transactions t , agents a where t.agent_name = a.username and t.tran_date like '2021-05%' group by a.state --Active Terminal Daily select count(*) a from (select distinct terminal_id from transactions where tran_date between '2021-07-03' and '2021-07-04' and transaction_type = 'WITHDRAWAL' and terminal_id <> '') a select sum(cast(agent_commission as float)) as ag_1 , sum(cast(_3line_commission as float)) as ag_2 from gravity_daily_commission where tran_date BETWEEN '2021-06-24' AND '2021-06-25' select wallet_number from agents where username like 'oyetunji.olorunwa%' select * from archived_transaction where agent_name = 'oyetunji.olorunwa' order by tran_date desc select * from freedom_wallet_transaction where wallet_number = '10165944270' --Generate Statement for 2020 select s.created_on, s.amount, s.balance_after, s.balance_before, s.channel, s.remark, s.tran_date, s.tranid, s.tran_type from dbo.freedom_wallet_transaction s where wallet_number = '37990475017' and created_on between '2020-01-01 00:00:00' and '2020-08-01 00:00:00' union select s.created_on, s.amount, s.balance_after, s.balance_before, s.channel, s.remark, s.tran_date, s.tranid, s.tran_type from dbo.freedom_wallet_transaction_aug_sept s where wallet_number = '37990475017' and created_on between '2020-08-01 00:00:00' and '2020-09-01 00:00:00' union select s.created_on, s.amount, s.balance_after, s.balance_before, s.channel, s.remark, s.tran_date, s.tranid, s.tran_type from dbo.freedom_wallet_transaction_aug_sept_2 s where wallet_number = '37990475017' and created_on between '2020-09-01 00:00:00' and '2020-10-01 00:00:00' union select s.created_on, s.amount, s.balance_after, s.balance_before, s.channel, s.remark, s.tran_date, s.tranid, s.tran_type from dbo.freedom_wallet_transaction_bk22022020 s where wallet_number = '92887235063' and created_on between '2020-12-01 00:00:00' and '2021-01-01 00:00:00' order by created_on desc; select wallet_number from freedom_proddb.dbo.agents where username = 'SOTONYE.CHARLES';
Editor is loading...