audit_queries.txt

--families without members
select 
	F.FamilyId
from 
	Families f
	OUTER APPLY
	(	
		SELECT TOP 1 FamilyId FROM FamilyMemberAssociation tfma WHERE tfma.FamilyId = f.FamilyID
	) fma
WHERE
	fma.FamilyID IS NULL

--members without family
select 
	m.MemberId
from 
	Members m
	OUTER APPLY
	(	
		SELECT TOP 1 FamilyId FROM FamilyMemberAssociation tfma WHERE tfma.MemberId = m.MemberId
	) fma
WHERE
	fma.FamilyID IS NULL

--families with single member
SELECT
	FamilyId
FROM
	Families f 
	CROSS APPLY (
		SELECT COUNT(tfma.MemberId) MemberCount
		FROM
			FamilyMemberAssociation tfma
		WHERE
			tfma.FamilyId = f.FamilyId
	) fma
WHERE
	fma.MemberCount = 1
	
--members without any default family
select 
	m.memberID
FROM
	members m
	OUTER APPLY
	(	
		SELECT TOP 1 FamilyId FROM FamilyMemberAssociation tfma WHERE tfma.MemberId = m.MemberId AND DefaultFamily = 1 
	) fma
WHERE
	fma.FamilyID IS NULL

--families without head of family	
select 
	F.FamilyId
from 
	Families f
	OUTER APPLY
	(	
		SELECT TOP 1 MemberID FROM Members tm WHERE tm.MemberID = f.HeadOfFamilyID				
	) fma
WHERE
	fma.MemberID IS NULL
	
--matrimony without correct status (alive, adult, single, dob)
SELECT
	m.MemberID
FROM
	Members m
	JOIN Matrimonials mat on mat.memberid = m.memberid
WHERE
	m.Alive = 0
	OR m.MaritalStatusId = 2
	OR m.DOB IS NULL
	OR (m.Gender = 1 AND m.DOB IS NOT NULL AND DATEDIFF(MONTH, m.DOB, GETDATE())/12 < 21) 
	OR (m.Gender = 0 AND m.DOB IS NOT NULL AND DATEDIFF(MONTH, m.DOB, GETDATE())/12 < 18)

--family with members with no relation count more than 1 (top level person don't show relation)
SELECT 
	f.FamilyId
FROM
	Families f
	JOIN FamilyMemberAssociation fma ON fma.FamilyID = f.FamilyID
WHERE
	fma.RelatedId IS NULL
	OR RelationTypeId IS NULL
GROUP BY
	f.FamilyID
HAVING
	COUNT(f.FamilyID) > 1
	
--pending approvals
SELECT 
	FamilyId, MemberId
FROM 
	FamilyMemberAssociation
WHERE
	Approved = 0
	
--eligible for matrimony but no matrimony profile exists
SELECT
	fma.FamilyID,
	m.MemberID
FROM
	Members m
	JOIN FamilyMemberAssociation fma ON fma.MemberID = m.MemberId
	OUTER APPLY (SELECT MemberID from Matrimonials tmat WHERE tmat.MemberID = m.MemberID) mat	
WHERE
	m.Alive = 1
	AND m.MaritalStatusId <> 2
	AND m.DOB IS NOT NULL
	AND ((m.Gender = 1 AND m.DOB IS NOT NULL AND DATEDIFF(MONTH, m.DOB, GETDATE())/12 > 20) OR 
	(m.Gender = 0 AND m.DOB IS NOT NULL AND DATEDIFF(MONTH, m.DOB, GETDATE())/12 > 17))
	AND mat.MemberID IS NULL