SQL Server Performance

Financial Year calculation

Discussion in 'General Developer Questions' started by trabant, May 12, 2006.

  1. trabant New Member

    Hi guys,

    This is probably a fairly simple query but I am really struggling with it!!

    I am trying to calculate a financial year to date flag for a time dimension.

    The time dimension holds dates from 1 Jan 1998 up to 31 Dec 2020.

    The financial year starts on the 17 April and runs for 52 weeks. In terms of calculating the YTD when I am still in the current year, I can make that work. However, my issue is with dealing with dates that are not in the same year as the start date of the financial year.

    For example: April 1 2006 is financial year 2005. I tried to take the approach below but it flags everything from the start of last years financial year up to today as the current financial year to date - not just everything from 17 April this year.

    case
    when d_time_caldate < cast(cast(year(getdate()) as varchar(4)) +'-' + '4' + '-' + '17' as datetime) and d_time_caldate between cast(cast(year(getdate())-1 as varchar(4)) +'-' + '4' + '-' + '17' as datetime) and getdate() then 'Y'
    when d_time_caldate between cast(cast(year(getdate()) as varchar(4)) +'-' + '4' + '-' + '17' as datetime) and getdate() then 'Y' else 'N' end

    I know the issue is with the first section of the case statement - I just can't work out how to make the code do this correctly!!

    Any comments/assistance would be greatly appreciated.....

    Thanks all!!




    Petew
  2. ranjitjain New Member

    check this:

    declare @caldate1 datetime,@caldate2 datetime
    select @caldate1='2006-04-16',@caldate2='2006-04-21'
    select case when @caldate1 between cast(convert(varchar(4),year(getdate()))+'-04'+'-17' as datetime) and getdate()
    then 'y' else 'n' end,
    case when @caldate2 between cast(convert(varchar(4),year(getdate()))+'-04'+'-17' as datetime) and getdate()
    then 'y' else 'n' end

  3. FrankKalis Moderator

    If I understand this correctly, this should give you some ideas:


    DECLARE @date_in DATETIME
    DECLARE @date_not_in DATETIME
    SELECT @date_in = '20070401', @date_not_in = '20070418'
    SELECT
    CASE
    WHEN @date_in BETWEEN '20060417' AND DATEADD(WEEK, 52, '20060417')
    THEN 'Date is in Financial Year 2006'
    ELSE 'Date is not in Financial Year 2006'
    END
    , CASE
    WHEN @date_not_in BETWEEN '20060417' AND DATEADD(WEEK, 52, '20060417')
    THEN 'Date is in Financial Year 2006'
    ELSE 'Date is not in Financial Year 2006'
    END


    ---------------------------------- ----------------------------------
    Date is in Financial Year 2006 Date is not in Financial Year 2006

    (1 row(s) affected)

    In production you would concatenate the string in the BETWEEN and CAST them to DATETIME to parameterize the query.


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  4. FrankKalis Moderator

  5. trabant New Member

    Guys,

    Thanks for this (especially the inspiration from Frank!).....

    Final solution as follows....The post from Frank reminded me that life becomes much easier when you know what the start date of your year is.....
    -------------------
    declare @currfinyear int
    declare @currfinyearstart datetime

    --Work out the current financial Year
    select @currfinyear = (select case when getdate() < cast(cast(year(getdate()) as varchar(4)) +'-' + '4' + '-' + '17' as datetime) then
    year(getdate()) -1 else year(getdate()) end)

    ---Calculate my current financial year start date
    select @currfinyearstart = ( select cast(cast(@currfinyear as varchar(4)) +'-' + '4' + '-' + '17' as datetime))

    select @currfinyear,@currfinyearstart

    select d_time_caldate,
    case
    when d_time_caldate between @currfinyearstart and getdate() then
    'Y' else 'N' end
    from d_time

    --------------------
    This happily gives me flags to identify the current financial year to date.

    Of course.......it only works properly if the financial year starts on 17 April every year. I am going to build up a table the calculates the start and end dates for the financial years correctly (ie: Find out the start date of the first financial year and then increment years in 52 week chunks to get more accurate start and end dates)

    Cheers all


    Petew
  6. trabant New Member

    All,

    In case you're interested, I tidied things up and played around with the code.

    Rather than create a new table just to hold start and end dates for financial years, I have used the below to calculate on the fly based on the date entry in the d_time_caldate field:

    --@currfinsource is the start date for all of the calculations. This could be set to any start --date required.
    select @currfinsource = '04-18-2005'

    select @currfinyearstart = (dateadd(week,52 * (year(@currfinsource) * -1 + year(getdate())),@currfinsource))

    select @currfinyearstart
    if getdate() < @currfinyearstart
    begin
    select @currfinyearstart = dateadd(week,-52,@currfinyearstart)
    end

    select @currfinyear = year(@currfinyearstart)

    select
    case
    when d_time_caldate between @currfinyearstart and getdate() then
    'Y' else 'N' end,
    --Which financial year does this date belong to
    year(
    case when
    d_time_caldate < dateadd(week,52 * (year(@currfinsource) * -1 + year(d_time_caldate)),@currfinsource)
    then dateadd(week,-52,dateadd(week,52 * (year(@currfinsource) * -1 + year(d_time_caldate)),@currfinsource))
    else
    dateadd(week,52 * (year(@currfinsource) * -1 + year(d_time_caldate)),@currfinsource)
    end
    )

    Petew
  7. cmdr_skywalker New Member

    What don't you update your time dimension with fiscal columns and use the queries above to update your dimension? Our time dimension does not only contain calendar dates but also fiscal dates.



    May the Almighty God bless us all!
    www.empoweredinformation.com
  8. FrankKalis Moderator

Share This Page