History: January 2021

SQL Project Code at Suryoday Small Finance Bank

---#19816 Rera Account EOD Balance

select d.boo
      ,[desc]
      ,d.title1
      ,d.cid
      ,d.bal
      ,d.as_on_dt
from profile.dbo.deposit d join profile.dbo.brnch_master b
on d.boo = b.brcd and 
   b.AS_ON_DTE = convert(date,getdate()-1)
where d.type = 2010 and 
not(PGK = 1 and BAL = 0) and 
STAT <> 4 and 
d.AS_ON_DT between dateadd(day,1,EOMONTH(getdate()-1,-1)) and convert(date,(getdate()-1));

----To monitor credit transactions in Rera Master Collection Account

use PROFILE

declare @tdate date = convert(date,getdate()-2)
SELECT t.AS_ON_DTE AS_ON
       ,ROW_NUMBER() over (order by t.cid) [Sr.No]
       ,tied [Account No]
	   ,sum(t.TAMT) Total_Credit_Bal
	  ,0.7*sum(t.TAMT) [70% of Credit Transaction]
	  ,0.3*sum(t.TAMT) [30% of Credit Transaction]
from	dbo.TXN_HIST t
		join dbo.DEPOSIT d
		on t.CID = d.CID
where
		d.TYPE = 2010
		and not(d.PGK = 1 and BAL = 0) and d.STAT <> 4
		and t.AS_ON_DTE = @tdate
		and d.AS_ON_DT = @tdate and DRCR = 1
group by 
		T.AS_ON_DTE,t.CID


------To Query GL Balance Group Loan Balance

select sum([GL Balance])
from
(
select g.CC [Branch Code]
     , b.[DESC] [Branch Name]
     , b.STATENAM [State Name],'         ' "CRL Limit"
     ,g.BAL [GL Balance]
from GL_BALANCE g join BRNCH_MASTER b on g.CC = b.BRCD
where ACN = '301010060001'
and g.AS_ON_DTE = convert(varchar,getdate()-1,106) 
and b.AS_ON_DTE = convert(varchar,getdate()-1,106)
order by [Branch Name] ) t


----Cash Retention Limit report - URC


select * from
(select g.cc
      ,b.[DESC] [Branch_Name]
      , G.ACN,isnull(g.bal,0) [glbal]
from GL_BALANCE g join BRNCH_MASTER b on g.CC = b.BRCD
and g.AS_ON_DTE = '20 Oct 2020' 
and b.AS_ON_DTE = '20 Oct 2020') t
pivot ( sum(glbal)
for acn in ([300110010001],[300410010001],[300410020001],[301010060001],[301010010001],[301010020001],[301010030001],[301010040001],[301010050001],[301110010001],[301210010001],[301020010001],[301410010001] )) pivottable

----Count of DD issued

select c.DBRANCH [Home Branch Code]
      ,c.PAYAT [Transaction done at Branch Code]
      ,c.PAYDATE
      ,c.SERNO [DD Serial No.]
      ,c.AMOUNT
      ,c.PAYEENAME [Payee Name]
      ,c.PURCHACN
from CASH_DDPO c
join (
select DBRANCH
      ,count(remtype) cremtype
from CASH_DDPO 
where STATUS = 'paid'
group by DBRANCH
having count(remtype) >= 50) t
on c.DBRANCH = t.DBRANCH
where c.STATUS = 'paid'


----Withdrawal Transaction Lien Marked Acnt


select h.cid
      ,h.tamt
      ,h.tcmt
      ,h.DRCR
      ,h.ETC
from TXN_HIST h
join DEPOSIT d on h.CID = d.CID
--select d.CID,d.hldamt from DEPOSIT d
where 
d.HLDAMT > 0
and h.DRCR = 0
and d.TYPE between 3000 and 4000
and d.AS_ON_DT = '1 Nov 2020'
--and h.AS_ON_DTE = '1 Nov 2020'
and d.BAL > 0
and not(d.PGK = 1 and BAL = 0) and d.STAT <> 4 and h.TCMT <> 'WithHold(TDS)' and h.TCMT <> 'Interest/Dividend Available Not Credited'
and ETC <> 'ITOC'


----transaction freeze query

select d.BOO [Home Branch Code],h.brcd [Transaction Done at Branch Code],h.CID [Account Number],t.[DESC],h.TAMT [Amount],h.[TJD DATE] [Transaction Date],
h.tim [Time of Transaction],case when DRCR = 0 then 'DR' else 'CR' END as [Transaction Type Desc],
case when TYPE between 1000 and 1999 then 'Savings A/c' 
     when TYPE between 2000 and 2999 then 'Current A/c'
     when TYPE between 3000 and 3999 then 'Fixed A/c'
     when TYPE between 4000 and 5000 then 'Recurring A/c'
	  end as [Account Type],
 d.TITLE1 [Account Name],h.USERID [Input Done By],
 case when h.TSO like '%MCCKRID%' then  SUBSTRING((SUBSTRING(h.TSO,CHARINDEX('MCCKRID',h.TSO),13)),9,13)
      when h.TSO not like '%MCCKRID%' then '' 
 end as [Authorization done]
 from TXN_HIST h
join
(
select r.CID, r.rflg,r.[DESC], ROW_NUMBER() over (order by CID) as [row_no] from ACCT_LEVEL_RESTRICTION r
--where r.[DESC] like '%Restricts Debit%'
WHERE r.RFLG in ('001','002','003','004','006','011','023','R001') and AS_ON_DTE = convert(date,getdate()-1)) t
on h.CID = t.CID
and t.row_no = 1
join DEPOSIT d
on h.CID = d.CID
join Suryoday.dbo.ETC_TxnList etc
on h.ETC = etc.ETC
where h.AS_ON_DTE = convert(date,getdate()-1)
and d.AS_ON_DT = convert(date,getdate()-1)
and etc.FeesCharges in ('',NULL) and len(etc.FeesCharges) = 0
and COMPONENT in ('',NULL) and len(COMPONENT)=0
and h.ETC not in ('ITID','ITIS','INIS','IIAS','IIPS','INID')
and h.DRCR <> 1
and not(d.PGK = 1 and BAL = 0) and d.STAT <> 4
UNION ALL
select d.BOO [Home Branch Code],h.brcd [Transaction Done at Branch Code],h.CID [Account Number],t.[DESC] [Description],h.TAMT [Amount],h.[TJD DATE] [Transaction Date],
h.tim [Time of Transaction],case when DRCR = 0 then 'DR' else 'CR' END as [Transaction Type Desc],
case when TYPE between 1000 and 1999 then 'Savings A/c' 
      when TYPE between 2000 and 2999 then 'Current A/c'
	  when TYPE between 3000 and 3999 then 'Fixed A/c'
	  when TYPE between 4000 and 5000 then 'Recurring A/c'
	  end as [Account Type],
 d.TITLE1 [Account Name],h.USERID [Input Done By],
 case when h.TSO like '%MCCKRID%' then  substring((SUBSTRING(h.TSO,CHARINDEX('MCCKRID',h.TSO),13)),9,13)
      when h.TSO not like '%MCCKRID%' then '' 
 end as [Authorization done]
 from TXN_HIST h
join
(
select r.CID, r.rflg,r.[DESC], ROW_NUMBER() over (order by CID) as row_no from ACCT_LEVEL_RESTRICTION r
--where r.[DESC] like '%Restricts Debit%'
WHERE r.RFLG in ('008','009') and AS_ON_DTE = CONVERT(date,GETDATE()-1)) t
on h.CID = t.CID
and t.row_no = 1
join DEPOSIT d
on h.CID = d.CID
join Suryoday.dbo.ETC_TxnList etc
on h.ETC = etc.ETC
where h.AS_ON_DTE = convert(date,getdate()-1)
and d.AS_ON_DT = convert(date,getdate()-1)
and etc.FeesCharges in ('',NULL) and len(etc.FeesCharges) = 0
and COMPONENT in ('',NULL) and len(COMPONENT)=0
and h.ETC not in ('ITID','ITIS','INIS','IIAS','IIPS','INID')
and not(d.PGK = 1 and BAL = 0) and d.STAT <> 4


-----#31018 Video KYC Account Opening_updated

use PROFILE
declare @tdate date = convert(date,getdate()-1)
select 
	 d.CID Account_No
	,c.ACN Customer_Id
        ,d.BOO BranchCode
	,b.[DESC] [BranchName]
	,c.FNAME First_Name
	,c.LNM Last_Name
        ,c.ZMPH Mobile_No
	,c.EMAIL Email_Id
	,c.oit Identification_Type
	,format(d.ODT,'dd-MMM-yyyy') Account_Opening_Date
	,p.[DES] [Variant of Account Opened]
	,format(d.ZACTDAT1, 'dd-MMM-yyyy') Account_Activation_Date
from dbo.CUSTOMER_MASTER c
join dbo.DEPOSIT d
on c.ACN = d.ACN
join dbo.PRODCTL p
on d.TYPE = p.TYPE
join UTBLBRCD b
on b.BRCD = d.BOO
where 
d.AS_ON_DT = @tdate
and c.CIF_STAT = 1 and d.ACCT_OPN_DTE = @tdate
--and c.acn <> '140000001'
and c.OIT in (31,36)
and not(d.PGK = 1 and d.BAL = 0) and d.STAT <> 4 and d.[TYPE] < 2000
--and d.type in ('1008','1005','2003','2001','1004','2006','1003')

------FD not closed but Fund got transferred


select d.CID,d.MDT,d.BAL
from DEPOSIT d
join TXN_HIST t
on d.CID = t.CID
where 
d.mdt <> ''
and convert(date,convert(date,d.MDT,105),23) < convert(date,getdate(),103)
and d.TYPE between 3000 and 4000
and d.STAT <> 4
and d.pgk = '0'
and d.BAL <= 0
and d.AS_ON_DT = convert(varchar,GETDATE()-1,106)
--and d.AS_ON_DT < convert(varchar,GETDATE()-1,106) and t.AS_ON_DTE < convert(varchar,GETDATE()-1,106)

Tuesday, 26 January 2021

SQL Project