Date Dimension – Quarter start date, Quarter end date, Week Start Date, Month Start Date, Month End Date, Week End Date, Current Quarter start date, Start date of previous quarter and End date of previous quarter

[Former Member]

 

There are often business requirements to find date dimensions like quarter start date, quarter end date and many other calculated columns.

 

Below formula can be used in calculated column in SAP HANA View 

 

Few of the below formula is derived based on M_TIME_DIMENSION. An attribute view on this table with the below calculated column can be created and used in any analytical/calculation view.

 

1. Week Start Date – Gives the date for the first day of week (Here Monday is considered as the first day of the week)

 

     if("DayOfWeek" = '00',"Date",adddays("Date",-int("DayOfWeek")))

 

2.  Week End Date – Gives the date for the last day of week (Here Sunday is considered as the    last day of the week)

 

     if("DayOfWeek" = '06',"Date",adddays("Date",06-int("DayOfWeek")))

 

3.  Month Start Date– Gives the date for the first day of the month

 

     date("CalMonth")

 

4.  Month End Date– Gives the date for the last day of the month

 

     if(rightstr("CalMonth",2)='12',adddays(date("CalMonth"+'01'),31),date(int("CalMonth")+1)) – 1

 

5.  Quarter Start Date– Gives the first day of the quarter based on input date

     date(

      string(

      string(component("Date",1))

      +

      case(

      string(

                 component("Date",2)),'1','01','2','01','3','01','4','04','5','04','6','04','7','07','8','07','9','07','10','10','11','10'     ,'12','10'

      )))

 

6.  Quarter End Date– Gives the end day of the quarter based on input date

 

     adddays(date(

      string(

      if("QuarterInt" = 4,string(component("Date",1) +1), string(component("Date",1)))

      + 

      case(

      string(

                 component("Date",2)),'1','04','2','04','3','04','4','07','5','07','6','07','7','10','8','10','9','10','10','01','11','01'     ,'12','01'

      ) ) ),-1)

 

Below formula can be used in SQL/Universe level 

 

Business generally request for few additional columns/filters at universe level. So, below are few of the calculated date columns -

 

1. Start of current quarter – Gives the first day of the current quarter

     select

 

     to_date(

     to_char(

     to_char(Year(Current_date))

      ||

 

case when substring(Quarter(Current_date),6) = ('Q1') then '01'

when substring(Quarter(Current_date),6) = ('Q2') then '04'

when substring(Quarter(Current_date),6) = ('Q3') then '07'

when substring(Quarter(Current_date),6) = ('Q4') then '11' end

 

      ) )

 

     from dummy

 

2.  End of Current quarter – Gives the last day of the current quarter

 

   select

 

   add_days(to_date(

case when substring(quarter(current_date),6) = 'Q4' then to_char(Year(Current_date) +1)

else to_char(Year(Current_date)) end

 

      ||

 

case when to_char(month(Current_date)) in ('1','2','3') then '04'

when  to_char(month(Current_date)) in ('4','5','6') then '07'

when to_char(month(Current_date)) in ('7','8','9') then '10'

when to_char(month(Current_date)) in ('10','11','12') then '01' end

 

      ),-1)

     from dummy

 

3.  Start of Previous quarter – Gives the first day of the last quarter

 

     select

 

      CASE WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q1')

THEN ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE)),-3)

 

WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q2')

THEN TO_DATE(YEAR(CURRENT_DATE), 'YYYY')

 

      WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q3')

THEN ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE)),3)

 

WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q4')

THEN ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE)),6)

 

     end

    from dummy

 

4.  End of Previous quarter – Gives the last day of the previous quarter

 

     select

 

     CASE WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q1')

     THEN ADD_DAYS(TO_DATE(YEAR(CURRENT_DATE)),-1)

 

WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q2')

     THEN ADD_DAYS(ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE)),3),-1)

 

WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q3')

     THEN ADD_DAYS(ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE)),6),-1)

 

     WHEN (SUBSTRING (QUARTER (TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'), 1),6,2) = 'Q4')

     THEN ADD_DAYS(ADD_MONTHS(TO_DATE(YEAR(CURRENT_DATE)),9),-1)

 

     End

    from dummy

Same can also be achived through below query -

   select ADD_DAYS(

     to_date(

     to_char(

     to_char(Year(Current_date))

      ||

case when substring(Quarter(Current_date),6) = ('Q1') then '01'

when substring(Quarter(Current_date),6) = ('Q2') then '04'

when substring(Quarter(Current_date),6) = ('Q3') then '07'

when substring(Quarter(Current_date),6) = ('Q4') then '11' end

      ) ) ,-1)

     from dummy