BAQ Help - [HDCase]

[BA-Quest (Bill Anthony)]

Greetings, everyone.

I’m working primarily with the HDCase table that contains two specific fields related to date/time:

Erp.HDCase.CreatedDate    {date}
Erp.HDCase.CreatedTime    {int}

I’m sure Epicor has a perfectly sound strategic reason to use (Julian?) values for the time field, but that’s throwing another level of complexity into the mixing bowl. I can use something like:

MAX(CONVERT(varchar, DATEADD(ms, (HDCase.CreatedTime * 1000), 0), 108))

…perhaps, to pull that back in as a legitimate time, but for what I’m trying to do, date and time really needs to be evaluated as, well,… date and time.

What I’m hoping to accomplish is to isolate the most recent case for each distinct ProjectID (also included in the HDCase table -----> HDCase.ProjectID). So, for as many distinct Projects that exist either in the HDCase table, or in the Project table itself, I need to return the latest case entered/opened by date.

Consider that there could be more than one case opened on any given date. That’s where either the [HDCase.CreatedTime], or the [HDCase.HDCaseNum] needs to come into play to return the lasted case occurring by time for the latest case day, if there exists more than one case for a specific date.

Here’s how I think I need to tackle this: Create “SubQuery1” to isolate the latest case date for each Project by MAX(HDCase.CreatedDate), grouping by HDCase.ProjectID at the very least. After that, I would need to create a “SubQuery2” to isolate the MAX(HDCase.CreatedTime) for each “maxed created date”, grouping by HDCase.ProjectID, then by MAX(HDCase.CreatedDate).

From what I understand, aggregates cannot be used in GROUP BY evaluations - and that’s where I am drawing a bit of a blank concerning what I really should be doing instead.

Would anyone wish to realign my thinking and point me in the right direction. Seems like a pretty common thing to do, but I’m not thinking it through very well.

[Mark_Wonsil]

The time is number of seconds since midnight. Divide that number by 86400 (60 x 60 x 24) to convert to the Microsoft version of time which is a fraction of a day.

[dhewi (Daryl Hewison)]

There are many tables in Epicor which work this way, and we always treat them the same way: a calculated field of form

DATEADD(s,table.TimeField,CAST(table.DateField as DateTime))

Then if you use that as a subquery, the query containing it can use it as an aggregate with no problem, so you can, for example, get the latest without any further complication.

[BA-Quest (Bill Anthony)]

Daryl:

I’ve tried banging on this for a while this morning and I’m not getting anywhere. Might you be able to explain this a different way (I think I may need a bit more details)?

[dhewi (Daryl Hewison)]

Ok, excuse me replying on my phone. For that reason I may misremember the exact detail, but hopefully you’ll get the gist.

If you add a calculated field of type DateTime and set it to DATEADD(s,HDCase.CreateTime,CAST(HDCase.CreateDate AS DateTime) then you should get your combined date and time in that column.

If you do that in a subquery, then including that subquery in a top level query will allow you to aggregate it. (Actually it is possible to do it all in one, but this way is easier to follow for anyone else looking at the BAQ).

[ckrusen (Calvin Krusen)]

@BA-Quest - Are you sure they are in Julian? Like @Mark_Wonsil said, Epicor stores times as an integer in the number of seconds after midnight.

Browse the data in that field and I bet you see nothing greater than 86399. If that was milliseconds, that would equate to just 86 seconds after midnight, or 12:01:26 AM

edit

I missed where you were multiplying by 1000.

[ckrusen (Calvin Krusen)]

The following returns a datetime by combining the ChangedDate(Date) & ChangedTime(int)

[ckrusen (Calvin Krusen)]

FWIW - The following don’t work

Exression:

DATEADD(s,OrderDtl.ChangeTime,OrderDtl.ChangeDate)

Yields:

Severity: Error, Table: , Field: , RowID: , Text: The datepart second is not supported by date function dateadd for data type date.

Expression:

convert(datetime,OrderDtl.ChangeDate) +  DATEADD(s,0,OrderDtl.ChangeTime)

Complies and runs, but sees ChangeTime as a date and adds 0 seconds to it

Edit

That first expression will work if the ChangeDate is first converted to DateTime

DATEADD(s,OrderDtl.ChangeTime,convert(datetime,OrderDtl.ChangeDate))

[BA-Quest (Bill Anthony)]

Daryl:

So I decided to remove all of the complexity of my existing BAQ and simply start fresh in a step-by-step manner.

I have two tables: Project, and HDCase.

I am using an outer join here because I want all of the “projects” whether or not they have ever had a case opened on them.

I know that there are roughly 270 project records and over 4000 cases.

I want to show the latest case record for each of the 270 projects. Therefore, I should be returning only 270 records (as many records as there are distinct projects).

My display fields in this example are simply [Project.ProjectID] and [Calculated_CreatedDate], which is defined as:

As is, the BAQ returns:

… but I’m trying to isolate the distinct project records with their latest associated created case record.

Going back to your example:

"Then if you use that as a subquery, the query containing it can use it as an aggregate with no problem"

I am interpreting the “that” as the expression you provided, which I just used to create the calculated field. So, there is a bit of confusion for me between using the calculated field as a subquery, or the actual expression as a subquery - and - since you’ve said “Subquery” and not “Subquery Criteria”… I am guessing that I’ll need to build out a separate query in the BAQ designer.

Additional confusion sets in when I realize that, if my strategy is to isolate the latest, or MAX(), created date for a collection of case records on each project record, then I’ll need to “recalculate” the expression you provided again, because the new subquery cannot “see” calculated fields applied to a table. It can only “see” fields that exist in a table, or another subquery - which includes calculated fields that might be present in subquery, which I don’t have at this stage in the process.

Mind you, this isn’t making much sense in my mind at this point, but I’ll run with it.

Here is the subquery thus far:

I’ll need to group on the project ID to make sure that all cases are grouped together per distinct project record.

So, now I need to go back to the BAQ Designer to tie in the new subquery…

… and run a quick execute to check for integrity:

Column 'Erp.HDCase.CreatedTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column 'Erp.HDCase.CreatedDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Well, I certainly don’t want to be grouping by dates or times, else I’ll be repeating my results for every possible date and time entered into the database.

Maybe it needs to be an aggregate here (as noted by the error message) - and since I have yet to define my MAX(), then maybe this is what is needed in this step.

So I apply the MAX() aggregate to the calculated CreatedDate in the subquery, but I still return the 4710 records. So, I remove the aggregate there and place it on the original calculated field in the top level query. Results are the same, but this time, it won’t even allow me to save the calculated field:

Ok, then. Maybe I misunderstood and was supposed to construct the aggregate as a “Subquery Criteria” instead of a subquery:

Nope. That’s not right either.

---

I’ll continue to poke at it, but obviously, I’m fairly confused.

[BA-Quest (Bill Anthony)]

Calvin:

Yeah - I had no idea what format that was being used there. I assumed Julian, which is why I used a question-mark in my original post:

I see that @dhewi’s expression works well at pulling both date and time values in together as a {datetime} field, which can then be evaluated in an aggregate function.

[dhewi (Daryl Hewison)]

I think (hope) I’d be more helpful if I was at my computer and could do more copying and pasting from Epicor! But in the meantime…

Your SubQuery will work if you group by Company and HDCase.ProjectID as long as you then put MAX() around the DATEADD expression in the calculated field. My original approach assumed an additional containing SubQuery which did the same job, which I find users coming later can read more easily. But I think your error is because you haven’t grouped and aggregated in the same query.

I believe SubQuery criteria are a red herring unless I’ve misunderstood in some way. An aggregated SubQuery should do the job.

[zwilli526 (Zack Williams)]

I’m not sure you have to get the date and time involved here. Wouldn’t returning MAX(HDCase.HDCaseNum) accomplish you end goal since the last case created should have the largest case number. This would be an inner sub-query grouped on Company and Project ID.

[BA-Quest (Bill Anthony)]

I played with this a bit more and got a bit further, but still don’t understand this fully.

Placing an aggregate on the DATEADD() expression seemed to be a big problem for me. So, I went back to try and illustrate that I could build a subquery with a simple aggregate, like, SUM() (I realize this is extremely silly to do, but in my mind, it doesn’t make sense that I can do this with one simple aggregate function, but not another):

Then I wanted to illustrate that I could do a simple aggregate, such as, MAX() on the CreatedDate field, like so:

… and this gives me the record count I expect, as well as data accuracy, because I had created a new case for Project #100-0182 last week and the “max CreatedDate” does show that correct date above.

Okay. Great. Now let’s go back and insert the expression that @dhewi showed me earlier inside of the MAX() aggregate:

I can’t explain it, but it works… and I thought I had already tried this exact approach about 10 times before; all with errors telling me that I can’t place either the [Erp.HDCase.CreatedDate] or [Erp.HDCase.CreatedTime] field “in the select list” (which they weren’t; they were part of a calculated field) because there were not “contained in either an aggregate function or the GROUP BY clause” (which, they were, both previously inside of the MAX() aggregate function).

So - I don’t understand any of that… nor do I understand why it’s suddenly performing in the manner I was expecting when I thought I did this exact same thing many times over already without success.

---

So, now that I have this “working” (until it doesn’t again), I need to add some extra data from the HDCase table.

Now, the subquery is only in place to define the “latest created case for each project”. So, I shouldn’t be adding data to the subquery (I think). I should be going back to the “top level” query to pull in the HDCase table a second time and join it to the subquery - then pulling in my extra fields directly from the HDCase table:

So, I merely pull in one arbitrary field from HDCase and…

… which leads me to believe that I’m not grouping on something that I should.

I’m setting this aside for now in a futile effort to preserve my sanity. I’ll try again tomorrow.

[BA-Quest (Bill Anthony)]

After looking at this again quickly this morning, I noticed that I had forgotten to thoroughly define my join between the subquery and the HDCase table (the one that I pulled back in to grab the values for the current case). My original join merely contained links on Company and ProjectID - but I also need to “filter” that join by the “latest case date” (by linking the calculated MAX() created date to the HDCase.CreatedDate).

That join actually didn’t work (initially) because the calculated MAX() aggregate operated on @dhewi’s suggestion to use the DATEADD() function (which was the perfect suggestion to use for converting the CreatedTime value and adding that to CreatedDate to get the full {datetime} value). In the case of this particular join, however, the calculated {datetime} value in the subquery was trying to join on the CreatedDate {date} value in the HDCase table, which returned no records.

So, I took @zwilli526’s suggestion to use the HDCaseNum value instead, because it is assumed that the incremental nature of that ID will always provide you the highest number value as the “most current” record. Sure, it’s not predicated on dates, but the notion of “most current” should hold true.

I added HDCaseNum as part of the join like this:

… and the returned records are precisely what I would expect – latest case for each project along with detailed values on those cases.

---

I really have to remember that if something isn’t making much sense, I need to go back to fundamental principles and evaluate those first.

Everyone’s input was incredibly valuable here and I appreciate your time and patience, as always.