Unique Rows

Epicor ERP 10
1

Here is my BAQ:

select 
	[JobMtl].[JobNum] as [JobMtl_JobNum],
	[JobMtl].[ShippedQty] as [JobMtl_ShippedQty],
	[JobMtl].[BorrowedQty] as [JobMtl_BorrowedQty],
	[JobMtl].[EstScrap] as [JobMtl_EstScrap],
	[JobMtl].[EstScrapType] as [JobMtl_EstScrapType],
	[JobMtl].[IssuedQty] as [JobMtl_IssuedQty],
	[JobMtl].[QtyPer] as [JobMtl_QtyPer],
	[LaborDtl].[ScrapQty] as [LaborDtl_ScrapQty],
	[LaborDtl].[LaborQty] as [LaborDtl_LaborQty],
	[LaborDtl].[ScrapReasonCode] as [LaborDtl_ScrapReasonCode],
	[JobHead].[QtyCompleted] as [JobHead_QtyCompleted],
	[JobHead].[ClosedDate] as [JobHead_ClosedDate],
	[JobMtl].[WarehouseCode] as [JobMtl_WarehouseCode],
	(JobHead.QtyCompleted*JobMtl.QtyPer) as [Calculated_AdjCompleted]
from Erp.JobMtl as JobMtl
inner join Erp.LaborDtl as LaborDtl on 
	JobMtl.Company = LaborDtl.Company
And
	JobMtl.JobNum = LaborDtl.JobNum
 and ( LaborDtl.LaborQty <> 0  )

inner join Erp.JobHead as JobHead on 
	JobMtl.Company = JobHead.Company
And
	JobMtl.JobNum = JobHead.JobNum
 and ( JobHead.ClosedDate >= @StartDte  and JobHead.ClosedDate <= @EndDte  )

 where (JobMtl.JobComplete = true  and not JobMtl.WarehouseCode like 'fg-'  and not JobMtl.WarehouseCode like 'rm-diect'  and not JobMtl.WarehouseCode like 'rm-inspc'  and JobMtl.IssuedQty <> 0)

The results give me double the rows for each line. How can I eliminate that and return one row for each? I’ve tried playing with the join types, and nothing. Was considering a subquery, what do you think? If so, how would you set it up?

2

Because you can have multiple labor Records… Add a Group By to each field and change Labor Scrap and Labor Qty yo SUM()

2 Likes
3

That can be done in Epicor? I thought that was only able in SSRS or other reporting service. Sorry for sounding so simple, but I am still learning. Thanks!

4

The BAQ Designer in 10 can do EVERYTHING (almost everything) you can do with raw SQL queries. It just takes some futzing around. Group By CheckBox is on DisplayTab

1 Like
5

Interesting, didn’t realize that. Display tab, mean Display Fields??

6

So, how do I get it to SUM in the BAQ?

7

Have you read the documentation provided by Epicor?

1 Like
8

Add a calculated field to your BAQ

Have you read the ICE Tools guide and all the other guides available in Epic Web? These and many other answers can be found there in :slight_smile:

https://epicweb.epicor.com/doc/Docs/EpicorICETools_UserGuide_101600.pdf

1 Like
9

Create a calculated field:

Sum()

10

Umm, yes and I no. LOL! I do calculated fields for some reason I was thinking something else. Much appreciated.

11

We all have brain farts @Will79 from time to time. Some of us… looks in mirror more than others. :wink:

3 Likes
12

William,
The ICE Tools User Guide is really great!.
Just this week I posted an article that kind of covers everything that has been discussed here, different example but same application. Check it out, I think it will help you see what all you can do with the BAQ Designer. While I’m comfortable writing SQL, I have found that for some things I was able to put what I needed together faster with the Epicor 10 BAQ Designer. And in some cases I ended up with an SQL statement I couldn’t figure out.

-Rick

13

@Rick_Bird I just read your article. Very informative and interesting, Thanks for the link! The more I work with Epicor the more I see so many things I don’t know, and so many interesting things you can do with this program.