Pivot Help

Epicor ERP 10
1

I’m trying to pivot the ProdCal table and having a hard time figuring this out.

What I need is a table to join with the columns CalendarID, WeekDayNum, and True/false

So I need this.
image

To pivot to this.

image
image.png251×594 7.92 KB

I can’t figure out how I would get the field names into data in a row. The pivot function only takes data from the table (which makes sense, but doesn’t help me)

Anyone have an idea on how to do this?

2

Are you trying to pivot within the BAQ I am assuming?

3

That’s what I am trying to do. Looking at it now, I don’t know if the pivot function is the right tool for the job. I’m trying to see if I can get a CTE to work now buy looping through 1-7 and doing a calculated field with a case statement to grab correct field. I just really don’t understand CTE queries. I’ve gotten a couple of them to work before, but I don’t understand how/why some things work the way they do, so unless I have an explicit example, they never seem to work for me.

2 Likes
4

hahaha I feel you! It happens the same to me

5

I think I may have figured it out. (It still seems like trial and error to me) but I have a list of number and calendar ID’s so I should be able to make my calculated field.

6

This should work.

image
image.png350×584 9.21 KB

7

would you mind sharing how you did it? I am curious now as of how to accomplish it.

8

Here’s the BAQ. I’m on 10.2.200.8.

shiftpivot.baq (43.4 KB)

Here’s the SQL for those who can’t open it.

with [DayNumbers] as 
(select 
	[ProdCal].[Company] as [ProdCal_Company],
	[ProdCal].[CalendarID] as [ProdCal_CalendarID],
	(1) as [Calculated_DayNum]
from Erp.ProdCal as ProdCal
union all
select 
	[ProdCal1].[Company] as [ProdCal1_Company],
	[ProdCal1].[CalendarID] as [ProdCal1_CalendarID],
	(DayNumbers.Calculated_DayNum + 1) as [Calculated_DayNum]
from  DayNumbers  as DayNumbers
inner join Erp.ProdCal as ProdCal1 on 
	DayNumbers.ProdCal_CalendarID = ProdCal1.CalendarID
	and DayNumbers.ProdCal_Company = ProdCal1.Company
where DayNumbers.Calculated_DayNum <= 6)

select 
	[DayNumbers1].[ProdCal_Company] as [ProdCal_Company],
	[DayNumbers1].[ProdCal_CalendarID] as [ProdCal_CalendarID],
	[DayNumbers1].[Calculated_DayNum] as [Calculated_DayNum],
	(case when DayNumbers1.Calculated_DayNum = 1 then ProdCal2.WorkWeek1 
    when DayNumbers1.Calculated_DayNum = 2 then ProdCal2.WorkWeek2
    when DayNumbers1.Calculated_DayNum = 3 then ProdCal2.WorkWeek3 
    when DayNumbers1.Calculated_DayNum = 4 then ProdCal2.WorkWeek4 
    when DayNumbers1.Calculated_DayNum = 5 then ProdCal2.WorkWeek5 
    when DayNumbers1.Calculated_DayNum = 6 then ProdCal2.WorkWeek6 
    when DayNumbers1.Calculated_DayNum = 7 then ProdCal2.WorkWeek7
   else  0
 end) as [Calculated_ValidDay]
from  DayNumbers  as DayNumbers1
inner join Erp.ProdCal as ProdCal2 on 
	DayNumbers1.ProdCal_Company = ProdCal2.Company
	and DayNumbers1.ProdCal_CalendarID = ProdCal2.CalendarID
1 Like
9

Thanks!