Skip to content

Instantly share code, notes, and snippets.

@kareypowell
Created June 19, 2015 05:24
Show Gist options
  • Save kareypowell/0c0548b6d5adffdf2e89 to your computer and use it in GitHub Desktop.
Save kareypowell/0c0548b6d5adffdf2e89 to your computer and use it in GitHub Desktop.
SELECT
branch.NAME AS RegionalCentre, COUNT(loanaccount.ID) AS LoansOutstanding,
SUM(loanaccount.PRINCIPALBALANCE) AS PrincipalBalance,
(
CASE
WHEN DATEDIFF(CURRENT_DATE, loanaccount.LASTSETTOARREARSDATE) > 7 THEN SUM(loanaccount.PRINCIPALBALANCE)
ELSE 0
END
) AS ValueAtRisk
FROM loanaccount
INNER JOIN branch ON loanaccount.ASSIGNEDBRANCHKEY = branch.ENCODEDKEY
WHERE loanaccount.ACCOUNTSTATE IN ("ACTIVE_IN_ARREARS")
GROUP BY RegionalCentre
ORDER BY RegionalCentre ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment