Incremental Count

[Celeste025]

I have a table that contains account numbers (ACCT), dates (TSD) that the person holding the account visited and then the amount of money spent (ATT).

I want to bring back the most recent 5 visits for each patron but when I do count of account or trip date, it just assigns each 1. I need it to find the most recent trip and assign it a 1 and then increment the next trips +1 so then I can say where count <= 5.

I have tried:

SELECT
ACCT,
AVG( ATT ) AS "AVGATT"
FROM
DTWDTA.DWFLTH1 a
WHERE
5 >(
SELECT
COUNT( TSD )
FROM
DTWDTA.DWFLTH1 b
WHERE
b.ACCT = b.ACCT
AND DATE( a.TSD, HYF ) < DATE( b.TSD, HYF )
)
GROUP BY
THACCT

And it works... kind of... its so intensive that it brings down my entire system - which makes me very unpopular.

What Im trying to do is run the ACCT, TSD, ATT & COUNT to a library file and then pull from that file COUNT <=5. I just dont know how to do the incremental count.

Thanks!

 

[amrita418]

How about something like this?

select    acct, 
          dates, 
          att
from      DTWDTA.DWFLTH1 a
where     dates in (select    top 5 
                              b.dates 
                    from      DTWDTA.DWFLTH1 b
                    where     a.acct = b.acct
                    order by  b.acct, 
                              b.dates desc)

PS: Code not Tested

Best Regards,
AA

 

[Celeste025]

I think this solution will give me the same problem as before.. I am allotted 30,000 seconds (8 hours) processing time on the server and when I start joining a table to itself, it comes back telling me it will take in the neighborhood of 22 years...

Im hoping for a solution that will do something other than link the table to itself for processing reasons.

Thanks!!