ISO WEEK NUMBER OF FISCAL AND NORMAL DATE IN DATA SERVICES

[former_member190054]

ISO WEEK NUMBER OF FISCAL AND NORMAL DATE IN DATA SERVICES

ISO week number of a date is similar to normal week number except in end of the year and start of the year. The First week of the year and last week of the year vary from normal date depends on the starting day of the year.

First week

·         The first week with the majority (four or more) of its days in the starting year

·         If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is part of week 52 or 53 of the previous year.

·         The week with the year's first working day in it (if Saturdays, Sundays, and 1 January are not working days).

Last week

·         If 31 December is on a Monday, Tuesday, or Wednesday, it is in week 01 of the next year, otherwise in week 52 or 53.

·         If 31 December is Wednesday then it will come on first week and remaining days in that week will also have week number one.

Fiscal year is simply financial year which can start at any date of the year. For e.g. the financial year may start at july01.That means the starting day of the year is july1 and end day of the year is jun30 of next normal year.

Here I am posting a code to get the ISO Week number of normal date and fiscal date. This function will give the ISO week number corresponding to any date (Fiscal date and normal date).

Custom Function

This custom function has two input parameters and one output parameter. The input parameters are actual date (Input Date) and the fiscal date format. The actual date will be the input date to which we are finding the ISO week number based on the fiscal date format. The Fiscal format should be like ’07.01’ that means the fiscal date start at July 1 and end at June 30.

Example: - Actual Date :- ‘2012.01.01’

Fiscal Format:- ’07.01’

The Actual date is a date value and date fiscal format is char type (varchar(6)).

The code is given below.

#$LV_USER_ENTERED_DATE    : User entered Date for which we going to find out ISO week number e.g.2010.01.01

#$PAR_USER_ENTERED_DATE: Parameter passing user entered date.

#$PAR_USER_FS_DATE_FORMAT: Parameter passing the Fiscal Date Format ('07.01')

#$LV_MONTH: Month of user entered Date

#$LV_FSCL_MNTH: Month number corresponding to Fiscal Date

#$LV_FS_DATE: Fiscal year Start Date corresponding to user entered Date

#$LV_LEAP_YEAR:- The fiscal year is leap year or not (1 for leap year and 0 for non-leap year)

#$LV_DAY_OF_ACT_DATE:- Day number of user entered date (1 for Monday)

#$LV_DAY_OF_FSCL_DATE: Day number of corresponding Fiscal start date (1 for Monday)

#$LV_FSCL_DAY: Number of fiscal day since the referred date.

#$LV_DAY_OF_PREV_FSCL_YEAR: Day number of previous Fiscal year date.

#$LV_FSCL_ISO_WEEK_NO: ISO Date based on the user entered parameters.

#Load a user entered date to variable

#------------------------------------------------------------------

$LV_USER_ENTERED_DATE= $PAR_USER_ENTERED_DATE;

$LV_MONTH = MONTH ($LV_USER_ENTERED_DATE);

$LV_FSCL_MNTH = CAST (SUBSTR ($PAR_USER_FS_DATE_FORMAT, 1, 2),'INTEGER');

#Fiscal start date corresponding user entered date depends on the month of user entered date and fiscal month

#------------------------------------------------------------------------

IF ($LV_MONTH>=$LV_FSCL_MNTH)

        BEGIN

                $LV_FS_DATE = TO_DATE (YEAR ($PAR_USER_ENTERED_DATE) ||'.' ||                     $PAR_USER_FS_DATE_FORMAT,'YYYY.MM.DD');

END

ELSE

        BEGIN

                $LV_FS_DATE = TO_DATE(YEAR($PAR_USER_ENTERED_DATE)-1||'.' || $PAR_USER_FS_DATE_FORMAT,'YYYY.MM.DD');

END

# Calculate fiscal year is a leap year or not

#----------------------------------------------------------------------------------

$LV_LEAP_YEAR = IFTHENELSE(MOD(YEAR($PAR_USER_ENTERED_DATE),4)=0,1,0);

#Find fiscal and normal day number corresponding to the date

#-----------------------------------------------------------------------------------

$LV_DAY_OF_ACT_DATE = DAY_IN_WEEK($LV_USER_ENTERED_DATE);

$LV_DAY_OF_FSCL_DATE = DAY_IN_WEEK($LV_FS_DATE);

#Fiscal day difference between the user entered date and fiscal year start date.

#---------------------------------------------------------------------------------------------------------------

$LV_FSCL_DAY = FISCAL_DAY($PAR_USER_FS_DATE_FORMAT,$LV_USER_ENTERED_DATE);

#Fiscal week number corresponding to the date

#--------------------------------------------------------------------------------------------------

IF($LV_DAY_OF_FSCL_DATE <=4)

     BEGIN

                IF(($LV_DAY_OF_FSCL_DATE <=2 OR ($LV_DAY_OF_FSCL_DATE =3 AND $LV_LEAP_YEAR=0)) AND $LV_FSCL_DAY >364-$LV_DAY_OF_FSCL_DATE+1)

                    BEGIN

                               $LV_FSCL_ISO_WEEK_NO=1;

                END

                ELSE

                    BEGIN

                               IF($LV_DAY_OF_ACT_DATE>=$LV_DAY_OF_FSCL_DATE)

                                    BEGIN

                                               $LV_FSCL_ISO_WEEK_NO = ($LV_FSCL_DAY-1)/7+1;

                                     END

                                ELSE

                                    BEGIN

                                               $LV_FSCL_ISO_WEEK_NO = (($LV_FSCL_DAY-1)/7)+2;

                                     END

                               END

                 END

ELSE

              IF($LV_DAY_OF_ACT_DATE=1 AND $LV_FSCL_DAY >365)

                   BEGIN

                                $LV_FSCL_ISO_WEEK_NO=1;

              END

                   ELSE

                         BEGIN

                                 IF($LV_DAY_OF_ACT_DATE>=$LV_DAY_OF_FSCL_DATE)

                                      BEGIN

                                                $LV_FSCL_ISO_WEEK_NO = ($LV_FSCL_DAY-1)/7;

                                                        IF($LV_FSCL_ISO_WEEK_NO = 0)

                                                              BEGIN

                                                                     $LV_DAY_OF_PREV_FSCL_YEAR = DAY_IN_WEEK(ADD_MONTHS($LV_FS_DATE,-12));

                                                                        IF($LV_DAY_OF_PREV_FSCL_YEAR<5)

                                                                             BEGIN

                                                                                       $LV_FSCL_ISO_WEEK_NO = 53;

                                                                          END

                                                                          ELSE

                                                                                        $LV_FSCL_ISO_WEEK_NO = 52;

                                                         END

                                   END

                                            ELSE

                                                  BEGIN

                                                             $LV_FSCL_ISO_WEEK_NO = (($LV_FSCL_DAY-1)/7)+1;

                                             END

                     END

RETURN $LV_FSCL_ISO_WEEK_NO;

 

The output of this function will be the ISO week number corresponding to the user entered date and fiscal year format.

We need to call function by using both parameters, if any parameter is null then this function will fail.

FISCAL_ISO_WEEK_NO ($PAR_USER_ENTERED_DATE,$PAR_USER_FS_DATE_FORMAT) Return Int.

If we give fiscal format is ’01.01’ then we will get normal ISO week number.

Consider an example:

Input date is ‘2012.05.12’ and fiscal format is ’07.01’ then ISO week number corresponding to this date will be 45

Thanks

Asgar Ali MK