---
You are grouping on the paymentDate AND selecting it. This will return
all paymentDates instead of the last.
I didn't have to do something like this yet, but I recall a thread with
some answers not that long ago. It isn't very simple (if you don't want
to make a query for each account).
Pascal
> -----Original Message-----
> From: Tony Weeg [mailto:[EMAIL PROTECTED]
> Sent: dinsdag 15 juni 2004 6:40
> To: CF-Talk
> Subject: sql question...
>
> I have these tables:
>
> 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]
