...
Panel |
---|
select count(distinct(customer_id)) as centers_managed from dim_personnel p where p.display_name = 'XXX' /* get loan office entry that is valid for date*/ and c.customer_status = 'CenterStatus-Active' |
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 where p.display_name = 'XXX' 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') |
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 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 is.
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 where p.personnel_id = 'XXX' and dpaid.action_date _value <= date('2010-01-01') and l.loan_status in ('AccountState-ActiveInGoodStanding', 'AccountState-ActiveInBadStanding') |