I have a table of projects (many thousands) and a related table for each project of data records (more than 100k). For each project I need to summarize, in real time, the last X data entries. Since the data entries are made aperiodically, I do not have a firm date anchor. One project might have a data entry today and I count back X entries, but another might have data entry from a week ago and I need to count back from that point.
I have it almost working with some simple self joins in the data table. The current flaw is I calculate the Max date for a given project's data records via a self join to use as my reference point to process the relevant data records. By doing so the result can't be stored because based on a self join. If I enter Max date manually or by script everything is fine, but that doesn't get me where I need to be, that is non script based.
Any strategy suggestions to get me to a real time calculated solution would be most welcomed.