1. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213

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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

3. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

4. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

6. Registered User
Join Date
Jan 2013
Posts
359
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,
...);

7. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

8. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

10. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213
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.

11. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

12. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579