Utilizing ProdCalWeek table to determine prod work dates

Kinetic ERP
1

We recently started using the ProdCalWeek table in our system. I am trying to create a BAQ utilizing this table to determine production work dates. I wrote the following query in SQL, but I’m not quite sure how to implement it in a BAQ because the SubQuery is not utilizing a table. Any help would be appreciated. Thanks,

SELECT pcw.WeekNum,
pcw.WorkDate As WeekStartDate,
DATEADD(DAY, n.number, pcw.WorkDate) AS ProdWorkDate,
datename(dw,DATEADD(DAY, n.number, pcw.WorkDate)) As DayOfWeek,
‘4’ As ‘Line_A_SlotsAvail’,
ISNULL(SUA.SlotsUsed,0) As ‘Line_A_SlotsUsed’,
‘7’ As ‘Line_B_SlotsAvail’,
ISNULL(SUB.SlotsUsed,0) As ‘Line_B_SlotsUsed’,
‘4’ As ‘Line_C_SlotsAvail’,
ISNULL(SUC.SlotsUsed,0) As ‘Line_C_SlotsUsed’,
‘1’ As ‘Line_D_SlotsAvail’,
ISNULL(SUD.SlotsUsed,0) As ‘Line_D_SlotsUsed’
From KineticPilot.Erp.ProdCalWeek pcw
Join (SELECT 0 AS number UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4) n
ON 1 = 1

2

I’m sure there’s a better way, but I think I would have written the subquery like this:

inner join  (select top (5)  
	(ROW_NUMBER() OVER (PARTITION BY ProdCalWeek.Company ORDER BY ProdCalWeek.Company)) as [Calculated_number]
from Erp.ProdCalWeek as ProdCalWeek)  as SubQuery1

It makes it feel cleaner in BAQ-Land, in my opinion. And I think it should get you through any hiccups the BAQ route might be giving you.

3

Thanks for the idea, I think I can make this work.