Monday, March 12, 2012

Last and Previous Query

I'm kindof at a lose for how to ask my database to show me the last payment amount and payment date for clients and also show me the previous payment amount and payment date for clients based on a date range.

Could anyone offer any pointers or examples?

Thank you very much.

We could use a very simple table and field setup like this just so you could show an example:

tblClient
Client_ID

tblOrder
Order_ID
Client_ID

tblOrderPayments
Payment_ID
Order_ID
Client_ID
PaymentDate
PaymentAmountLookup aggregates such as MAX and check out TOP.|||I looked them up but the only thing is that most examples are of there simplist use. So getting the max is easy but then getting the payment just prior to that is another thing all together. If anyone can offer any suggestions, I thank you,|||Well one way, and they'll be others, would be to get the latest set and store them in a table var. Then run the same query again except you exclude those you found in the first. Now you can do that all in 1 batch but the syntax can get a bit confusing. I like table vars, sure they're not optimal but they do make the SQL nice and easy. Once you get it working you can always optimise out the table var.

No comments:

Post a Comment