If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > DATEADD Issues - Fiscal Dates vs Calendar Dates

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 147
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?
Reply With Quote
  #2 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,744
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.
Reply With Quote
  #3 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,744
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 147
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.
Reply With Quote
  #5 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,744
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 291
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,
...);
Reply With Quote
  #7 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,744
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.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 147
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.
Reply With Quote
  #9 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,744
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.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 147
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.
Reply With Quote
  #11 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,744
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.
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Oct 2010
Location: Atlanta, GA
Posts: 147
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.
Reply With Quote
  #13 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,744
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On