P_SPP_TrainingDate01

@AbapCatalog.sqlViewName: 'PSDSPPTD01'
@AbapCatalog.preserveKey:true
@AbapCatalog.compiler.compareFilter:true
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.private: true
@VDM.viewType: #COMPOSITE

define view P_SPP_TrainingDate01
as select from I_CalendarDate as CD
left outer join P_SPP_TrainingPeriod as TP
  on TP.CalendarDate = TP.CalendarDate
{
  key CD.CalendarDate,
  
  YearMonth,
  YearQuarter,
  
  CalendarWeek,
  CalendarMonth,
  CalendarQuarter,

  cast(
    case
      when CalendarDay >= '01' and CalendarDay <= '10'
        then 1
      when CalendarDay >= '11' and CalendarDay <= '20'
        then 2
      else 3
    end
  as sp_pom ) as PartOfMonth,
  
  cast(
    case
      when ( CalendarMonth = '01' or CalendarMonth = '04' or CalendarMonth = '07' or CalendarMonth = '10' )
           and ( CalendarDay >= '01' and CalendarDay <= '10' )
        then 1
      when ( CalendarMonth = '01' or CalendarMonth = '04' or CalendarMonth = '07' or CalendarMonth = '10' )
           and ( CalendarDay >= '11' and CalendarDay <= '20' )
        then 2
      when ( CalendarMonth = '01' or CalendarMonth = '04' or CalendarMonth = '07' or CalendarMonth = '10' )
           and ( CalendarDay >= '21' )
        then 3
      when ( CalendarMonth = '02' or CalendarMonth = '05' or CalendarMonth = '08' or CalendarMonth = '11' )
           and ( CalendarDay >= '01' and CalendarDay <= '10' )
        then 4
      when ( CalendarMonth = '02' or CalendarMonth = '05' or CalendarMonth = '08' or CalendarMonth = '11' )
           and ( CalendarDay >= '11' and CalendarDay <= '20' )
        then 5
      when ( CalendarMonth = '02' or CalendarMonth = '05' or CalendarMonth = '08' or CalendarMonth = '11' )
           and ( CalendarDay >= '21' )
        then 6
      when ( CalendarMonth = '03' or CalendarMonth = '06' or CalendarMonth = '09' or CalendarMonth = '12' )
           and ( CalendarDay >= '01' and CalendarDay <= '10' )
        then 7
      when ( CalendarMonth = '03' or CalendarMonth = '06' or CalendarMonth = '09' or CalendarMonth = '12' )
           and ( CalendarDay >= '11' and CalendarDay <= '20' )
        then 8
      when ( CalendarMonth = '03' or CalendarMonth = '06' or CalendarMonth = '09' or CalendarMonth = '12' )
           and ( CalendarDay >= '21' )
        then 9
    end
  as sp_poq) as PartOfQuarter,
  
  left( dats_add_months( CD.CalendarDate, -1,'FAIL' ), 6 ) as Previous1YearMonth,
  
  cast(
    case
      when CalendarQuarter = '1'
        then concat( left( dats_add_months( CD.CalendarDate,  -12,'FAIL' ), 4 ), '4' )
      when CalendarQuarter = '2'
        then concat(CalendarYear, '1')
      when CalendarQuarter = '3'
        then concat(CalendarYear, '2')
      else
        concat(CalendarYear, '3')
    end
  as vdm_yearquarter) as Previous1YearQuarter
}
where CD.CalendarDate >= TP.TrainingStartDate
  and CD.CalendarDate < TP.TrainingEndDate
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CALENDARDATE",
"P_SPP_TRAININGPERIOD"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"VERSION":0
}
}*/