Help on Creating Fiscal Year Periods

  • Hi,

    I am developing a financial application using SQLServer 2008 as database and I want to generate fiscal periods of any year for example

    Input : June 2010

    Results desired

    Period…………….Start…………………………………..End

    1…………………1-06-2010……………………………30-06-2010

    2................1-07-2010........................31-07-2010

    And so on untill May 2010 …Actually need to get 12 rows (12 months) starting from any month and output will be the starting date of the month and end would be the last day of the month like for example in my scenario June 2010 - May 2011 ..what will be the query.

    Thanks in Advance

  • Check out this article. It should help.

    http://www.sqlservercentral.com/articles/T-SQL/70482/

  • Thanks for the link but this is not my requirement i mean Calendar table and all. I just want simple query to achieve this like for example.

    DECLARE @mydate DATETIME

    SELECT @mydate = GETDATE()

    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,

    'Last Day of Previous Month'

    UNION

    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,

    'First Day of Current Month' AS Date_Type

    UNION

    SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type

    UNION

    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,

    'Last Day of Current Month'

    UNION

    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,

    'First Day of Next Month'

    The above query is returning only one row with the desired result..how can i make it for 12 months by providing input of the starting month..

  • Is this what you want?

    DECLARE @mydate DATETIME

    SELECT @mydate = GETDATE()

    DECLARE @StartDate datetime

    SELECT @StartDate = DATEADD(month, DATEDIFF(month, 0, @mydate), 0)

    SELECT

    number AS [Period],

    DATEADD(month, number - 1, @StartDate) AS [Start],

    DATEADD(day, -1, DATEADD(month, number, @StartDate)) AS [End]

    FROM (

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7 UNION ALL

    SELECT 8 UNION ALL

    SELECT 9 UNION ALL

    SELECT 10 UNION ALL

    SELECT 11 UNION ALL

    SELECT 12

    ) N(number)

  • Just so I understand...

    You are developing a financial application and want something to work for now, hoping it will work in the future? I have developed financial applications and find that proven patterns solve way more problems than you can even anticipate. If this is a long-term application that will do many reports or screens based on the same periods, the recommendation for the calendar table is really your best bet. It pretty much guarantess consistant results everywhere you may need that informaiton.

    Just my two cents worth.

  • andrewd.smith Thanks for the answer exactly I was looking for. Hats off to ya 🙂

  • jerry-621596 (8/19/2010)


    Just so I understand...

    You are developing a financial application and want something to work for now, hoping it will work in the future? I have developed financial applications and find that proven patterns solve way more problems than you can even anticipate. If this is a long-term application that will do many reports or screens based on the same periods, the recommendation for the calendar table is really your best bet. It pretty much guarantess consistant results everywhere you may need that informaiton.

    Just my two cents worth.

    Jerry thank you for the advise actually I have short time and my financial year has 12 periods (months) which i am using to (open, close & current) to avoid wrong postings and inconsistent results. I would appreciate if you can explain the usage of calendar so I can use it later in my application.

Viewing 7 posts - 1 through 6 (of 6 total)

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