Page Comparison

...

Panel

select count(distinct(customer_id)) as centers_managed

from dim_personnel p
join dim_customer c on c.loan_officer_key = p.personnel_key

where p.display_name = 'XXX' /* get loan office entry that is valid for date*/
and p.valid_from <= date('2010-01-01')
and p.valid_to > date('2010-01-01')

and c.customer_status = 'CenterStatus-Active'
and c.valid_from <= date('2010-01-01') /* get center entry that is valid for date*/
and c.valid_to > date('2010-01-01')

b) as of 2010-01-01, how many client clients with active loans did loan officer XXX manage

Panel

select count(distinct(c.customer_id)) as clients_with_loans

from dim_personnel p
join dim_customer c on c.loan_officer_key = p.personnel_key
join dim_loan l on l.customer_key = c.customer_key

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

and c.customer_level_id = 1 /* client... without this groups would get picked up as well*/

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')

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 look looks at active loans managed by a loan office at a certain point in time... which is okay if that's what the question really was.

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_date d on d.date_key = paid.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 d.date_value <= date('2010-01-01')
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')

Versions Compared

Old Version 9

New Version 10

Key