HANA Get Start and Finish dates for a week of the ...

brad_smith5 · ‎06-14-2017

Recently I have been working with the WEEK() function in HANA. This works to identify the week of the year for a specific date.

SELECT WEEK(CURRENT_DATE) from dummy;

This information is great but I need a little more for my scenario. I am looking for start and end dates of the week for display and reporting purposes.

How would I do this? For any given year I know the week 1 start date is the first of January. To find the end date of the first week I can use the WEEKDAY() function to return the day of the week and calculate an offset. This returns a value of 0 to 6 with 0 indicating Monday. From this point, I can iterate over the dates until I find the desired week number.

I considered creating a scalar function to perform this, but decided against this approach as the processing would be wasted when evaluated on multiple rows for the same year.

I decided on using a Calculation View so that I could retrieve and store all of the dates for a given year, reducing the amount of processing required.

WEEKS_FOR_YEAR.calculationview

/********* Begin Procedure Script ************/ 

BEGIN 



    --Get the week 1 Start and Finish dates

    declare lv_week_start DATE := TO_DATE(CAST(:iv_year as VARCHAR) || '-01-01');



    --Calculate the offset

    declare lv_day_offest integer := 6 - WEEKDAY(:lv_week_start);

    declare lv_week_finish DATE := ADD_DAYS(:lv_week_start, :lv_day_offest);



    --The data is stored in Arrays

    declare lv_weekIndex integer := 0;

    declare lv_week_array_id integer ARRAY;

    declare lv_week_array_date_start DATE ARRAY;

    declare lv_week_array_date_finish DATE ARRAY;

    

    --Prime the arrays with the week 1 dates

    lv_week_array_id[1] := 1;

    lv_week_array_date_start[1] = :lv_week_start;

    lv_week_array_date_finish[1] = :lv_week_finish; 

    

    --Loop over the remaining weeks

    FOR lv_weekIndex in 2 .. 53 DO

        lv_week_start = ADD_DAYS(:lv_week_finish, 1);

        lv_week_finish = ADD_DAYS(:lv_week_start, 6); 

        

        --The last week of the year can not go over YYYY-12-31

        if(:lv_weekIndex = 53)

        then

            if(TO_DATE(CAST(:iv_year as VARCHAR) || '-12-31') < :lv_week_finish)

            then

                lv_week_finish = TO_DATE(CAST(:iv_year as VARCHAR) || '-12-31');

            end if;

        end if;

        

        --Add the values the the respective arrays

        lv_week_array_id[:lv_weekIndex] := :lv_weekIndex;

        lv_week_array_date_start[:lv_weekIndex] = :lv_week_start;

        lv_week_array_date_finish[:lv_weekIndex] = :lv_week_finish;

        

    end for; 

    

    --Select the arrays into the result set

    var_out = unnest(:lv_week_array_id, :lv_week_array_date_start, :lv_week_array_date_finish) as ("Week", "StartDate", "FinishDate");



END /********* End Procedure Script ************/

Finally, we must add the output column definitions and input parameters to the view.

Columns

```
Week: INTEGER
```

```
StartDate: DATE
```

```
FinishDate: DATE
```

Input Parameters

```
iv_year: INTEGER
```

I can now call this calculation view for any year and join the results to any data set as required.

SELECT 

	"Week",

	"StartDate",

	"FinishDate",

	YEAR(CURRENT_DATE) as "Year"

FROM "_SYS_BIC"."dev/WEEKS_FOR_YEAR"

	(placeholder."$$iv_year$$"=>YEAR(CURRENT_DATE))

	

union all

	

	SELECT 

	"Week",

	"StartDate",

	"FinishDate",

	YEAR(CURRENT_DATE)-1 as "Year"

FROM "_SYS_BIC"."dev/WEEKS_FOR_YEAR"

	(placeholder."$$iv_year$$"=>YEAR(CURRENT_DATE)-1)

	

	;

Output

Week	StartDate	FinishDate	Year

1	01.01.2017	01.01.2017	2017

2	02.01.2017	08.01.2017	2017

3	09.01.2017	15.01.2017	2017

4	16.01.2017	22.01.2017	2017

5	23.01.2017	29.01.2017	2017

6	30.01.2017	05.02.2017	2017

7	06.02.2017	12.02.2017	2017

8	13.02.2017	19.02.2017	2017

9	20.02.2017	26.02.2017	2017

10	27.02.2017	05.03.2017	2017

11	06.03.2017	12.03.2017	2017

12	13.03.2017	19.03.2017	2017

13	20.03.2017	26.03.2017	2017

14	27.03.2017	02.04.2017	2017

15	03.04.2017	09.04.2017	2017

16	10.04.2017	16.04.2017	2017

17	17.04.2017	23.04.2017	2017

18	24.04.2017	30.04.2017	2017

19	01.05.2017	07.05.2017	2017

20	08.05.2017	14.05.2017	2017

21	15.05.2017	21.05.2017	2017

22	22.05.2017	28.05.2017	2017

23	29.05.2017	04.06.2017	2017

24	05.06.2017	11.06.2017	2017

25	12.06.2017	18.06.2017	2017

26	19.06.2017	25.06.2017	2017

27	26.06.2017	02.07.2017	2017

28	03.07.2017	09.07.2017	2017

29	10.07.2017	16.07.2017	2017

30	17.07.2017	23.07.2017	2017

31	24.07.2017	30.07.2017	2017

32	31.07.2017	06.08.2017	2017

33	07.08.2017	13.08.2017	2017

34	14.08.2017	20.08.2017	2017

35	21.08.2017	27.08.2017	2017

36	28.08.2017	03.09.2017	2017

37	04.09.2017	10.09.2017	2017

38	11.09.2017	17.09.2017	2017

39	18.09.2017	24.09.2017	2017

40	25.09.2017	01.10.2017	2017

41	02.10.2017	08.10.2017	2017

42	09.10.2017	15.10.2017	2017

43	16.10.2017	22.10.2017	2017

44	23.10.2017	29.10.2017	2017

45	30.10.2017	05.11.2017	2017

46	06.11.2017	12.11.2017	2017

47	13.11.2017	19.11.2017	2017

48	20.11.2017	26.11.2017	2017

49	27.11.2017	03.12.2017	2017

50	04.12.2017	10.12.2017	2017

51	11.12.2017	17.12.2017	2017

52	18.12.2017	24.12.2017	2017

53	25.12.2017	31.12.2017	2017

1	01.01.2016	03.01.2016	2016

2	04.01.2016	10.01.2016	2016

3	11.01.2016	17.01.2016	2016

4	18.01.2016	24.01.2016	2016

5	25.01.2016	31.01.2016	2016

6	01.02.2016	07.02.2016	2016

7	08.02.2016	14.02.2016	2016

8	15.02.2016	21.02.2016	2016

9	22.02.2016	28.02.2016	2016

10	29.02.2016	06.03.2016	2016

11	07.03.2016	13.03.2016	2016

12	14.03.2016	20.03.2016	2016

13	21.03.2016	27.03.2016	2016

14	28.03.2016	03.04.2016	2016

15	04.04.2016	10.04.2016	2016

16	11.04.2016	17.04.2016	2016

17	18.04.2016	24.04.2016	2016

18	25.04.2016	01.05.2016	2016

19	02.05.2016	08.05.2016	2016

20	09.05.2016	15.05.2016	2016

21	16.05.2016	22.05.2016	2016

22	23.05.2016	29.05.2016	2016

23	30.05.2016	05.06.2016	2016

24	06.06.2016	12.06.2016	2016

25	13.06.2016	19.06.2016	2016

26	20.06.2016	26.06.2016	2016

27	27.06.2016	03.07.2016	2016

28	04.07.2016	10.07.2016	2016

29	11.07.2016	17.07.2016	2016

30	18.07.2016	24.07.2016	2016

31	25.07.2016	31.07.2016	2016

32	01.08.2016	07.08.2016	2016

33	08.08.2016	14.08.2016	2016

34	15.08.2016	21.08.2016	2016

35	22.08.2016	28.08.2016	2016

36	29.08.2016	04.09.2016	2016

37	05.09.2016	11.09.2016	2016

38	12.09.2016	18.09.2016	2016

39	19.09.2016	25.09.2016	2016

40	26.09.2016	02.10.2016	2016

41	03.10.2016	09.10.2016	2016

42	10.10.2016	16.10.2016	2016

43	17.10.2016	23.10.2016	2016

44	24.10.2016	30.10.2016	2016

45	31.10.2016	06.11.2016	2016

46	07.11.2016	13.11.2016	2016

47	14.11.2016	20.11.2016	2016

48	21.11.2016	27.11.2016	2016

49	28.11.2016	04.12.2016	2016

50	05.12.2016	11.12.2016	2016

51	12.12.2016	18.12.2016	2016

52	19.12.2016	25.12.2016	2016

53	26.12.2016	31.12.2016	2016

HANA Get Start and Finish dates for a week of the year

SAP PI for Beginners

ABAP 7.40 Quick Reference

Fiori: technical installation and configuration of one app from A - Z