BAQ - get last ClockIn per ResourceID on same Payroll Date

well I just typed MAX in there...

it works now! thanks a lot man!

Hi,


I am trying to design a BAQ(I want to later use it as a dashboard) and I am having trouble on getting the last ClockIn Time from a ResourceID on a specific day.


I tried using subqueries and sorting descending on ResourceID, PayrollDate, ClockInTime and used a select top clause=1.


In this case it only returns last clock in time, not per resourceID.


Any suggestions?  


Thanks,

John

If just getting direct labor ResourceGroup (Left Outer, First) to LaborDtl then (LeftOuter) to LaborHed

Labor Head will have Payroll Date, Labor Dtl the Time.

 

Patrick Winter

 

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Friday, September 02, 2016 01:41
To: vantage@yahoogroups.com
Subject: [Vantage] BAQ - get last ClockIn per ResourceID on same Payroll Date

 

 

Hi,

 

I am trying to design a BAQ(I want to later use it as a dashboard) and I am having trouble on getting the last ClockIn Time from a ResourceID on a specific day.

 

I tried using subqueries and sorting descending on ResourceID, PayrollDate, ClockInTime and used a select top clause=1.

 

In this case it only returns last clock in time, not per resourceID.

 

Any suggestions?  

 

Thanks,

John

This e-mail and any attachments may contain confidential and privileged information. If you are not the intended and/or named recipient or recipients, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient or recipients is unauthorized and may be illegal. Any views or opinions expressed in this email are those of the author and do not necessarily represent those of the Specialty Screw Corporation. Warning: Although precautions have been taken to make sure no viruses are present in this email, Specialty Screw Corporation cannot accept responsibility for any loss or damage that may arise from the use of this email or attachments.
This e-mail and any attachments may contain confidential and privileged information. If you are not the intended and/or named recipient or recipients, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient or recipients is unauthorized and may be illegal. Any views or opinions expressed in this email are those of the author and do not necessarily represent those of the Specialty Screw Corporation. Warning: Although precautions have been taken to make sure no viruses are present in this email, Specialty Screw Corporation cannot accept responsibility for any loss or damage that may arise from the use of this email or attachments.   ­­  
I do not think top 1 on a subquery works correctly.

Anyways just try 

LaborDtl (table)
--->ResourceID(grouped)
--->PayrollDate(grouped)
--->Calculated field "MAX(LaborDtl.ClockInTime)"
What do you mean by first? I am using Epicor 10....

I’m in 9, BAQs we can select find the FIRST, LAST or EACH record. Find first just grabs from the end or beginning of the primary or whatever index is selected by the joins.

 

Patrick Winter

 

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Friday, September 02, 2016 02:03
To: vantage@yahoogroups.com
Subject: [Vantage] Re: BAQ - get last ClockIn per ResourceID on same Payroll Date

 

 

What do you mean by first? I am using Epicor 10....

This e-mail and any attachments may contain confidential and privileged information. If you are not the intended and/or named recipient or recipients, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient or recipients is unauthorized and may be illegal. Any views or opinions expressed in this email are those of the author and do not necessarily represent those of the Specialty Screw Corporation. Warning: Although precautions have been taken to make sure no viruses are present in this email, Specialty Screw Corporation cannot accept responsibility for any loss or damage that may arise from the use of this email or attachments.
This e-mail and any attachments may contain confidential and privileged information. If you are not the intended and/or named recipient or recipients, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient or recipients is unauthorized and may be illegal. Any views or opinions expressed in this email are those of the author and do not necessarily represent those of the Specialty Screw Corporation. Warning: Although precautions have been taken to make sure no viruses are present in this email, Specialty Screw Corporation cannot accept responsibility for any loss or damage that may arise from the use of this email or attachments.   ­­  
Yeah I know that, but the thing is first, last, each do not exist in E10 anymore...
I tried that:

Sort Order: ResourceID
                 Payroll Date
                 Add calculated field: max(LaborDtl.ClockinTime) but I get the following message:

Incorrect syntax near 'LabortDtl'...somehow it doesn't like max in there...
 
To be fair, you never mentioned which epicor.  

Not in the sort order.. Display fields

I just made that exact query I posted and no issues.  
duckorz, 

you're right, I am sorry for not mentioning my version of Epicor.

It's funny, it let's me use with min but not with max...

max_error.png
max_error.png
Shared with Dropbox
Preview by Yahoo

 





---In vantage@yahoogroups.com, <don.n.doan@...> wrote :

To be fair, you never mentioned which epicor.  

Not in the sort order.. Display fields

I just made that exact query I posted and no issues.  
You can't use max as the field name.