Week Ending Date Calculation

647304

Hi Guru's,

I have a requirement to calculate the Weekending date as week data is Saturday to Friday.  Start is Saturday 12:01AM.  End is Friday 12AM

Column A Column B(Should be populate the week Ending Date)

01 July 2016 25 June 2016

02 July 2016 02 July 2016

03 July 2016 02 July 2016

04 July 2016 02 July 2016

05 July 2016 02 July 2016

06 July 2016 02 July 2016

07 July 2016 02 July 2016

08 July2016  02 July 2016

09 July 2016 09 July 2016

10 July 2016 09 July 2016

11 July 2016 09 July 2016

12 July 2016 09 July 2016

13 July 2016 09 July 2016

14 July 2016 09 July 2016

15 July 2016 09 July 2016

16 July 2016 16 July 2016

17 July 2016 16 July 2016

18 July 2016 16 July 2016

19 July 2016 16 July 2016

20 July 2016 16 July 2016

21 July 2016 16 July 2016

22 July 2016 16 July 2016

I know there is post of Week Ending Date Calculation but it is different from my requirement. Please help me to achieve this.

Thanks in advance.

Regards

@li

647304

I know how to do in TSQL below is the code i can use. But i am not able to translate the Module % in OBIEE. I am getting error when i use % sign.

weekStartSaturday = dateadd(dd, - datepart(dw,Current_date) % 7, current_date)

Regards

@LI

Gianni Ceresa

Hi,

Did you try to do it by yourself? Because the post you link give most of the things you will need ...

Based on your example, assuming you want to calculate value of Column B based on value of Column A you are looking to get the date of the last Saturday before the date (or the date itself if it's a Saturday).

Gianni Ceresa

% is the modulo operation, if you look in the formula editor into the math functions area you find the "mod" function doing the same ...

TIMESTAMPADD(SQL_TSI_DAY, MOD(DAYOFWEEK("Time"."T00 Calendar Date"), 7) * -1,"Time"."T00 Calendar Date")

DAYOFWEEK return you the day number in the week (1= Sunday, 7 = Saturday).

If it match what you were looking for close the thread as it will help others knowing they can find something useful on here ...