September 15, 2009 at 1:03 pm
Hi,
I have to write an sp in sql that calculates the company's fiscal month to date .
The fiscal year starts from '3/27/2009' for this year . A week always begins on a Friday .
Below are the list of fiscal months .
BeginsEnds
27-Mar23-Apr
24-Apr21-May
22-May25-Jun
26-Jun23-Jul
24-Jul20-Aug
21-Aug24-Sep
25-Sep22-Oct
23-Oct19-Nov
20-Nov24-Dec
25-Dec21-Jan
22-Jan18-Feb
19-Feb25-Mar
I have done a part of the sp which calculates weektodate. This is working fine .
CREATE Procedure ABCD
@DAteType varchar(40)
AS
BEGIN
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
IF @DateType = 'WeekToDate'
BEGIN
SET DATEFIRST 5;
-- Set it to the friday
SET @StartDate = (SELECT DISTINCT DATEADD(day,(DATEPART(weekday,GETDATE())*-1)+1,GETDATE()) )
SET @EndDate = GETDATE()
END
END
.....Followed by the rest of the procedure .
How Do I calculate the MonthToDate ?
Help appreciated .
September 15, 2009 at 1:36 pm
What is the business rule to calculate the change to the next business month?
That's what I figured so far:
a) It's not adding 4 weeks (would leed to June 19th instead of June 26th)
b) It's not the last Friday of each month (would be May 29th)
c) it's not adding one week per quarter (would be August 28th)
So, please help us help you.
September 15, 2009 at 1:40 pm
What have you tried so far for the fiscal month solution?
Is the list of Fiscal Months static from year to year? If yes, you could potentially hard code those into the proc. If yes or no, you could put the fiscal month begin and end dates into a lookup table and key off that in the code.
There could be several ways to perform the calculation for Fiscal Month Sums. It is easier to see what you have tried with it, in order to give adequate direction.
I.E. you might use temp tables, CTEs, variables, and date manipulation such as you have for the weeks.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 15, 2009 at 1:46 pm
No. The fiscal month is not static year to year . For 2010 its 3/26/2010 .
September 15, 2009 at 1:55 pm
K - so that drives more towards having a lookup table for the fiscal month dates (my opinion).
So what all have you tried to make the summing of months work? Please include sql and table structures where applicable.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 15, 2009 at 1:59 pm
The business rule is
Begins Ends
27-Mar 23-Apr ( 4 weeks)
24-Apr 21-May ( 4 weeks)
22-May 25-Jun ( 5 weeks)
26-Jun 23-Jul ( 4 weeks)
24-Jul 20-Aug ( 4 weeks)
21-Aug 24-Sep ( 5 weeks)
25-Sep 22-Oct ( 4 weeks)
23-Oct 19-Nov ( 4 weeks)
20-Nov 24-Dec ( 5 weeks)
25-Dec 21-Jan ( 4 weeks)
22-Jan 18-Feb ( 4 weeks)
19-Feb 25-Mar( 5 weeks)
September 15, 2009 at 2:02 pm
Panchi (9/15/2009)
No. The fiscal month is not static year to year . For 2010 its 3/26/2010 .
I figured that one... (assuming it's always the last Friday of March).
In this case FY2011 would start on March 25th, right? ... Or is it April 1st (which is a Friday, as well... - then, the business rule would be "Friday before April 2nd...", not "last Friday in March")
I don't think it'll help to answer the question regarding the business rules for each month when the answer is related to the start of the next fiscal year, just given as a plain date again without any business rule....
At this point there's very little I can do...
September 15, 2009 at 2:57 pm
First of all: my previous post didn't include your last post. So please disregard, since you provided the data I asked for.
Following please find a proposal to calculate your business month.
However, I'd like to second CirquedeSQLeils proposal to use a lookup table.
The code I'm proposing could be used to fill/update a calendar table.
The code brings up another issue: is it correct that the Fiscal Year 2012 will start on March 23rd and not on March 30th? If March 30th would be correct, the logic supplied doesn't match....)
DECLARE @fy CHAR (4),
@fystart DATETIME
SELECT @fy ='2009' -- start fiscal year
SET @fystart = dateadd(dd, - datepart(dw,@fy+'/04/01')+ 1, @fy+'/04/01')
IF (SELECT datepart(mm,@fystart))= 4
SET @fystart = dateadd(wk,-1,@fystart)
;WITH
cte_numbers (N) AS -- numbers table for the next three year (=36 month)
(
SELECT TOP 36 ROW_NUMBER () OVER(ORDER BY object_id) FROM sys.columns
)
SELECT
datepart(year,dateadd(yy,(n-1)/12,@fy)) AS FiscalYear,
CASE WHEN ((n + 2) / 3) % 4 = 0 THEN 4 ELSE ((n + 2) / 3) % 4 END AS fy_Quarter,
CASE WHEN n % 12 = 0 THEN 12 ELSE n % 12 END AS fy_month,
dateadd(wk,(n-1)*4 + datediff(wk,@fystart,dateadd(wk,(n-1)*4,@fystart))/12,@fystart) AS fy_month_start,
dateadd(dd,-1,dateadd(wk,(n)*4 + datediff(wk,@fystart,dateadd(wk,(n)*4,@fystart))/12,@fystart)) AS fy_month_end
FROM cte_numbers
September 15, 2009 at 3:26 pm
Thank You for your reply . Your solution shows fy_month_start =3/29/2009 and fy_month_end ='4/25/2009' but its supposed to be '3/27/2009' and fy_month_end ='4/23/2009' .
For 2010 the fiscal year starts from March,26th.
But for the corresponding years 2011 and later , it has not been decided which months will have 5 weeks .
September 15, 2009 at 3:35 pm
Panchi (9/15/2009)
Thank You for your reply . Your solution shows fy_month_start =3/29/2009 and fy_month_end ='4/25/2009' but its supposed to be '3/27/2009' and fy_month_end ='4/23/2009' .For 2010 the fiscal year starts from March,26th.
But for the corresponding years 2011 and later , it has not been decided which months will have 5 weeks .
addSET datefirst 5at the beginning of the sample code. (missed it when I copied the sample). Sorry about that... :crying:
September 16, 2009 at 7:37 am
Thank You . It works fine .
September 16, 2009 at 8:25 am
Panchi (9/15/2009)
Thank You for your reply . Your solution shows fy_month_start =3/29/2009 and fy_month_end ='4/25/2009' but its supposed to be '3/27/2009' and fy_month_end ='4/23/2009' .For 2010 the fiscal year starts from March,26th.
But for the corresponding years 2011 and later , it has not been decided which months will have 5 weeks .
The bolded part is the key fact - it indicates that there is no set of business rules to model. More strictly, it means the calendar is not mechanistically determinable in the general case. Which, of course, means you can't write code to pre-generate the calendar. Ultimately, you're always going to end up having to enter a list of dates in your situation. Some years, it may be possible to write code to generate that list, but others, it may not.
______
Twitter: @Control_Group
September 16, 2009 at 9:25 am
Thanks .
September 16, 2009 at 3:23 pm
Panchi (9/15/2009)
The business rule isBegins Ends
27-Mar 23-Apr ( 4 weeks)
24-Apr 21-May ( 4 weeks)
22-May 25-Jun ( 5 weeks)
26-Jun 23-Jul ( 4 weeks)
24-Jul 20-Aug ( 4 weeks)
21-Aug 24-Sep ( 5 weeks)
25-Sep 22-Oct ( 4 weeks)
23-Oct 19-Nov ( 4 weeks)
20-Nov 24-Dec ( 5 weeks)
25-Dec 21-Jan ( 4 weeks)
22-Jan 18-Feb ( 4 weeks)
19-Feb 25-Mar( 5 weeks)
You've already done the hard part... just put that information into a table and you're done.;-)
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply