BAQ Earliest Date

Kinetic ERP
1

Is there a way to find the earliest date across multiple tables in a BAQ?

I’m querying historical data which I pushed into a UD Table. So, forget native Epicor field names…

For a given job, I have:

  • Material Transactions (Table A)
  • Labor Transactions (Table B)
  • Subcontract Transaction (Table C)
  • Misc Transactions (Table D)

Each record in these tables has a transaction date.

I want to create a calculated field that populates with the earliest transaction date against a given Job.

I already have these tables in their own subqueries with calculated fields to determine the min(date) of each table. So, I have the first Labor Date, first Material Date, etc.

If I only had two tables, I can simply do a “case when dateA<dateB”.

But since I have more than two tables, do I need to create an insane “case when” statement to cover all scenarios… or… is there maybe a more simplified approach?

SQL has a “Least” function and a “Cross Apply”… but neither appear to be supported in Epicor’s BAQ’s.

2

Cross apply is simply 2 tables with no joins.

Your best bet is probably going to union those tables, then use calculated fields on the union afterwords.

3

Thanks @Banderson.

Is there a proper syntax for ISNULL() when dealing with dates?

4

Date fields really shouldn’t be null. So I would exclude any records where the dates are null.

Where are you getting null dates?

5

Historical / Legacy data… agreed they shouldn’t be null… but alas, there are a few.

I suppose I could write in a dummy date if that were to occur.

6

Yeah, so

isnull(field, resultIfNull)

You should be able to enter in something to the result that doesn’t mess with the results. Like today

isnull(field, GetDate())

Should handle what you need.

I don’t know what your data is, or what the end goal is, so you’ll have to adjust as necessary.

Also, I don’t know if you are grouping everything, or doing subqueries with a rejoin, but if it’s the latter, you can use a windowing function to get the earliest date without special subqueries.

Something like this.

min(isnull(field, GetDate())) over (partition by whateverFieldsYouNeedToPartitionBy)
7

It also occurs when a top level job assembly doesn’t have a certain transaction type.

For example, Assy Level 0 doesn’t have any “Labor” transactions… but the subassemblies do.

But if I just do a simple dateA < dateB… and dateA doesn’t exist… I get no result.

8

You could take a slightly different approach.

I think the subcon and labor table could be 1 subquery by using the JobOper.FirstLabor (or something like - sorry on phone)

Then in each of your subquerys :

1.Filter the table JobNum as a expression eg.JobHead.JobNum.

  1. Sort by date ascending and add the field to your subquery

  2. Set the subquery to Top and the rows to 1.

  3. In your top level query at a calc field with the syntax of your subquery wrapped in braces {Subquery1}

9

…only if you want to return today’s date if all of the date values are in the future, or if all values are null. MIN() only returns the smallest non-null value.

Otherwise, yep, window function is the way to go.

10

I assumed he was getting errors, which is why I put that in there, and since it was historical data, it would all be in the past. But yeah, if the isnull isn’t needed, then even simpler.