SELECT * FROM dbo.
BL_Customer WHERE FirstName IS NULL OR FirstName=''
SELECT * FROM dbo.BL_Customer WHERE FatherHusbandName IS NULL OR FatherHusbandNa
me=''
SELECT * FROM dbo.BL_Customer WHERE GenderId NOT IN(1,2,3)--Member with invalid
gender id
SELECT * FROM dbo.BL_Customer WHERE DOB >= dbo.PCBS_udfGetDate() OR DOB<'01-01-1
900'--Member with invalid DOB
SELECT * FROM dbo.BL_Customer WHERE Age >= 100 OR Age<=0--Member with invalid Ag
e
SELECT * FROM BL_Customer WHERE CateogryID NOT IN(SELECT CateogryID FROM [Link]
agory)--Member with invalid category id
SELECT * FROM BL_Customer WHERE CustNatureID NOT IN(SELECT CustNatureID FROM dbo
.NatureOfCustomer)--Member with invalid nature of customer id
SELECT * FROM BL_Customer WHERE OccupationID NOT IN(SELECT OccupationID FROM dbo
.Occupation)--Member with invalid occupation id
SELECT * FROM BL_Customer WHERE CommunityID NOT IN(SELECT CommunityID FROM dbo.C
ommunity)--Member with invalid community id
SELECT * FROM BL_Customer WHERE QualificationID NOT IN(SELECT QualificationID FR
OM [Link])--Member with invalid qualification id
SELECT * FROM BL_Customer WHERE MaritalStatusID NOT IN(SELECT MaritalStatusID FR
OM [Link])--Member with invalid marital status id
SELECT * FROM BL_Customer WHERE ReligionID NOT IN(SELECT ReligionID FROM [Link]
igion)--Member with invalid religion id
SELECT * FROM BL_Customer WHERE NationalityID NOT IN(SELECT NationalityID FROM d
[Link])--Member with invalid nationality id
SELECT * FROM BL_Customer WHERE IdProofID NOT IN(SELECT IdProofID FROM [Link]
of)--Member with invalid idproof id
SELECT * FROM BL_Customer WHERE ResidentialProofId NOT IN(SELECT ResidentialProo
fId FROM [Link])--Member with invalid residential proof id
SELECT * FROM BL_Customer WHERE AnnualIncomeID NOT IN(SELECT AnnualIncomeID FROM
[Link])--Member with invalid annula income id
SELECT * FROM dbo.BL_Customer WHERE AddressLine1 IS NULL OR AddressLine1=''--Mem
ber with invalid Address id
SELECT * FROM BL_Customer WHERE TahsilID NOT IN(SELECT TahsilID FROM [Link])
OR TahsilID IS NULL--Member with invalid tahsil id
SELECT * FROM BL_Customer WHERE PostOfficeID NOT IN(SELECT PostOfficeID FROM dbo
.POSTOFFICE) OR PostOfficeID IS NULL--Member with invalid Postoffice id
SELECT * FROM BL_Customer WHERE VillageID NOT IN(SELECT VillageID FROM [Link]
ge) OR VillageID IS NULL--Member with invalid village id
SELECT * FROM dbo.BL_Customer WHERE OldMemberShipId NOT IN(SELECT OldMemberShipI
d FROM dbo.BL_ShareDetail) AND IsMember=1--Member with no share a/c details
SELECT * FROM dbo.BL_Customer WHERE OldMemberShipId NOT IN(SELECT distinct ltrim
(rtrim([Link])) from dbo.BL_AccountMaster a cross apply [Link]([Link]
ipId,',') as b where [Link]([Link])='SH') AND IsMember=1--Member w
ith no share a/c
------------update bl_customer set tahsilid=136
select * from bl_customer order by tahsilid desc
SELECT BranchCode,COUNT(OldAccNumber),GLID FROM dbo.BL_AccountMaster WHERE dbo.u
dfGetSchemeType(GLID)='SH'
GROUP BY BranchCode,GLID
SELECT COUNT([Link]),[Link] FROM dbo.BL_AccountMaster a INNER JOIN dbo.B
L_ShareDetail b
ON [Link]=[Link] AND
[Link]=[Link] AND [Link]=[Link]
WHERE [Link]='00064'
GROUP BY [Link]
select * from BL_AccountMaster where BranchCode='00064' AND [Link]
(GLID)='SH'
select * from BL_ShareDetail where BranchCode='00064' AND [Link](G
LID)='SH'
select * from BL_ShareDetail where GlId='833'
select * from BL_LoanMovementRegister where LandPropertyDetail is not null
select COUNT(*) FROM dbo.BL_AccountMaster WHERE BranchCode='00064' AND [Link]
tSchemeType(GLID)='SH'
select COUNT(*) FROM dbo.BL_ShareDetail WHERE BranchCode='00064' --AND [Link]
tSchemeType(GLID)='SH'
SELECT * FROM dbo.BL_ShareDetail WHERE ISNULL(NoOfShare,0)<=0 OR ISNULL(ShareAmo
unt,0)<=0 OR ISNULL(TotalAmount,0)<=0
--SELECT * FROM dbo.BL_Customer WHERE OldMemberShipId='1188M'
SELECT [Link] FROM dbo.BL_LoanDetails a INNER JOIN dbo.BL_LoanMovementR
egister b
ON [Link]=[Link] AND [Link]=[Link] AND [Link]=[Link]
er
INNER JOIN dbo.BL_AccountMaster c ON [Link] = [Link] AND
[Link] = [Link] AND [Link]=[Link]
--count in all 3 queries must be same
SELECT COUNT(LoanAccNumber) FROM dbo.BL_LoanMovementRegister WHERE BranchCode='0
0064'
SELECT COUNT(LoanAccNumber) FROM dbo.BL_LoanDetails WHERE BranchCode='00064'
SELECT COUNT(OldAccNumber) FROM dbo.BL_Accountmaster WHERE [Link](
GLID)='LN' AND BranchCode='00064'
----Validate Nominee
SELECT * FROM dbo.BL_Nominee WHERE FirstName IS NULL OR FirstName=''
SELECT * FROM dbo.BL_Nominee WHERE FatherHusbandName IS NULL OR FatherHusbandNam
e=''
SELECT * FROM dbo.BL_Nominee WHERE GenderId NOT IN(1,2,3)--Nominee with invalid
gender id
SELECT * FROM dbo.BL_Nominee WHERE DOB >= dbo.PCBS_udfGetDate() OR DOB<'01-01-19
00' OR DOB IS NULL--Nominee with invalid DOB
SELECT * FROM dbo.BL_Nominee WHERE OldMemberShipId IS NULL OR OldMemberShipId=''