Last Quoted, Last Quote price, Last Quote number

Good morning…at least here in Ohio!

I am not trying to “beat a dead horse” but I have tried everything listed here in EpicHelp and I cannot figure out what I’m doing wrong.

I am trying to pull the above mentioned data. I consistently get all quotes, their quote dates and price.

I’ve tried using subqueries and aggregate, and using Distinct and all the other iterations on the subqueries but to no avail.

This should be simple, but it looks like I’ve made it more difficult that it should be.

Any thoughts - Thanks!

I’m partial to Window Functions because you don’t have to mess with Sub-Queries.

I’m not sure what you are joining to the Quotes but assuming that you want the Last Quote for a Part you would join the tables and create a calculated field. The formula would then be something like:


Here is a great link explaining the basics of Window Functions from a great resource: Window Function Examples for SQL Server - Brent Ozar Unlimited®

And the absolute expert Itzik Ben-Gan:

Thank you for responding so quickly, unfortunately I know very little SQL. Do you have information using Epicor’s BAQ Designer to accomplish this?

Thank you!

Are you trying to get this for each customer?

For each part - I’m using the following tables:

There are probably other ways to do this, but this is how I would. You need to have three queries: 1) your top level, “All Parts”, 2) an innersubquery called “MaxQuoteNum” and 3) another innersubquery called “QuoteDetails”.

1 & 2 are connected via PartNum
2 & 3 are connected via MaxQuoteNum and QuoteNum (see below)

The MaxQuoteNum query is simply the QuoteDtl table, grouped by PartNum. Then, a calculated field called “MaxQuoteNum” is added.

For the third query, you simply add QuoteDtl again, but this time added in QuoteHed (to get the QuoteDate)

Finally, back on the TopLevel query you’ll need to display all of these fields from the subqueries.

1 Like