BAQ how to get next record in query

Epicor ERP 10
1

Hi
I want to get the next sequence number from a query but not sure how I should do this
my current query is:
select
[LaborDtl].[LaborDtlSeq] as [LaborDtl_LaborDtlSeq],
[NextRecord].[Calculated_NextLDR] as [Calculated_NextLDR]
from Erp.LaborHed as LaborHed
inner join Erp.LaborDtl as LaborDtl on
LaborHed.Company = LaborDtl.Company
and LaborHed.LaborHedSeq = LaborDtl.LaborHedSeq
left outer join (select
[LaborDtl3].[Company] as [LaborDtl3_Company],
[LaborDtl3].[EmployeeNum] as [LaborDtl3_EmployeeNum],
[LaborDtl3].[LaborHedSeq] as [LaborDtl3_LaborHedSeq],
(min( LaborDtl3.LaborDtlSeq )) as [Calculated_NextLDR]
from Erp.LaborDtl as LaborDtl3
group by [LaborDtl3].[Company],
[LaborDtl3].[EmployeeNum],
[LaborDtl3].[LaborHedSeq]) as NextRecord on
LaborDtl.Company = NextRecord.LaborDtl3_Company
and LaborDtl.EmployeeNum = NextRecord.LaborDtl3_EmployeeNum
and LaborDtl.LaborHedSeq = NextRecord.LaborDtl3_LaborHedSeq

My Out put is:
LaborDtl_LaborDtlSeq Calculated_NextLDR
33860 33860
33871 33860
33915 33860
33967 33860
33872 33872
33873 33872
33917 33872

What I want is:
LaborDtl_LaborDtlSeq Calculated_NextLDR
33860 33871
33871 33915
33915 33967
33967 NULL
33872 33873
33873 33917
33917 NULL

Any thoughts on how I might do this?

2

Check out lead and lag functions. They aren’t part of the Epicor menu items, buts it’s SQL so you can type them in. I’ve tried it a couple times and it works, but I don’t have a specific example because I ended up going another direction.

3 Likes
3

That did it!

Thanks heaps Brandon :slight_smile:

4

Hi Rob,

I am currently going down the Lead/Lag rabbit hole.

Wondering if you could share your completed code/query after you managed to get it working?

Thanks :slight_smile: