Former Member

### How to find last day and fist day of every week in a year

Hi Experts,

1. First and last days of every week

Ex: Week 42 first day is 16/10/2016 and last day 22/10/2016

2. Number Week in Fiscal Month

Ex: 42 week of October is 3rd week of the Month October

43 week of October is 4th week of that month October

44 week of November is 1st week of the month November

Thank you,

Jyothirmayi

10|10000 characters needed characters exceeded

Nov 22, 2016 at 02:15 AM

Hello,

However, for point number 1 and just as an example, one could use m_time_dimension table to get the max and min values for date_sap on the calendar week defined there:

```select week_year_int, week_int, min(date_sap) as first_day, max(date_sap) as last_day from "_SYS_BI"."M_TIME_DIMENSION" where week_int in (1,2,42,51,52) and week_year_int = 2016
group by week_year_int, week_int
order by week_year_int

-- result:
WEEK_YEAR_INT;WEEK_INT;FIRST_DAY;LAST_DAY
2016         ;1       ;20160103 ;20160109
2016         ;2       ;20160110 ;20160116
2016         ;42      ;20161016 ;20161022
2016         ;51      ;20161218 ;20161224
2016         ;52      ;20161225 ;20161231```

Worth giving special attention in this example for the field week_year_int which needs to map to the year you want to look for. Using column year instead will give you wrong results as week numbers will extend to the next year. For example, take week_int = 52 and year = 2016 (and not week_year_int).
This will give you any date within 2016 that has week_int = 52, including the week 52 of 2015! Check what we have on M_TIME_DIMENSION:
```select week_year_int, week_int, year, date_sap from "_SYS_BI"."M_TIME_DIMENSION"
where year = 2016 and
week_int = 52
-- result:
WEEK_YEAR_INT;WEEK_INT;YEAR;DATE_SAP
2015         ;52      ;2016;20160101    <-- week 52 of 2015!
2015         ;52      ;2016;20160102    <-- week 52 of 2015!
2016         ;52      ;2016;20161225    <-- day 1 of week 52 on 2016
2016         ;52      ;2016;20161226    <-- day 2 of week 52 on 2016
2016         ;52      ;2016;20161227    <-- day 3 of week 52 on 2016
2016         ;52      ;2016;20161228    <-- day 4 of week 52 on 2016
2016         ;52      ;2016;20161229    <-- day 5 of week 52 on 2016
2016         ;52      ;2016;20161230    <-- day 6 of week 52 on 2016
2016         ;52      ;2016;20161231    <-- day 7 of week 52 on 2016
```

Again, fiscal calendar may include exceptions and you need to tailor this based on that and possibly create a table function to modularize your logic.

BRs,

Lucas de Oliveira