Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Nov 22, 2016 at 02:15 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 02, 2016 at 12:00 PM

    Thank you Oliveira.

    Add comment
    10|10000 characters needed characters exceeded