BAQ : Most recent InvoiceDate

[mlp]

Hello~!

I am making a BAQ to look at our vendors. I want it to just give me the most recent payment/InvoiceDate, instead of all the PO’s ever made under that vendor.

I currently have Vendor joined to APInvHead.

How can I go about this?

[JMPCONJ (John Magee)]

Group by vendor and take Max(InvoiceDate)…BAQ “SQL” should look like below

---

select
[Vendor].[Company] as [Vendor_Company],
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[Name] as [Vendor_Name],
(Max(InvoiceDate)) as [Calculated_MaxInvcDate]

from Erp.APInvHed as [APInvHed]
inner join Erp.Vendor as [Vendor] on
APInvHed.Company = Vendor.Company
and APInvHed.VendorNum = Vendor.VendorNum
group by
[Vendor].[Company],
[Vendor].[VendorID],
[Vendor].[Name]

[Mark_Wonsil]

Another option that works if your fields aren’t ordered by date, is to use SQL Window Functions:

SQL Window Function in BAQ

I need to be able to return the latest completed job per part number from the jobhead table. I’ve got this working with a BAQ that has a top level BAQ and one inner subquery BAQ, but it returns duplicates if the “latest completed date” is the same on multiple job records. From my research via Google I should be able to instead use a “window function” within my sql statement. However, I don’t know how to accomplish this in a BAQ format. I’ve included a link below to the example that I used. I’ve …

[mlp]

Sorry, stupid question… How? The only Group By I find is when I analyze/test it.

[dcamlin (David Camlin)]

In your display columns:

[JMPCONJ (John Magee)]

What @dcamlin said…I had four fields in my test (company, vendorID, name) and checked them all off…and added a calculated field to get max(InvoiceDate).

And not a stupid question either - especially if you’re new to BAQs etc.

[mlp]

Thank you!

I ran into a SQL error, though, when I test it.

select 
	[Vendor].[VendorID] as [Vendor_VendorID],
	[Vendor].[VendorNum] as [Vendor_VendorNum],
	[Vendor].[Name] as [Vendor_Name],
	[Vendor].[Inactive] as [Vendor_Inactive],
	(Max(InvoiceDate)) as [Calculated_MaxInvcDate],
	[APInvHed].[Company] as [APInvHed_Company]
from Erp.APInvHed as APInvHed
inner join Erp.Vendor as Vendor on 
	APInvHed.Company = Vendor.Company
	and APInvHed.VendorNum = Vendor.VendorNum
group by [Vendor].[VendorID],
	[Vendor].[Name],
	[APInvHed].[Company]

Severity: Error, Text: Bad SQL statement.
Review the server event logs for details.
Query returned 0 row(s).
Query has no more records to return.
Query execution total time: 15.0477 ms.

[JMPCONJ (John Magee)]

Add Vendor.Inactive and Vendor.VendorNum to your group-by selections. Whatever fields you select for display from the Vendor table need to be flagged as group-by.

[dcamlin (David Camlin)]

Here’s a tip…

When you have a calculated field, always click the “Check Syntax” button in the Calc Field Editor.

It doesn’t just look at the syntax of the Field… it looks at the whole query. So, instead of getting a “Bad SQL statement” error, it’ll give you more detail. Like, “Vendor.Inactive must be part of the group-by statement”… although, typically worded less useful than that

In cases of aggregate functions, if you use one (like sum, min, max, etc.)… you generally have to add all non-aggregate columns to the group by. But again, if you “Check Syntax” any errors that pop up will hopefully lead you in that direction.

[JMPCONJ (John Magee)]

[mlp]

Thank you both, it worked! I added all lines to Group By except the calculated field, and got the results I was looking for.