Celeste025
Technical User
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!
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!