3 Replies Latest reply: Apr 3, 2012 1:06 PM by Marc Livingston RSS

Joining SQL table with Master Calendar. Issue with resident load?

Marc Livingston

Ok I am joining our employee table with our master calendar so that I can do some other links correctly with the date and employee fields. However when I go to create my final table after all the joining it is not staying and the table is gone. Is there something wrong with my script?

 

 

 

Calendar:  

LET vDateMin = Num(MakeDate(2010,1,1));  
LET vDateMax = Floor(YearEnd(Today()));  
LET vDateToday = Num(Today());  
LET PD = Date(makedate(2010,1,08));

TempCalendar:  
LOAD 
$(vDateMin) + RowNo() - 1 AS DateNumber,  
Date($(vDateMin) + RowNo() - 1) AS TempDate  
AUTOGENERATE 1  
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);  
Calendar:  
LOAD 
Date(TempDate) AS CalendarDate,  
//Date(TempDate) AS  calendarlink,
if(if(mod(floor(TempDate)-'$(PD)',14)=0,TempDate)<=date(Today()+18),if(mod(floor(TempDate)-'$(PD)',14)=0,TempDate) )as _PayDay,

// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth,  
WeekDay(TempDate) AS CalendarDayName,  
Week(TempDate) AS CalendarWeekOfYear,  
Month(TempDate) AS CalendarMonthName,  
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,  
Year(TempDate) AS CalendarYear,  

// Calendar Date Names  
WeekName(TempDate) as CalendarWeekNumberAndYear,  
MonthName(TempDate) as CalendarMonthAndYear,  
QuarterName(TempDate) as CalendarQuarterMonthsAndYear,  

// Start Dates  
DayStart(TempDate) as CalendarDayStart,  
WeekStart(TempDate) as CalendarWeekStart,  
MonthStart(TempDate) as CalendarMonthStart,  
QuarterStart(TempDate) as CalendarQuarterStart,  
YearStart(TempDate) as CalendarYearStart,  

// End Dates  
DayEnd(TempDate) as CalendarDayEnd,  
WeekEnd(TempDate) as CalendarWeekEnd,  
MonthEnd(TempDate) as CalendarMonthEnd,  
QuarterEnd(TempDate) as CalendarQuarterEnd,  
YearEnd(TempDate) as CalendarYearEnd,  

// Combo Date Examples  
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,  
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,  
'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays  

RESIDENT TempCalendar ORDER BY TempDate ASC;  

DROP TABLE TempCalendar;  

LET vDateMin = Num(MakeDate(2010,1,1));  
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));  
LET vDateToday = Num(Today());  








Calendarnew:  
LOAD 
date( CalendarDate,'WWW') as DayofWeekName,
CalendarDate as 'Calendar Date',
month(CalendarDate) as 'Calendar Month',
year(CalendarDate) as 'Calendar Year',
MonthName(CalendarDate) as 'Calendar Month-Year'

RESIDENT Calendar;



Employee:
join (Calendarnew)
LOAD 
    company & '-' & jcdept as jclink,
    company & '-' & expensecode as expenselink,
    company & '-' & empid as laborheadlink,
    company & '-' & empid as laborheadlink2,    
    company as Company,
    empid as 'Employee ID',
    name as Employee,
    shift as Shift,
    laborrate as 'Labor Rate',
    if(payroll=1,'Employee','Contract') as 'On Payroll',
    if(empstatus='A','Active','Inactive') as Status,
    expensecode as 'Expense Code',
    jcdept as 'JC Dept. Code',
  Plant,

  Department,

   [Plant Short],
     if(productionworker=1,'Shop','Office') as 'Worker';
SQL SELECT company,
    empid,
    name,
    shift,
    laborrate,
    payroll,
    empstatus,
    expensecode,
    jcdept,
    number01,
    number02,
    productionworker
FROM epicor904.dbo.empbasic;






NewEmployeeCal:
Load 
DayofWeekName,
[Calendar Date],
[Calendar Month],
[Calendar Year],
[Calendar Month-Year],
jclink,
expenselink,
laborheadlink & '-' & [Calendar Date] as laborheadlink,
laborheadlink & '-' & [Calendar Date] as laborheadlink2,    
Company,
[Employee ID],
Employee,
Shift,
[Labor Rate],
[On Payroll],
Status,
[Expense Code],
[JC Dept. Code],
Plant,
Department,
[Plant Short],
Worker

RESIDENT Calendarnew;

Drop Table Calendarnew;

 

 

Note that I removed some of the calculations for confidentiality sake in employee table and just left the new field name.

 

I should have a new table labeled NewEmployeeCal but there is none.