Skip to Content
0

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

Nov 21, 2016 at 05:45 AM

112

avatar image

Hi Experts,

Could anyone please help me in finding below:(using SQL script)

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 left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Lucas Oliveira
Nov 22, 2016 at 02:15 AM
1

Hello,

As already discussed here you need to adapt whatever you want to do to your actual business/fiscal calendar.

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

Share
10 |10000 characters needed characters left characters exceeded
Jyothirmayi Thottempudi Dec 02, 2016 at 12:00 PM
0

Thank you Oliveira.

Share
10 |10000 characters needed characters left characters exceeded