Hello everyone
Im trying to get the recent date of the part purchased along with the quantity. Im able to get the recent date of the part purchased but when i add the quantity in the Display column in BAQ i do not get the exact data as expacted. Expected Data should be 3 rows but im getting 13 rows after adding quantity. I have attached the images for reference
Is your Join backwards? Try using the SysRowID of the most recent date for the part to link back to the Qty purchased.
Hi @CSmith thank you for the quick response. Im not sure what is Join backwards is about. But below is the query of how it looks like
[QuoteMtl].[QuoteNum] as [QuoteMtl_QuoteNum],
[QuoteMtl].[PartNum] as [QuoteMtl_PartNum],
(max(RcvDtl.ReceiptDate)) as [Calculated_LastPurchased],
[RcvDtl].[OurQty] as [RcvDtl_OurQty]
from Erp.QuoteMtl as QuoteMtl
inner join Erp.RcvDtl as RcvDtl on
QuoteMtl.VendorNum = RcvDtl.VendorNum
and QuoteMtl.PartNum = RcvDtl.PartNum
and ( RcvDtl.PartNum = QuoteMtl.PartNum and RcvDtl.VendorNum = QuoteMtl.VendorNum )
inner join Erp.Vendor as Vendor on
RcvDtl.Company = Vendor.Company
and RcvDtl.VendorNum = Vendor.VendorNum
and ( Vendor.VendorNum = QuoteMtl.VendorNum )
inner join Erp.VendCnt as VendCnt on
Vendor.Company = VendCnt.Company
and Vendor.VendorNum = VendCnt.VendorNum
and Vendor.PrimPCon = VendCnt.ConNum
and ( VendCnt.VendorNum = Vendor.VendorNum )
where (QuoteMtl.QuoteNum = 22738)
group by [QuoteMtl].[QuoteNum],
[QuoteMtl].[PartNum],
[RcvDtl].[OurQty]
order by QuoteMtl.PartNum
Ahhh, I see well you are getting what you asked the system for:
The Max with Group For each ( QuoteNum, PartNum, Qty ) it’s the last one messing up your results for you. You need the Qty on the outside so you will need to move the MAX part into a subquery or use a windowing function to get your max date.
Think of it this way ONLY if you received in the same qty amount each date you would then only get one record for that set of data, but for the other items you will get multiples because of the grouping on amount.
Hope that make sense, if you need further assistance let me know 
I tried using innerSubQuery to calculate the max(RcvDtl.field_name) and then linked with Top level query using Subquery criteria. Im getting only single row in return. When i added quantity field in the innerSubQuery again and group it i get 8 rows in return. I can understand why its happening but not getting solution to get the exact 3 rows.
I have added a screenshot and highlighted the field which i actually want
You showing the results is only 1/2 of the picture. Could you provide the Query so I can see what you are asking the system to provide as a result set?
Here is the query
[RcvDtl].[PartNum] as [RcvDtl_PartNum],
[RcvDtl].[ReceiptDate] as [RcvDtl_ReceiptDate],
[RcvDtl].[OurQty] as [RcvDtl_OurQty]
from Erp.QuoteMtl as QuoteMtl
inner join Erp.Vendor as Vendor on
QuoteMtl.Company = Vendor.Company
and QuoteMtl.VendorNum = Vendor.VendorNum
and ( Vendor.VendorNum = QuoteMtl.VendorNum )
inner join Erp.VendCnt as VendCnt on
Vendor.Company = VendCnt.Company
and Vendor.VendorNum = VendCnt.VendorNum
and Vendor.PrimPCon = VendCnt.ConNum
and ( VendCnt.VendorNum = Vendor.VendorNum )
inner join Erp.RcvDtl as RcvDtl on
Vendor.Company = RcvDtl.Company
and Vendor.VendorNum = RcvDtl.VendorNum
and ( RcvDtl.VendorNum = QuoteMtl.VendorNum and RcvDtl.PartNum = QuoteMtl.PartNum )
where (QuoteMtl.QuoteNum = 22738)
and (RcvDtl.ReceiptDate = ANY (select Calculated_LastPurchased from ((select
(max(RcvDtl1.ReceiptDate)) as [Calculated_LastPurchased]
from Erp.RcvDtl as RcvDtl1)) as PartQtyQuery))
Thank you, but there is something missing here or this is not the right query. Also, please refer to this post Code Syntax Highlighting in Posts when providing code like output.
From just a cursory glance you are not isolating your result set with this:
Should be looking for and saving the last date for the part num to link back to the part number result based on the query you provided.
Yes i was missing to link back to the part number.
It is working now.
Thank you so much
Good to hear! Very welcome