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.

 > DATEADD Issues - Fiscal Dates vs Calendar Dates

 clawlan Registered User Join Date: Oct 2010 Location: Atlanta, GA Posts: 154
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?
 Pat Phelan Resident Curmudgeon Join Date: Feb 2004 Location: In front of the computer Posts: 14,810
 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.
 Pat Phelan Resident Curmudgeon Join Date: Feb 2004 Location: In front of the computer Posts: 14,810
 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.
 clawlan Registered User Join Date: Oct 2010 Location: Atlanta, GA Posts: 154
 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.
 Pat Phelan Resident Curmudgeon Join Date: Feb 2004 Location: In front of the computer Posts: 14,810
 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.
 Celko Registered User Join Date: Jan 2013 Posts: 300
 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, ...);
 Pat Phelan Resident Curmudgeon Join Date: Feb 2004 Location: In front of the computer Posts: 14,810
 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.
 clawlan Registered User Join Date: Oct 2010 Location: Atlanta, GA Posts: 154
 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.
 Pat Phelan Resident Curmudgeon Join Date: Feb 2004 Location: In front of the computer Posts: 14,810
 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.
 clawlan Registered User Join Date: Oct 2010 Location: Atlanta, GA Posts: 154
Quote:
 Originally Posted by Pat Phelan 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.
 Pat Phelan Resident Curmudgeon Join Date: Feb 2004 Location: In front of the computer Posts: 14,810
 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.
 clawlan Registered User Join Date: Oct 2010 Location: Atlanta, GA Posts: 154
 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.