BAQ to display absent workers


(Robert Gordon) #1

Been working on a BAQ for our production manager that displays employees that are present and absent (edit: to clarify I mean getting getting record of past presents/absences). I’ve been able to get the BAQ to display the former in the query, but not the latter. Is there a way to create rows in the BAQ for workers that don’t clock in? Would it be more feasible to display it VIA crystal reports, or running the data through an Excel sheet?

Anyone have any thoughts?


(al maragni (USA - NY/NJ)) #2

Doesn’t the SHOP TRACKER show who is NOT here?

(Brad Boes) #3

I would start with the employee master file and link the labor header to that with an outer join. That should show all employees clocked in or not.

It’s a start…



(Robert Gordon) #4

amaragni, the Shop Tracker only shows you who is absent today (the day you run Shop Tracker), whereas I want a record of past absences.

(Tim Shoemaker) #5

showing people who were not here “Last Week” is a little more challenging, especially in E9 BAQs… It was hard to find things that are not there.
In E10, it is a little easier, because you can create one sub-query that summarizes all the dates worked (showing only dates), and then a top level query that would show an employee listing, linked to a counter that shows how many records exist for each day… Anyone with zero was not there that day. You could then filter that, and have your dashboard.

(Robert Gordon) #6

Brad that is what we are doing currently, unfortunately it only shows employees that are clocked in.

(Robert Gordon) #7

Yeah Tim, the more I look into this with E9 the more challenging it becomes. If our company DOES end up upgrading to 10 one day, I’ll keep your sub-query advice in mind, so I do appreciate that suggestion (at least E10-wise). In the meantime, any suggestions with E9?

(Calvin Krusen) #8

Couldn’t a left outer join between all employees and clocked-in records be done?

Group by employee, with a count of clocked in records.

Then filter (in dashboard or Report) on the results that have a zero for the count of clocked records

(Robert Gordon) #9

Thanks for the suggestion Calvin. However that seems to be Crystal Reports instructions, not BAQ instructions. I do plan on testing that through Crystal Reports here soon so I will keep you posted on the results.

(Calvin Krusen) #10

I’m pretty sure I used to do outer left joins in V8. Can’t imagine why you couldn’t do them in E9.

The grouping happens automatically when the right table only returns calculated values.

Here’s the SQL from MSquery (which is even more limited than V8 or E9’s BAQ. (We don’t track labor, so I couldn’t do it on the tables you want. But this returns the Orders with the count of lines. Think of the OrderHead as your table of people, and the OrderLine as the table of clock-in records)

SELECT OrderHed.OrderNum, Count(OrderDtl.OrderLine) AS 'Count of OrderLine'
FROM {oj LIVE.dbo.OrderHed OrderHed LEFT OUTER JOIN LIVE.dbo.OrderDtl OrderDtl ON OrderHed.OrderNum = OrderDtl.OrderNum}
GROUP BY OrderHed.OrderNum
ORDER BY OrderHed.OrderNum

The above returns …


The highlighted lines are Orders(People), with no Lines(Clock-in records)

If memory serves me correctly, you can only use each table once in a V8 or E9 BAQ. So to get non-calculated data from the right table (like the actual date), you might need to do this in a Dashboard with sub-BAQ

(Tim Shoemaker) #11

Yes, you CAN do that with orders… but with something like a DATE, it is harder… Basically, there is not a table of dates available to use as your primary “header”

(Calvin Krusen) #12

UD101 ?


I actual do have a UD table that just has the Key1 as the numbers from 1 to 1000.

(Louis Fequet) #13

Can easily be done by external BAQs.

We do tend to forget them.