Running Calendar in BAQ

Epicor ERP 10
,
1

Good morning Epicor Community!

I wanted to see if anyone has some ideas for creating a standalone calendar to be used in BAQ’s.
The purpose would be to have this calendar as a point of reference. Some tables used in my queries do not have daily activity, and creating running totals based off of every day of the month cannot be achieved in these cases. There have been a few occasions where this would have been useful, but my latest task calls for this type of solution.

For example, I am trying to determine daily, monthly, and yearly machine utilization in the LaborDtl table, but this machine does not get used every day. And my boss wants to see a running daily, monthly, and yearly snapshot of the utilization (Earned hours divided by 20 hours per day), regardless of if it was used that day.

Right now, here’s my calculation to get the total hours each day, but it only totals when there is a clock in date -
sum(20) over (partition by year, month order by LaborDtl.ClockInDate)

It would be nice if I could sum 20 hours each day of every month to calculate the earned hours against, but don’t have that point of reference.

I’m not sure if bringing in a UD table containing each day of the month for the next x amount of years would be the best option, or if this can be achieved as a calculated field. Ultimately, I’m not sure what the best solution is, but I’m sure someone would have some ideas!

As always, I appreciate any ideas or suggestions you might have!

2

I have an idea. It uses the ZDataField table as a source for essentially infinite records. (Over 100,000 anyway). Check this out, super simple:

select 
	(ROW_NUMBER() OVER(ORDER BY ZDataField.SysRevID ASC)) as [Calculated_Row_Num],
	(dateadd(day, Row_Num, '1/1/2000')) as [Calculated_Date]
from Ice.ZDataField as ZDataField

You can set that start date to anything you want depending on how early you want the dates to go.

2 Likes
3

That is super clever Nate!
Now I just need to figure out how to mesh it together. This new field and my existing data aren’t playing well together.

image
image1804×718 155 KB

I have my LaborDtl data in one subquery, and the ZDateField table containing the calendar in another subquery. Trying to join these two subqueries together isn’t so straightforward. I would like for the Calendar field to be the new point of reference, and to show LaborDtl data when the ClockInDate aligns with one any given calendar dates.

Any ideas how I could include the calendar field in a way that’s compatible with my existing data from the LaborDtl table?

4

I think this should get you there. I made two subqueries. One for the calendar and one for the labor. I joined them on a top level query on just the date field. Seems to return a lot of data. You may need to add more fields and filters to get the data you need.

select 
	[SubQuery1].[Calculated_Date] as [Calculated_Date],
	[SubQuery2].[LaborDtl_ClockInDate] as [LaborDtl_ClockInDate],
	[SubQuery2].[LaborDtl_EmployeeNum] as [LaborDtl_EmployeeNum],
	[SubQuery2].[LaborDtl_JobNum] as [LaborDtl_JobNum],
	[SubQuery2].[LaborDtl_AssemblySeq] as [LaborDtl_AssemblySeq],
	[SubQuery2].[LaborDtl_OprSeq] as [LaborDtl_OprSeq],
	[SubQuery2].[LaborDtl_LaborHrs] as [LaborDtl_LaborHrs],
	[SubQuery2].[LaborDtl_LaborQty] as [LaborDtl_LaborQty]
from  (select 
	(ROW_NUMBER() OVER(ORDER BY ZDataField.SysRevID ASC)) as [Calculated_Row_Num],
	(dateadd(day, Row_Num, '1/1/2000')) as [Calculated_Date]
from Ice.ZDataField as ZDataField)  as SubQuery1
inner join  (select 
	[LaborDtl].[ClockInDate] as [LaborDtl_ClockInDate],
	[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
	[LaborDtl].[JobNum] as [LaborDtl_JobNum],
	[LaborDtl].[AssemblySeq] as [LaborDtl_AssemblySeq],
	[LaborDtl].[OprSeq] as [LaborDtl_OprSeq],
	[LaborDtl].[LaborHrs] as [LaborDtl_LaborHrs],
	[LaborDtl].[LaborQty] as [LaborDtl_LaborQty]
from Erp.LaborDtl as LaborDtl)  as SubQuery2 on 
	SubQuery1.Calculated_Date = SubQuery2.LaborDtl_ClockInDate

AllDatesAndLabor.baq (34.0 KB)

1 Like
5

Sorry Nate, I’m on 10.2.700.13 and couldn’t import your BAQ to check out how you did this. But after looking at your query phrase, I feel silly. I had it right when I did an inner join on the two subqueries, but didn’t sort by calendar date so the query results threw me off. You are officially my hero and have made my week!

Thanks for all your help man!

2 Likes
6

Woo! So happy to help! Have a great day!

1 Like
7

Future reference:

1 Like
8

Thanks @klincecum, had no idea this was possible!

9

I’ve added a Date UD field to the User Code table and just made 10+ years of date entries.

1 Like