Calculating company's fiscal year

  • Hi,

    I have to write an sp in sql that calculates the company's fiscal month to date .

    The fiscal year starts from '3/27/2009' for this year . A week always begins on a Friday .

    Below are the list of fiscal months .

    BeginsEnds

    27-Mar23-Apr

    24-Apr21-May

    22-May25-Jun

    26-Jun23-Jul

    24-Jul20-Aug

    21-Aug24-Sep

    25-Sep22-Oct

    23-Oct19-Nov

    20-Nov24-Dec

    25-Dec21-Jan

    22-Jan18-Feb

    19-Feb25-Mar

    I have done a part of the sp which calculates weektodate. This is working fine .

    CREATE Procedure ABCD

    @DAteType varchar(40)

    AS

    BEGIN

    DECLARE @StartDate DATETIME

    DECLARE @EndDate DATETIME

    IF @DateType = 'WeekToDate'

    BEGIN

    SET DATEFIRST 5;

    -- Set it to the friday

    SET @StartDate = (SELECT DISTINCT DATEADD(day,(DATEPART(weekday,GETDATE())*-1)+1,GETDATE()) )

    SET @EndDate = GETDATE()

    END

    END

    .....Followed by the rest of the procedure .

    How Do I calculate the MonthToDate ?

    Help appreciated .

  • What is the business rule to calculate the change to the next business month?

    That's what I figured so far:

    a) It's not adding 4 weeks (would leed to June 19th instead of June 26th)

    b) It's not the last Friday of each month (would be May 29th)

    c) it's not adding one week per quarter (would be August 28th)

    So, please help us help you.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • What have you tried so far for the fiscal month solution?

    Is the list of Fiscal Months static from year to year? If yes, you could potentially hard code those into the proc. If yes or no, you could put the fiscal month begin and end dates into a lookup table and key off that in the code.

    There could be several ways to perform the calculation for Fiscal Month Sums. It is easier to see what you have tried with it, in order to give adequate direction.

    I.E. you might use temp tables, CTEs, variables, and date manipulation such as you have for the weeks.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • No. The fiscal month is not static year to year . For 2010 its 3/26/2010 .

  • K - so that drives more towards having a lookup table for the fiscal month dates (my opinion).

    So what all have you tried to make the summing of months work? Please include sql and table structures where applicable.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • The business rule is

    Begins Ends

    27-Mar 23-Apr ( 4 weeks)

    24-Apr 21-May ( 4 weeks)

    22-May 25-Jun ( 5 weeks)

    26-Jun 23-Jul ( 4 weeks)

    24-Jul 20-Aug ( 4 weeks)

    21-Aug 24-Sep ( 5 weeks)

    25-Sep 22-Oct ( 4 weeks)

    23-Oct 19-Nov ( 4 weeks)

    20-Nov 24-Dec ( 5 weeks)

    25-Dec 21-Jan ( 4 weeks)

    22-Jan 18-Feb ( 4 weeks)

    19-Feb 25-Mar( 5 weeks)

  • Panchi (9/15/2009)


    No. The fiscal month is not static year to year . For 2010 its 3/26/2010 .

    I figured that one... (assuming it's always the last Friday of March).

    In this case FY2011 would start on March 25th, right? ... Or is it April 1st (which is a Friday, as well... - then, the business rule would be "Friday before April 2nd...", not "last Friday in March")

    I don't think it'll help to answer the question regarding the business rules for each month when the answer is related to the start of the next fiscal year, just given as a plain date again without any business rule....

    At this point there's very little I can do...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • First of all: my previous post didn't include your last post. So please disregard, since you provided the data I asked for.

    Following please find a proposal to calculate your business month.

    However, I'd like to second CirquedeSQLeils proposal to use a lookup table.

    The code I'm proposing could be used to fill/update a calendar table.

    The code brings up another issue: is it correct that the Fiscal Year 2012 will start on March 23rd and not on March 30th? If March 30th would be correct, the logic supplied doesn't match....)

    DECLARE @fy CHAR (4),

    @fystart DATETIME

    SELECT @fy ='2009' -- start fiscal year

    SET @fystart = dateadd(dd, - datepart(dw,@fy+'/04/01')+ 1, @fy+'/04/01')

    IF (SELECT datepart(mm,@fystart))= 4

    SET @fystart = dateadd(wk,-1,@fystart)

    ;WITH

    cte_numbers (N) AS -- numbers table for the next three year (=36 month)

    (

    SELECT TOP 36 ROW_NUMBER () OVER(ORDER BY object_id) FROM sys.columns

    )

    SELECT

    datepart(year,dateadd(yy,(n-1)/12,@fy)) AS FiscalYear,

    CASE WHEN ((n + 2) / 3) % 4 = 0 THEN 4 ELSE ((n + 2) / 3) % 4 END AS fy_Quarter,

    CASE WHEN n % 12 = 0 THEN 12 ELSE n % 12 END AS fy_month,

    dateadd(wk,(n-1)*4 + datediff(wk,@fystart,dateadd(wk,(n-1)*4,@fystart))/12,@fystart) AS fy_month_start,

    dateadd(dd,-1,dateadd(wk,(n)*4 + datediff(wk,@fystart,dateadd(wk,(n)*4,@fystart))/12,@fystart)) AS fy_month_end

    FROM cte_numbers



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thank You for your reply . Your solution shows fy_month_start =3/29/2009 and fy_month_end ='4/25/2009' but its supposed to be '3/27/2009' and fy_month_end ='4/23/2009' .

    For 2010 the fiscal year starts from March,26th.

    But for the corresponding years 2011 and later , it has not been decided which months will have 5 weeks .

  • Panchi (9/15/2009)


    Thank You for your reply . Your solution shows fy_month_start =3/29/2009 and fy_month_end ='4/25/2009' but its supposed to be '3/27/2009' and fy_month_end ='4/23/2009' .

    For 2010 the fiscal year starts from March,26th.

    But for the corresponding years 2011 and later , it has not been decided which months will have 5 weeks .

    addSET datefirst 5at the beginning of the sample code. (missed it when I copied the sample). Sorry about that... :crying:



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thank You . It works fine .

  • Panchi (9/15/2009)


    Thank You for your reply . Your solution shows fy_month_start =3/29/2009 and fy_month_end ='4/25/2009' but its supposed to be '3/27/2009' and fy_month_end ='4/23/2009' .

    For 2010 the fiscal year starts from March,26th.

    But for the corresponding years 2011 and later , it has not been decided which months will have 5 weeks .

    The bolded part is the key fact - it indicates that there is no set of business rules to model. More strictly, it means the calendar is not mechanistically determinable in the general case. Which, of course, means you can't write code to pre-generate the calendar. Ultimately, you're always going to end up having to enter a list of dates in your situation. Some years, it may be possible to write code to generate that list, but others, it may not.

    ______
    Twitter: @Control_Group

  • Thanks .

  • Panchi (9/15/2009)


    The business rule is

    Begins Ends

    27-Mar 23-Apr ( 4 weeks)

    24-Apr 21-May ( 4 weeks)

    22-May 25-Jun ( 5 weeks)

    26-Jun 23-Jul ( 4 weeks)

    24-Jul 20-Aug ( 4 weeks)

    21-Aug 24-Sep ( 5 weeks)

    25-Sep 22-Oct ( 4 weeks)

    23-Oct 19-Nov ( 4 weeks)

    20-Nov 24-Dec ( 5 weeks)

    25-Dec 21-Jan ( 4 weeks)

    22-Jan 18-Feb ( 4 weeks)

    19-Feb 25-Mar( 5 weeks)

    You've already done the hard part... just put that information into a table and you're done.;-)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply