December 17, 2009 at 1:49 pm
I have a work to split entered date range into weeks or fiscal months.
Suppose if the date range is entered as 2009/08/02 to 2009/10/31 then I have to split them into weeks or fiscal month.
I have figured it out to split them into weeks by
CREATE TABLE #Numbers (Number int identity primary key, Place varchar(5))
INSERT INTO #Numbers (PlaceHolder)
SELECT TOP 500 NULL
FROM tblXYZ
DECLARE @SDate datetime
DECLARE @EDate datetime
SET @SDate = '2009/08/02'
SET @EDate = '2009/10/31'
SELECT Number as Week,
Dateadd(week, number-1, @SDate) as StartDate,
CASE
WHEN Dateadd(week, number, @SDate)-1 > @EDate then @EDate
ELSE Dateadd(week, number, @SDate)-1
END as EndDate
FROM #Numbers
WHERE Number <= Datediff(week, @SDate, @EDate)
DROP TABLE #Numbers
But I’m struck on how to split them into fiscal months. We have a function called ‘GetPreviousFiscalMonthStartandEndDates’ to give start and end dates of prevous fiscal month. I was thinking to use it and split them. It works as
Select StartDate from GetPreviousFiscalMonthStartandEndDates('2009/10/31')
Select EndDate from GetPreviousFiscalMonthStartandEndDates('2009/10/31')
Can anybody please help me to figure it out?
December 17, 2009 at 2:05 pm
Build a calendar table. Solve the problem once, then just use that to join to.
You can have "FiscalMonth" as a column in the table, along with the usual "DayOfWeek", and "BusinessDay" columns.
They come in tremendously useful for numerous things.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply