Having trouble creating valid query

[alwayslrN]

I'm mentally stuck. I have the following:

select b.loan, b.int_coll_p,
(select isnull(sum(r.interest),0) - isnull(p2.interest,0)
 from nyl_sfp_poolmast p1
left outer join nyl_sfp_remit r on
   r.pool = p1.pool
left outer join (select pool,accrued_int as interest
                  from nyl_sfp_poolmast p
                  where p.pool = b.loan and
                        p.closed_date between DATEADD(dd, 01-1, DATEADD(mm, 01-1, DATEADD(yyyy, year(b.accounting_date)-1900, 0))) and b.accounting_date) as p2 on
                  p1.pool = p2.pool 
 where p1.pool = b.loan and
       r.pool = b.loan and
       r.lockbox_acctg_date between DATEADD(dd, 31-1, DATEADD(mm, 01-1, DATEADD(yyyy, year(b.accounting_date)-1900, 0))) and b.accounting_date group by p2.interest)
from  balhist b
where b.accounting_date = '2007-01-31' and
      b.loan = '333333'

What this does not five is the value for p2.interest because there is no record in nyl_sfp_remit r. I tried playing around with the above, but ended up with the same results.

When I do the following:

select b.loan, b.int_coll_p,
(select isnull(remit.interest,0) - isnull(accrued_int,0)
 from nyl_sfp_poolmast p
 left outer join (select r.pool,sum(interest) as interest
                  from nyl_sfp_remit r
                  where r.pool = b.loan and
                        r.lockbox_acctg_date between DATEADD(dd, 31-1, DATEADD(mm, 01-1, DATEADD(yyyy, year(b.accounting_date)-1900, 0))) and b.accounting_date group by r.pool) as remit on
 remit.pool = p.pool 
 where p.pool = b.loan and
       p.closed_date between DATEADD(dd, 01-1, DATEADD(mm, 01-1, DATEADD(yyyy, year(b.accounting_date)-1900, 0))) and b.accounting_date)

from  balhist b
where b.accounting_date = '2007-01-31' and
      b.loan = '3333333'

I get a value for p2.interest, however other loans are not correct.

Can some one please steer me in the right direction?

 

[fuzzyocelot]

If it's not too much trouble, could you please provide some sample dummy data for an idea of what the data is like?

Thanks!