QuoteHDR, QuoteDTL, Quote QTY

Epicor ERP 10
21

This is what I have:
select
[QuoteDtl].[PartNum] as [QuoteDtl_PartNum],
[QuoteDtl].[QuoteNum] as [QuoteDtl_QuoteNum],
(max( QuoteHed.DateQuoted )) as [Calculated_LastQTDate],
[QuoteDtl].[QuoteLine] as [QuoteDtl_QuoteLine],
[QuoteDtl].[QuoteComment] as [QuoteDtl_QuoteComment]
from Erp.QuoteDtl as QuoteDtl
inner join Erp.QuoteHed as QuoteHed on
QuoteDtl.Company = QuoteHed.Company
and QuoteDtl.QuoteNum = QuoteHed.QuoteNum
group by [QuoteDtl].[PartNum],
[QuoteDtl].[QuoteNum],
[QuoteDtl].[QuoteLine],
[QuoteDtl].[QuoteComment]

But this is the result:
image

Multiple quote dates/ quote numbers for 1 part number

22

I believe I have what you need. I created two subqueries and then used them in a third top level to get these results.

image
image1826×640 25.2 KB

select
[SubQuery2].[QuoteHed1_DateQuoted] as [QuoteHed1_DateQuoted],
[SubQuery2].[QuoteHed1_QuoteNum] as [QuoteHed1_QuoteNum],
[SubQuery2].[QuoteDtl1_QuoteLine] as [QuoteDtl1_QuoteLine],
[SubQuery2].[QuoteDtl1_PartNum] as [QuoteDtl1_PartNum],
[SubQuery2].[QuoteDtl1_LineDesc] as [QuoteDtl1_LineDesc],
[SubQuery2].[QuoteDtl1_QuoteComment] as [QuoteDtl1_QuoteComment]

from (select
[QuoteDtl].[PartNum] as [QuoteDtl_PartNum],
[QuoteDtl].[LineDesc] as [QuoteDtl_LineDesc],
(max(QuoteHed.DateQuoted)) as [Calculated_LastQDate]

from Erp.QuoteDtl as [QuoteDtl]
inner join Erp.QuoteHed as [QuoteHed] on
QuoteDtl.Company = QuoteHed.Company
and QuoteDtl.QuoteNum = QuoteHed.QuoteNum
group by
[QuoteDtl].[PartNum],
[QuoteDtl].[LineDesc]) as [SubQuery1]
inner join (select
[QuoteHed1].[QuoteNum] as [QuoteHed1_QuoteNum],
[QuoteHed1].[DateQuoted] as [QuoteHed1_DateQuoted],
[QuoteDtl1].[QuoteLine] as [QuoteDtl1_QuoteLine],
[QuoteDtl1].[PartNum] as [QuoteDtl1_PartNum],
[QuoteDtl1].[LineDesc] as [QuoteDtl1_LineDesc],
[QuoteDtl1].[QuoteComment] as [QuoteDtl1_QuoteComment]

from Erp.QuoteHed as [QuoteHed1]
inner join Erp.QuoteDtl as [QuoteDtl1] on
QuoteHed1.Company = QuoteDtl1.Company
and QuoteHed1.QuoteNum = QuoteDtl1.QuoteNum
where (QuoteHed1.Quoted = TRUE)) as [SubQuery2] on
SubQuery1.QuoteDtl_PartNum = SubQuery2.QuoteDtl1_PartNum
and SubQuery1.Calculated_LastQDate = SubQuery2.QuoteHed1_DateQuoted

23

I’ve narrowed this down to JUST the QuoteDtl Table using Max( QuoteDtl.Quote.Num).

It is out and out ignoring the MAX.

24

I might have just got it…preliminary findings are looking promising