accts | that has debtorNumber
debtors | that has a debtorNumber id field
payments | that has an acctId column to relate to accts, which relates
down to debtors
paymentPlans | that has an id, and a frequency (how often, int 30 - 60 -
90 days), and an acctId
and I want to get the last payment, the date, and the amount from the
payments table, that relate to payment plans, through the planId in the
payments table, and relating on up as described above.
now, im getting multiple records for 1 account, is there no way to do
this what im looking for in sql? might it take some cf logic?
thanks if you can help...
select
distinct a.acctNo,
max(p.paymentDate) as lastPaymentDate,
d.fname,
d.lname,
p.paymentDate,
pp.frequency,
a.acctNo,
p.amount,
acctNo
from
debtors d
inner join
accts a on a.debtorNumber = d.debtorNumber
left outer join
payments p on p.acctId = a.acctNo
inner join
paymentPlans pp on pp.acctId = a.acctNo
where
dateDiff(dd,dateAdd(d,-pp.frequency,getDate()),p.paymentDate) <=
34
group by
a.acctNo,
d.fname,
d.lname,
p.paymentDate,
pp.frequency,
a.acctNo,
p.amount
order by
p.paymentDate
asc
....tony
r e v o l u t i o n w e b d e s i g n
[EMAIL PROTECTED]
www.revolutionwebdesign.com
Visit http://www.antiwrap.com the next time you want to send a link to a
friend.
its only looks good to those who can see bad as well
-anonymous
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
