Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    168

    Question DATEADD Issues - Fiscal Dates vs Calendar Dates

    I discovered an annoying little issue in a function I wrote. I am trying to calculate a 12 fiscal month period to track sales within. I want the 12 month period to end 2 month ago, and start 12 months prior to that:

    Today is FY2013 FM2
    My 12 month timing would end: FY2012 FM12 (2 fiscal months ago)
    My 12 month timing would start: FY2012 FM01

    So calc sales 201201 to 201212. Makes sense?

    I am calculating this by using today's calendar month and using the DATEADD function: DATEADD(month, -2, getdate())

    Trouble is, there are circumstances where the calendar date calculated from this is not 2 fiscal months ago because our fiscal calendar is not in sync with the normal calendar.

    I was thinking doing calcs just on the month number and year number but that gets complicated when you have to worry about rolling over from 12 to 1 and what not.

    Any ideas on how to solve this issue?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    So something like:
    Code:
    DECLARE @d DATETIME
    SET @d = GetDate()
    
    SELECT DateAdd(m, DateDiff(m, 424, @d), 0) AS lastYear
    ,  DateAdd(m, DateDiff(m, 59, @d), 0) AS thisYear
    That way you could find data that was lastYear <= targetDate < thisYear ?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    Maybe you should lay out a table of 24 rows with Current, begin, and end dates. I'm not sure that I understand what you want, but once I do I'm positive that I can craft an expression to render it for you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    168
    Thanks for the help Pat. Here is my actual code:

    Code:
    DECLARE @startDtKey int
      DECLARE @endDtKey int
     
      SET @endDtKey = 
        (Select max(FiscalDayKey)
        from PRODDW.dbo.vwdfiscalday
        where fiscalyrmth = (Select fiscalyrmth
                            from PRODDW.dbo.vwdfiscalday
                            where cast(fiscaldt as date) = cast(DATEADD(month, -2, getdate()) as date)))
    
      SET @startDtKey = 
        (Select min(FiscalDayKey)
        from PRODDW.dbo.vwdfiscalday
        where fiscalyrmth = (Select fiscalyrmth
                            from PRODDW.dbo.vwdfiscalday
                            where cast(fiscaldt as date) = cast(DATEADD(month, -13, getdate()) as date)))

    What I am doing is looking at today's date minus 2 months. Then using that date to reference the fiscalYrMth that corresponds to it. I then determine the MAX fiscaldaykey for that fiscalYrMth. That gives me the last fiscal day of the fiscalYrMth. Then I do the same thing for 13 months ago, this time taking the MIN fiscaldaykey, giving me the first day of the 12 month period.

    I then use those 2 days in a WHERE clause to determine sales like this:
    Code:
    SELECT sum(sales)
    FROM salesTable
    WHERE fiscaldaykey between @startDtKey AND @endDtKey
    But as I stated earlier, there are cases, like I ran into today, where the calendar date calcs dont correspond to a 12 month fiscal period. When I select the FiscalYrMth using DATEADD -13, I get 201112. It should ideally be 201201 because 201112 to 201212 would be calculating sales within a 13 month period, not a 12 month period.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    If you use <= for the start (which I would expect), and only use < for the end (which I would expect), then you'll be fine.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2013
    Posts
    306
    Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math.


    CREATE TABLE Calendar
    (cal_date DATE NOT NULL PRIMARY KEY,
    fiscal_year SMALLINT NOT NULL,

    fiscal_month SMALLINT NOT NULL,
    week_in_year SMALLINT NOT NULL, -- SQL Server is not ISO standard
    holiday_type SMALLINT NOT NULL
    CHECK(holiday_type IN ( ..), --
    day_in_year
    SMALLINT NOT NULL,
    julian_business_day INTEGER NOT NULL,
    ...);

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    At least i don't see this question as the kind of problem that a calendar table can or will fix.. If you look at the code posted, this is already a data warehouse with a calendar table but that isn't enough to fix the underlying problem(s) that the OP (Original Poster) is trying to solve.

    Once they lay out a table that shows what they want to accomplish, I think that the solution will be simple... My problem is getting to the meat of the problem to figure out what they want!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    168
    I guess my only option is logic on the month and year. i can pull the current fiscal month and yr, and ill just build logic to subtract from those numbers, taking into account rolling from 12 back to 1, to get the start and end YrMonths.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    What begin and end dates do you want for the following input dates: '2013-01-01 08:15', '2013-01-31 13:50', '2013-02-28 23:59', '2013-03-01 00:00'

    This isn't hard to do, I just need to understand what you want!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    168
    Quote Originally Posted by Pat Phelan View Post
    What begin and end dates do you want for the following input dates: '2013-01-01 08:15', '2013-01-31 13:50', '2013-02-28 23:59', '2013-03-01 00:00'

    This isn't hard to do, I just need to understand what you want!

    -PatP
    Pat, it's easy to use dateadd to find these -2 months, -13 months. The issue is that our fiscal calender and regular calender do not always match up. For your example: 2013-1-1, I have to check what the fiscal yr and mth are for that calendar date, which may or may not be 2013 and 1 respectively. I think teh only real way to accomplish this is manipulating the fiscal yr and mth as integers. i have the logic almost done now.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    d1 and d2 are what you asked for, d3 and d4 are what I suspect that you want.
    Code:
    DECLARE @t TABLE (
       input_date	DATETIME
       )
    
    INSERT INTO @t (input_date)
       VALUES ('2013-01-01 08:15'), ('2013-01-31 13:50')
    ,     ('2013-02-28 23:59'), ('2013-03-01 00:00')
    
    SELECT input_date
    ,  DateAdd(m, -13, input_date) AS d1
    ,  DateAdd(m,  -2, input_date) AS d2
    ,  DateAdd(m, DateDiff(m, 395, input_date), 0) AS d3
    ,  DateAdd(m, DateDiff(m,  58, input_date), 0) AS d4
       FROM @t
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    168
    Pat, you are still not getting the issue. Your logic works if I was only using calendar date. But my start and end dates have to be based on fiscal date. DateADD is used to perform mathematical calc on a date. Logically, if I dateADD todays date minus 2 months, that will get me 2 months ago, but that MAY NOT be 2 fiscal months ago.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    I've dealt with quite literally thousands of fiscal calendars. I used to work for a large accounting firm that specialized in entrepreneurs. The firms that they ran had many fiscal calendars (as did our accounting firm), and I never encountered any calendar that was difficult to cope with once you understood the rules.

    It may be easier for you to craft a solution that suits your needs than it is for you to explain those needs.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •