Home » SQL & PL/SQL » SQL & PL/SQL » getting finacial week number (oracle 11gr2)
getting finacial week number [message #612379] Thu, 17 April 2014 06:09 Go to next message
guddu_12
Messages: 173
Registered: April 2012
Location: UK
Senior Member
Dear All,

I want to populate week number into table for financial year '2014-15' which start from 01-apr-2014 and end on 31-03-2015.

the to_char(date,'ww') do not give appropriate answer as it start from 01-jan where as i want it to start form 01-apr-2014.

How can i achive the running week number from 01-apr-2014 to 31-mar-2015.

Re: getting finacial week number [message #612380 is a reply to message #612379] Thu, 17 April 2014 06:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
CEIL((DT - DATE '2014-04-01') / 7)

SY.
Re: getting finacial week number [message #612381 is a reply to message #612380] Thu, 17 April 2014 06:31 Go to previous messageGo to next message
guddu_12
Messages: 173
Registered: April 2012
Location: UK
Senior Member
but i need to hard code the date in the above answeered , the year will be randaom
Re: getting finacial week number [message #612382 is a reply to message #612381] Thu, 17 April 2014 06:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
And how diffinct that would be???

CEIL((DT - ADD_MONTHS(TRUNC(ADD_MONTHS(DT,-3),'YY'),3)) / 7)

SY.
Re: getting finacial week number [message #612384 is a reply to message #612380] Thu, 17 April 2014 07:21 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Solomon Yakobson wrote on Thu, 17 April 2014 13:17
CEIL((DT - DATE '2014-04-01') / 7)

SY.


Assuming that the year starts on the 1st April 2014 and week numbering is based on ISO, therefore, Monday being the first day of the week and Sunday the end of the week, then I think what you suggest with CEIL will not work as each Monday will have the same week number as the previous week. I think for this problem TRUNC(...)+1 should be used rather than CEIL. The following is a date generator for the whole period, indicating the difference between the result of the CEIL and TRUNC in terms of week numbering.

set linesize 300
alter session set nls_date_format = 'yyyy-mm-dd';
column day_name format a15
--
select
    date '2014-04-01' + (level - 1) dateval,
    to_char(date '2014-04-01' + (level - 1), 'day') day_name,
    to_char(date '2014-04-01' + (level - 1), 'd') day_of_week,
    trunc(level / 7)+1  week_number_trunc,
    ceil(level / 7) week_number_ceil
from
    "PUBLIC".dual
connect by
    level <= date '2015-03-31' - date '2014-04-01' + 1;


And if you compare the 4th and 5th column of the following output for each Monday you can see the difference in terms of the week numbering.

DATEVAL    DAY_NAME        D WEEK_NUMBER_TRUNC WEEK_NUMBER_CEIL
---------- --------------- - ----------------- ----------------
2014-04-01 tuesday         3                 1                1
2014-04-02 wednesday       4                 1                1
2014-04-03 thursday        5                 1                1
2014-04-04 friday          6                 1                1
2014-04-05 saturday        7                 1                1
2014-04-06 sunday          1                 1                1
2014-04-07 monday          2                 2                1
2014-04-08 tuesday         3                 2                2
2014-04-09 wednesday       4                 2                2
2014-04-10 thursday        5                 2                2
2014-04-11 friday          6                 2                2

DATEVAL    DAY_NAME        D WEEK_NUMBER_TRUNC WEEK_NUMBER_CEIL
---------- --------------- - ----------------- ----------------
2014-04-12 saturday        7                 2                2
2014-04-13 sunday          1                 2                2
2014-04-14 monday          2                 3                2
2014-04-15 tuesday         3                 3                3
2014-04-16 wednesday       4                 3                3
2014-04-17 thursday        5                 3                3
2014-04-18 friday          6                 3                3
2014-04-19 saturday        7                 3                3
2014-04-20 sunday          1                 3                3
2014-04-21 monday          2                 4                3
2014-04-22 tuesday         3                 4                4

DATEVAL    DAY_NAME        D WEEK_NUMBER_TRUNC WEEK_NUMBER_CEIL
---------- --------------- - ----------------- ----------------
2014-04-23 wednesday       4                 4                4
2014-04-24 thursday        5                 4                4
2014-04-25 friday          6                 4                4
2014-04-26 saturday        7                 4                4
2014-04-27 sunday          1                 4                4
2014-04-28 monday          2                 5                4
2014-04-29 tuesday         3                 5                5
2014-04-30 wednesday       4                 5                5
2014-05-01 thursday        5                 5                5
2014-05-02 friday          6                 5                5
2014-05-03 saturday        7                 5                5

DATEVAL    DAY_NAME        D WEEK_NUMBER_TRUNC WEEK_NUMBER_CEIL
---------- --------------- - ----------------- ----------------
2014-05-04 sunday          1                 5                5
2014-05-05 monday          2                 6                5
2014-05-06 tuesday         3                 6                6
2014-05-07 wednesday       4                 6                6
2014-05-08 thursday        5                 6                6
2014-05-09 friday          6                 6                6
2014-05-10 saturday        7                 6                6
2014-05-11 sunday          1                 6                6
2014-05-12 monday          2                 7                6
2014-05-13 tuesday         3                 7                7
2014-05-14 wednesday       4                 7                7

DATEVAL    DAY_NAME        D WEEK_NUMBER_TRUNC WEEK_NUMBER_CEIL
---------- --------------- - ----------------- ----------------
2014-05-15 thursday        5                 7                7
2014-05-16 friday          6                 7                7
2014-05-17 saturday        7                 7                7
2014-05-18 sunday          1                 7                7
2014-05-19 monday          2                 8                7
2014-05-20 tuesday         3                 8                8
2014-05-21 wednesday       4                 8                8
2014-05-22 thursday        5                 8                8
2014-05-23 friday          6                 8                8
2014-05-24 saturday        7                 8                8
2014-05-25 sunday          1                 8                8

DATEVAL    DAY_NAME        D WEEK_NUMBER_TRUNC WEEK_NUMBER_CEIL
---------- --------------- - ----------------- ----------------
2014-05-26 monday          2                 9                8
2014-05-27 tuesday         3                 9                9
2014-05-28 wednesday       4                 9                9
2014-05-29 thursday        5                 9                9
2014-05-30 friday          6                 9                9
2014-05-31 saturday        7                 9                9
2014-06-01 sunday          1                 9                9
2014-06-02 monday          2                10                9
2014-06-03 tuesday         3                10               10
2014-06-04 wednesday       4                10               10
2014-06-05 thursday        5                10               10
          .
          .
          .

[Updated on: Thu, 17 April 2014 07:29]

Report message to a moderator

Re: getting finacial week number [message #612390 is a reply to message #612384] Thu, 17 April 2014 07:48 Go to previous messageGo to next message
guddu_12
Messages: 173
Registered: April 2012
Location: UK
Senior Member
Very nice answer, much appriciated
Re: getting finacial week number [message #612393 is a reply to message #612384] Thu, 17 April 2014 09:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
dariyoosh wrote on Thu, 17 April 2014 08:21
And if you compare the 4th and 5th column of the following output for each Monday you can see the difference in terms of the week numbering.


Solution I posted assumes week 1 of fiscal year starts April 1. Your solution is totally wrong. The only reason it shows correct results for 2014 is April 1 is Tuesday. Look what happens for 2016:

select
    date '2016-04-01' + (level - 1) dateval,
    to_char(date '2016-04-01' + (level - 1), 'day') day_name,
    to_char(date '2016-04-01' + (level - 1), 'd') day_of_week,
    trunc(level / 7)+1  week_number_trunc,
    ceil(level / 7) week_number_ceil
from
    "PUBLIC".dual
connect by
    level <= date '2017-04-30' - date '2016-04-01' + 1
/

DATEVAL    DAY_NAME        D WEEK_NUMBER_TRUNC WEEK_NUMBER_CEIL
---------- --------------- - ----------------- ----------------
2016-04-01 friday          6                 1                1
2016-04-02 saturday        7                 1                1
2016-04-03 sunday          1                 1                1
2016-04-04 monday          2                 1                1
2016-04-05 tuesday         3                 1                1
2016-04-06 wednesday       4                 1                1
2016-04-07 thursday        5                 2                1
2016-04-08 friday          6                 2                2
2016-04-09 saturday        7                 2                2
2016-04-10 sunday          1                 2                2
2016-04-11 monday          2                 2                2


Now your week 2 starts Thursday. Also, your query will continue week numbering if connect by spans multiple fiscal years while we need to reset week numer to 1. If week starts monday:

CEIL((DT - TRUNC(ADD_MONTHS(TRUNC(ADD_MONTHS(DT,-3),'YY'),3),'IW') + 1) / 7)


For example:

WITH T AS (
           SELECT  DATE '2016-03-31' + LEVEL DT
             FROM  DUAL
             CONNECT BY LEVEL <= 400
          )
SELECT  DT,
        TO_CHAR(DT,'FMDay') DAY_NAME,
        CEIL((DT - TRUNC(ADD_MONTHS(TRUNC(ADD_MONTHS(DT,-3),'YY'),3),'IW') + 1) / 7) WEEK_NUMBER
  FROM  T
/
 10  /

DT         DAY_NAME        WEEK_NUMBER
---------- --------------- -----------
2016-04-01 Friday                    1
2016-04-02 Saturday                  1
2016-04-03 Sunday                    1
2016-04-04 Monday                    2
2016-04-05 Tuesday                   2
2016-04-06 Wednesday                 2
2016-04-07 Thursday                  2
2016-04-08 Friday                    2
2016-04-09 Saturday                  2
2016-04-10 Sunday                    2
2016-04-11 Monday                    3
.
.
.

DT         DAY_NAME        WEEK_NUMBER
---------- --------------- -----------
2017-03-19 Sunday                   51
2017-03-20 Monday                   52
2017-03-21 Tuesday                  52
2017-03-22 Wednesday                52
2017-03-23 Thursday                 52
2017-03-24 Friday                   52
2017-03-25 Saturday                 52
2017-03-26 Sunday                   52
2017-03-27 Monday                   53
2017-03-28 Tuesday                  53
2017-03-29 Wednesday                53

DT         DAY_NAME        WEEK_NUMBER
---------- --------------- -----------
2017-03-30 Thursday                 53
2017-03-31 Friday                   53
2017-04-01 Saturday                  1
2017-04-02 Sunday                    1
2017-04-03 Monday                    2
2017-04-04 Tuesday                   2
2017-04-05 Wednesday                 2
2017-04-06 Thursday                  2
2017-04-07 Friday                    2
2017-04-08 Saturday                  2
2017-04-09 Sunday                    2

DT         DAY_NAME        WEEK_NUMBER
---------- --------------- -----------
2017-04-10 Monday                    3
2017-04-11 Tuesday                   3
2017-04-12 Wednesday                 3
2017-04-13 Thursday                  3
2017-04-14 Friday                    3
2017-04-15 Saturday                  3
2017-04-16 Sunday                    3
2017-04-17 Monday                    4
2017-04-18 Tuesday                   4
2017-04-19 Wednesday                 4
2017-04-20 Thursday                  4

DT         DAY_NAME        WEEK_NUMBER
---------- --------------- -----------
2017-04-21 Friday                    4
2017-04-22 Saturday                  4
2017-04-23 Sunday                    4
2017-04-24 Monday                    5
2017-04-25 Tuesday                   5
2017-04-26 Wednesday                 5
2017-04-27 Thursday                  5
2017-04-28 Friday                    5
2017-04-29 Saturday                  5
2017-04-30 Sunday                    5
2017-05-01 Monday                    6

DT         DAY_NAME        WEEK_NUMBER
---------- --------------- -----------
2017-05-02 Tuesday                   6
2017-05-03 Wednesday                 6
2017-05-04 Thursday                  6
2017-05-05 Friday                    6

400 rows selected.

SQL> 


SY.
Re: getting finacial week number [message #612404 is a reply to message #612379] Thu, 17 April 2014 12:52 Go to previous messageGo to next message
are_we_there_yeti
Messages: 2
Registered: April 2014
Location: South Carolina
Junior Member
Sounds like you want an ISO date.
http://www.epochconverter.com/epoch/weeknumbers.php

...if so, use a date format of 'IW'
select to_char(sysdate,'IW') from dual;

[Updated on: Thu, 17 April 2014 12:55]

Report message to a moderator

Re: getting finacial week number [message #612405 is a reply to message #612404] Thu, 17 April 2014 14:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sounds like you are wrong.

Re: getting finacial week number [message #612406 is a reply to message #612393] Thu, 17 April 2014 14:58 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Solomon Yakobson wrote on Thu, 17 April 2014 16:24
Solution I posted assumes week 1 of fiscal year starts April 1. Your solution is totally wrong. The only reason it shows correct results for 2014 is April 1 is Tuesday. Look what happens for 2016:

. . .

Also, your query will continue week numbering if connect by spans multiple fiscal years while we need to reset week numer to 1


Thanks a lot for pointing out my error.
Testing it for other years, as you said, my solution is completely a mess ! Sad Sad

Just to make sure that I understand your solution. When you write
. . .
trunc(add_months(trunc(add_months(t1.dateval, -3), 'yy'), 3), 'iw')
. . .


If I split it into parts what I understand is:

add_months(t1.dateval, -3)

Goes back 3 months for each date. Then
trunc(add_months(t1.dateval, -3), 'yy')

For example if the year is 2014 this gives: 2014-01-01 (and gives this value until 2015-03-31 where the fiscal year ends given the fact that in this question the end of the Gregorian year is not the same as the end of the fiscal year)
add_months(trunc(add_months(t1.dateval, -3), 'yy'), 3)

Again, for example if the year is 2014 this gives: the beginning date of the fiscal year: 2014-04-01 (and gives this value until 2015-03-31 where the fiscal year ends given the fact that in this question the end of the Gregorian year is not the same as the end of the fiscal year).

And finally
trunc(add_months(trunc(add_months(t1.dateval, -3), 'yy'), 3), 'iw')

Gives the date of the 1st Monday of the 1st week of the fiscal year (the week that starts according to ISO by Monday & includes the 1st of April, given the fact that trunc argument is provided with the format 'iw')

Is my understanding of this part of your solution correct?

Thanks,
Re: getting finacial week number [message #612407 is a reply to message #612406] Thu, 17 April 2014 15:26 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
trunc(add_months(trunc(add_months(t1.dateval, -3), 'yy'), 3), 'iw')

doesn't give "the date of the 1st Monday of the 1st week of the fiscal year". It gives last monday prior or equal to 1st day of fiscal year. This is point of reference. It makes sure only remainder of the week which started in prior fiscal year but ended in current fiscal year is marked as week 1.

SY.
Re: getting finacial week number [message #612408 is a reply to message #612405] Thu, 17 April 2014 15:43 Go to previous messageGo to next message
are_we_there_yeti
Messages: 2
Registered: April 2014
Location: South Carolina
Junior Member
Aw.... I didn't RTFQ. I saw financial week and jumped to the wrong conclusion.

You are right, I am wrong.

I vote myself off this particular island.
Re: getting finacial week number [message #612409 is a reply to message #612407] Thu, 17 April 2014 16:21 Go to previous message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Solomon Yakobson wrote on Thu, 17 April 2014 22:26
... It gives last monday prior or equal to 1st day of fiscal year ...


Thanks a lot for the clarification. Smile
Previous Topic: Need to get max update date time last 7 days
Next Topic: sp_log_entry
Goto Forum:
  


Current Time: Tue Sep 16 18:47:53 CDT 2014

Total time taken to generate the page: 0.10163 seconds