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

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

1 Comment

You must be Logged on to comment or reply to a post.

Lars Breddemann

June 14, 2017 at 2:17 am

Nice programming example. Thanks for posting it!

Two remarks:

1) It's usually a good idea to include the HANA version you used.

2) The special case handling for the 53th week seems bloated:

 --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;

This is a long way to say "give me a date that is smaller or equal 31.12. "

What you can do instead is using the LEAST() function, i.e.:

do 
begin

declare maxdate date := '31.12.2008';
declare currdate date := '02.01.2009';
declare outdate date;

    outdate := LEAST(:currdate, :maxdate);    
    select :currdate, :maxdate, :outdate from dummy;

end;

That way, you can ensure that the 31.12. for that year is the maximum date and avoid the IF THEN-block.

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

Assigned Tags