Last Quoted, Last Quote price, Last Quote number

[jpol (Judy Poling)]

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!

[John_Mitchell]

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:

LAST_VALUE(QuoteDtl.QuoteNum) OVER (PARTITION BY QuoteDtl.PartNum ORDER BY 
QuoteDtl.QuoteNum ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

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:
https://www.itprotoday.com/microsoft-sql-server/how-use-microsoft-sql-server-2012s-window-functions-part-1

[jpol (Judy Poling)]

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!

[Adam (Adam Szymanski)]

Are you trying to get this for each customer?

[jpol (Judy Poling)]

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

[Adam (Adam Szymanski)]

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.