Versions Compared

Old Version 10

changes.mady.by.user johnwoodlock

Saved on

compared with

New Version 11

changes.mady.by.user johnwoodlock

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

c) as of 2010-01-01, what were the repayments on active loans that loan officer XXX dealt with.  Here's where I struggle a bit.  It's a little struggle technically but really its a bigger struggle to make sure you are asking the right question.  The query below only looks at non-adjusted repayments for active loans managed by a loan office at a certain point in time... which is okay if that's what the question really wasis.

Panel

select ifnull(sum(principal_amount),0.0) as principal_paid, ifnull(sum(interest_amount),0.0) as interest_paid

from fact_loan_repayments paid
join dim_loan l on l.loan_account_key = paid.loan_account_key
join dim_personnel p on p.personnel_key = paid.loan_officer_key
join dim_account_date d action aa on daa.dateaccount_action_key = paid.account_action_date_key

where p.personnel_id = 'XXX'
and p.valid_from <= date('2010-01-01')
and p.valid_to > date('2010-01-01')

and dpaid.action_date _value  <= date('2010-01-01')
and aa.account_action_id = 1
and paid.adjusted is false

and l.loan_status in ('AccountState-ActiveInGoodStanding', 'AccountState-ActiveInBadStanding')
and l.valid_from <= date('2010-01-01')
and l.valid_to > date('2010-01-01')