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.