|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Determining financial year quarterThe quarter function returns 1 to 4 but is based by default on a year
starting jan1 and ending dec 31 as far as I can tell. I need to find a way to determine in what quarter of a year a date is when a year starts and ends on other dates (eg May 1 and ends April 30) as is the case in financial years of corporations. As in for the year starting May and ending April 30, in what quarter is January 21? And also for same year, what is start and end date of quarter 2? I suppose I can work out some sort of logic but I wonder if anyone has pointers to bits of code that do the above so I don't have to reinvent the wheel. By the way, working with SQL server 2005 and VS 2005. Any help would be appreciated. Bob swag: you could get the quarter of your date - diff in quarter start
from year start declare @yourdate datetime, @yourq1 datetime set @yourq1 = '20050501' set @yourdate='20050801' -- should be q2 select datepart(q, dateadd(d, -datediff(d, @yourq1-datepart(dy,@yourq1)+1 ,@yourq1), @yourdate)) Bob wrote: Show quote > The quarter function returns 1 to 4 but is based by default on a year > starting jan1 and ending dec 31 as far as I can tell. > > I need to find a way to determine in what quarter of a year a date is when a > year starts and ends on other dates (eg May 1 and ends April 30) as is the > case in financial years of corporations. > > As in for the year starting May and ending April 30, in what quarter is > January 21? And also for same year, what is start and end date of quarter 2? > > I suppose I can work out some sort of logic but I wonder if anyone has > pointers to bits of code that do the above so I don't have to reinvent the > wheel. By the way, working with SQL server 2005 and VS 2005. > > Any help would be appreciated. > > Bob > > Thanks, tried it and it works just fine.
Second part of question was, knowing we're in quarter 2 and knowing year start date, what are the start and end dates of quarter 2? Do you think you might have that up your sleeve somewhere? Would really appreciate that if you do, Bob Show quote "Trey Walpole" <treypole@newsgroups.nospam> wrote in message news:uBvKcjoAGHA.2320@TK2MSFTNGP11.phx.gbl... > swag: you could get the quarter of your date - diff in quarter start from > year start > > declare @yourdate datetime, @yourq1 datetime > set @yourq1 = '20050501' > set @yourdate='20050801' -- should be q2 > select datepart(q, dateadd(d, -datediff(d, @yourq1-datepart(dy,@yourq1)+1 > ,@yourq1), @yourdate)) > > > > Bob wrote: >> The quarter function returns 1 to 4 but is based by default on a year >> starting jan1 and ending dec 31 as far as I can tell. >> >> I need to find a way to determine in what quarter of a year a date is >> when a year starts and ends on other dates (eg May 1 and ends April 30) >> as is the case in financial years of corporations. >> >> As in for the year starting May and ending April 30, in what quarter is >> January 21? And also for same year, what is start and end date of quarter >> 2? >> >> I suppose I can work out some sort of logic but I wonder if anyone has >> pointers to bits of code that do the above so I don't have to reinvent >> the wheel. By the way, working with SQL server 2005 and VS 2005. >> >> Any help would be appreciated. >> >> Bob >> Hi
You may want to look at a calendar table e.g. http://www.aspfaq.com/show.asp?id=2519 this can be pre-populated with financial quarters and finding first (min) and last (max) date are no issue. John Show quote "Bob" <bduf***@sgiims.com> wrote in message news:O92ThMzAGHA.3984@TK2MSFTNGP14.phx.gbl... > Thanks, tried it and it works just fine. > Second part of question was, knowing we're in quarter 2 and knowing year > start date, what are the start and end dates of quarter 2? > Do you think you might have that up your sleeve somewhere? > Would really appreciate that if you do, > > Bob > > "Trey Walpole" <treypole@newsgroups.nospam> wrote in message > news:uBvKcjoAGHA.2320@TK2MSFTNGP11.phx.gbl... >> swag: you could get the quarter of your date - diff in quarter start from >> year start >> >> declare @yourdate datetime, @yourq1 datetime >> set @yourq1 = '20050501' >> set @yourdate='20050801' -- should be q2 >> select datepart(q, dateadd(d, -datediff(d, @yourq1-datepart(dy,@yourq1)+1 >> ,@yourq1), @yourdate)) >> >> >> >> Bob wrote: >>> The quarter function returns 1 to 4 but is based by default on a year >>> starting jan1 and ending dec 31 as far as I can tell. >>> >>> I need to find a way to determine in what quarter of a year a date is >>> when a year starts and ends on other dates (eg May 1 and ends April 30) >>> as is the case in financial years of corporations. >>> >>> As in for the year starting May and ending April 30, in what quarter is >>> January 21? And also for same year, what is start and end date of >>> quarter 2? >>> >>> I suppose I can work out some sort of logic but I wonder if anyone has >>> pointers to bits of code that do the above so I don't have to reinvent >>> the wheel. By the way, working with SQL server 2005 and VS 2005. >>> >>> Any help would be appreciated. >>> >>> Bob >>> > I created a solution that will do this for any arbitrary fiscal years. I
have to run, but will post the whole project Sunday. -- Show quoteWilliam Stacey [MVP] "Bob" <bduf***@sgiims.com> wrote in message news:O92ThMzAGHA.3984@TK2MSFTNGP14.phx.gbl... > Thanks, tried it and it works just fine. > Second part of question was, knowing we're in quarter 2 and knowing year > start date, what are the start and end dates of quarter 2? > Do you think you might have that up your sleeve somewhere? > Would really appreciate that if you do, > > Bob > > "Trey Walpole" <treypole@newsgroups.nospam> wrote in message > news:uBvKcjoAGHA.2320@TK2MSFTNGP11.phx.gbl... >> swag: you could get the quarter of your date - diff in quarter start from >> year start >> >> declare @yourdate datetime, @yourq1 datetime >> set @yourq1 = '20050501' >> set @yourdate='20050801' -- should be q2 >> select datepart(q, dateadd(d, -datediff(d, @yourq1-datepart(dy,@yourq1)+1 >> ,@yourq1), @yourdate)) >> >> >> >> Bob wrote: >>> The quarter function returns 1 to 4 but is based by default on a year >>> starting jan1 and ending dec 31 as far as I can tell. >>> >>> I need to find a way to determine in what quarter of a year a date is >>> when a year starts and ends on other dates (eg May 1 and ends April 30) >>> as is the case in financial years of corporations. >>> >>> As in for the year starting May and ending April 30, in what quarter is >>> January 21? And also for same year, what is start and end date of >>> quarter 2? >>> >>> I suppose I can work out some sort of logic but I wonder if anyone has >>> pointers to bits of code that do the above so I don't have to reinvent >>> the wheel. By the way, working with SQL server 2005 and VS 2005. >>> >>> Any help would be appreciated. >>> >>> Bob >>> > And does not use any tables.
-- Show quoteWilliam Stacey [MVP] "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message news:O0%23V171AGHA.832@tk2msftngp13.phx.gbl... >I created a solution that will do this for any arbitrary fiscal years. I >have to run, but will post the whole project Sunday. > > -- > William Stacey [MVP] > > "Bob" <bduf***@sgiims.com> wrote in message > news:O92ThMzAGHA.3984@TK2MSFTNGP14.phx.gbl... >> Thanks, tried it and it works just fine. >> Second part of question was, knowing we're in quarter 2 and knowing year >> start date, what are the start and end dates of quarter 2? >> Do you think you might have that up your sleeve somewhere? >> Would really appreciate that if you do, >> >> Bob >> >> "Trey Walpole" <treypole@newsgroups.nospam> wrote in message >> news:uBvKcjoAGHA.2320@TK2MSFTNGP11.phx.gbl... >>> swag: you could get the quarter of your date - diff in quarter start >>> from year start >>> >>> declare @yourdate datetime, @yourq1 datetime >>> set @yourq1 = '20050501' >>> set @yourdate='20050801' -- should be q2 >>> select datepart(q, dateadd(d, -datediff(d, >>> @yourq1-datepart(dy,@yourq1)+1 ,@yourq1), @yourdate)) >>> >>> >>> >>> Bob wrote: >>>> The quarter function returns 1 to 4 but is based by default on a year >>>> starting jan1 and ending dec 31 as far as I can tell. >>>> >>>> I need to find a way to determine in what quarter of a year a date is >>>> when a year starts and ends on other dates (eg May 1 and ends April 30) >>>> as is the case in financial years of corporations. >>>> >>>> As in for the year starting May and ending April 30, in what quarter is >>>> January 21? And also for same year, what is start and end date of >>>> quarter 2? >>>> >>>> I suppose I can work out some sort of logic but I wonder if anyone has >>>> pointers to bits of code that do the above so I don't have to reinvent >>>> the wheel. By the way, working with SQL server 2005 and VS 2005. >>>> >>>> Any help would be appreciated. >>>> >>>> Bob >>>> >> > > The Sql2005 clr project is at
http://channel9.msdn.com/ShowPost.aspx?PostID=147390 It will allow you to do things like below. Has no data tables to install or update: --use <YourDBName> declare @q1 SqlDateRange declare @q2 SqlDateRange declare @q3 SqlDateRange declare @q4 SqlDateRange declare @startFiscalYear int -- Get all quarters in the fiscal year. set @startFiscalYear = 1 -- Or other (i.e. 1-12). set @q1 = SqlDateRange::GetQuarterByNumber(2005, 1, @startFiscalYear) set @q2 = SqlDateRange::GetQuarterByNumber(2005, 2, @startFiscalYear) set @q3 = SqlDateRange::GetQuarterByNumber(2005, 3, @startFiscalYear) set @q4 = SqlDateRange::GetQuarterByNumber(2005, 4, @startFiscalYear); select @q1.ToString() as Q1Range, @q2.ToString() as Q2Range, @q3.ToString() as Q3Range, @q4.ToString() as Q4Range select @q1.StartDate as Q1, @q2.StartDate as Q2, @q3.StartDate as Q3, @q4.StartDate as Q4 -- Get qtrs before and after q1. declare @nextQtr SqlDateRange declare @priorQtr SqlDateRange set @nextQtr = @q1.GetNextQuarter(@startFiscalYear); set @priorQtr = @q1.GetPriorQuarter(@startFiscalYear); select @priorQtr.ToString() as [DateRange of prior Qtr], @nextQtr.ToString() as [DateRange of next Qtr] -- Is a date in 1st quarter? if ( SqlDateRange::IsBetweenQuarter('4/1/2005', 1, 1) = 'true' ) print 'Date is in the quarter.' else print 'Date is not in the quarter' -- OR if ( @q1.IsBetween('3/31/2005 11:59:59.998 pm') = 'true' ) print 'Date is in first quarter.' else print 'Date is not in first quarter.' -- Show quoteWilliam Stacey [MVP] "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message news:O0%23V171AGHA.832@tk2msftngp13.phx.gbl... >I created a solution that will do this for any arbitrary fiscal years. I >have to run, but will post the whole project Sunday. > > -- > William Stacey [MVP] > > "Bob" <bduf***@sgiims.com> wrote in message > news:O92ThMzAGHA.3984@TK2MSFTNGP14.phx.gbl... >> Thanks, tried it and it works just fine. >> Second part of question was, knowing we're in quarter 2 and knowing year >> start date, what are the start and end dates of quarter 2? >> Do you think you might have that up your sleeve somewhere? >> Would really appreciate that if you do, >> >> Bob >> >> "Trey Walpole" <treypole@newsgroups.nospam> wrote in message >> news:uBvKcjoAGHA.2320@TK2MSFTNGP11.phx.gbl... >>> swag: you could get the quarter of your date - diff in quarter start >>> from year start >>> >>> declare @yourdate datetime, @yourq1 datetime >>> set @yourq1 = '20050501' >>> set @yourdate='20050801' -- should be q2 >>> select datepart(q, dateadd(d, -datediff(d, >>> @yourq1-datepart(dy,@yourq1)+1 ,@yourq1), @yourdate)) >>> >>> >>> >>> Bob wrote: >>>> The quarter function returns 1 to 4 but is based by default on a year >>>> starting jan1 and ending dec 31 as far as I can tell. >>>> >>>> I need to find a way to determine in what quarter of a year a date is >>>> when a year starts and ends on other dates (eg May 1 and ends April 30) >>>> as is the case in financial years of corporations. >>>> >>>> As in for the year starting May and ending April 30, in what quarter is >>>> January 21? And also for same year, what is start and end date of >>>> quarter 2? >>>> >>>> I suppose I can work out some sort of logic but I wonder if anyone has >>>> pointers to bits of code that do the above so I don't have to reinvent >>>> the wheel. By the way, working with SQL server 2005 and VS 2005. >>>> >>>> Any help would be appreciated. >>>> >>>> Bob >>>> >> > > I've just looked at your project, and yes, you can specify a starting month,
but since you still use calendar months, it's not a true fiscal calendar. It is rare for a fiscal period to commence on the first day of a month. Many fiscal calendars use the 445445445445 (weeks/period) scheme to define their fiscal period, but even this is not set in stone. Last year, our accountants started out with that scheme, but then decided that one of the reporting periods was too far removed from the corresponding calendar month so they changed the period start and end dates to tweak it. This type of thing would not be possible without using a calendar table to control things. Bob Barrows William Stacey [MVP] wrote: Show quote > I created a solution that will do this for any arbitrary fiscal > years. I have to run, but will post the whole project Sunday. > > -- > William Stacey [MVP] > > "Bob" <bduf***@sgiims.com> wrote in message > news:O92ThMzAGHA.3984@TK2MSFTNGP14.phx.gbl... >> Thanks, tried it and it works just fine. >> Second part of question was, knowing we're in quarter 2 and knowing >> year start date, what are the start and end dates of quarter 2? >> Do you think you might have that up your sleeve somewhere? >> Would really appreciate that if you do, >> >> Bob >> >> "Trey Walpole" <treypole@newsgroups.nospam> wrote in message >> news:uBvKcjoAGHA.2320@TK2MSFTNGP11.phx.gbl... >>> swag: you could get the quarter of your date - diff in quarter >>> start from year start >>> >>> declare @yourdate datetime, @yourq1 datetime >>> set @yourq1 = '20050501' >>> set @yourdate='20050801' -- should be q2 >>> select datepart(q, dateadd(d, -datediff(d, >>> @yourq1-datepart(dy,@yourq1)+1 ,@yourq1), @yourdate)) >>> >>> >>> >>> Bob wrote: >>>> The quarter function returns 1 to 4 but is based by default on a >>>> year starting jan1 and ending dec 31 as far as I can tell. >>>> >>>> I need to find a way to determine in what quarter of a year a date >>>> is when a year starts and ends on other dates (eg May 1 and ends >>>> April 30) as is the case in financial years of corporations. >>>> >>>> As in for the year starting May and ending April 30, in what >>>> quarter is January 21? And also for same year, what is start and >>>> end date of quarter 2? >>>> >>>> I suppose I can work out some sort of logic but I wonder if anyone >>>> has pointers to bits of code that do the above so I don't have to >>>> reinvent the wheel. By the way, working with SQL server 2005 and >>>> VS 2005. >>>> >>>> Any help would be appreciated. >>>> >>>> Bob -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. Got ya. Yeh I did not consider anything but real month boundarys. BTW - I
never understood why some companies do this. Seems like more mental issues then what it is worth. What is the advantage(s)? TIA -- Show quoteWilliam Stacey [MVP] "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:eVXpaz8BGHA.3496@TK2MSFTNGP11.phx.gbl... > I've just looked at your project, and yes, you can specify a starting > month, > but since you still use calendar months, it's not a true fiscal calendar. > It > is rare for a fiscal period to commence on the first day of a month. Many > fiscal calendars use the 445445445445 (weeks/period) scheme to define > their > fiscal period, but even this is not set in stone. Last year, our > accountants > started out with that scheme, but then decided that one of the reporting > periods was too far removed from the corresponding calendar month so they > changed the period start and end dates to tweak it. This type of thing > would > not be possible without using a calendar table to control things. > > Bob Barrows > > William Stacey [MVP] wrote: >> I created a solution that will do this for any arbitrary fiscal >> years. I have to run, but will post the whole project Sunday. >> >> -- >> William Stacey [MVP] >> >> "Bob" <bduf***@sgiims.com> wrote in message >> news:O92ThMzAGHA.3984@TK2MSFTNGP14.phx.gbl... >>> Thanks, tried it and it works just fine. >>> Second part of question was, knowing we're in quarter 2 and knowing >>> year start date, what are the start and end dates of quarter 2? >>> Do you think you might have that up your sleeve somewhere? >>> Would really appreciate that if you do, >>> >>> Bob >>> >>> "Trey Walpole" <treypole@newsgroups.nospam> wrote in message >>> news:uBvKcjoAGHA.2320@TK2MSFTNGP11.phx.gbl... >>>> swag: you could get the quarter of your date - diff in quarter >>>> start from year start >>>> >>>> declare @yourdate datetime, @yourq1 datetime >>>> set @yourq1 = '20050501' >>>> set @yourdate='20050801' -- should be q2 >>>> select datepart(q, dateadd(d, -datediff(d, >>>> @yourq1-datepart(dy,@yourq1)+1 ,@yourq1), @yourdate)) >>>> >>>> >>>> >>>> Bob wrote: >>>>> The quarter function returns 1 to 4 but is based by default on a >>>>> year starting jan1 and ending dec 31 as far as I can tell. >>>>> >>>>> I need to find a way to determine in what quarter of a year a date >>>>> is when a year starts and ends on other dates (eg May 1 and ends >>>>> April 30) as is the case in financial years of corporations. >>>>> >>>>> As in for the year starting May and ending April 30, in what >>>>> quarter is January 21? And also for same year, what is start and >>>>> end date of quarter 2? >>>>> >>>>> I suppose I can work out some sort of logic but I wonder if anyone >>>>> has pointers to bits of code that do the above so I don't have to >>>>> reinvent the wheel. By the way, working with SQL server 2005 and >>>>> VS 2005. >>>>> >>>>> Any help would be appreciated. >>>>> >>>>> Bob > > -- > Microsoft MVP -- ASP/ASP.NET > Please reply to the newsgroup. The email account listed in my From > header is my spam trap, so I don't check it very often. You will get a > quicker response by posting to the newsgroup. > > No idea. I've often wondered the same thing myself. I once asked and got
some bablle about "standardization" but that's ridiculous given the way they "tweak" the calendar. Bob Barrows William Stacey [MVP] wrote: Show quote > Got ya. Yeh I did not consider anything but real month boundarys. BTW - I > never understood why some companies do this. Seems like more > mental issues then what it is worth. What is the advantage(s)? TIA > > > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message > news:eVXpaz8BGHA.3496@TK2MSFTNGP11.phx.gbl... >> I've just looked at your project, and yes, you can specify a starting >> month, >> but since you still use calendar months, it's not a true fiscal >> calendar. It >> is rare for a fiscal period to commence on the first day of a month. >> Many fiscal calendars use the 445445445445 (weeks/period) scheme to >> define their >> fiscal period, but even this is not set in stone. Last year, our >> accountants >> started out with that scheme, but then decided that one of the >> reporting periods was too far removed from the corresponding >> calendar month so they changed the period start and end dates to >> tweak it. This type of thing would >> not be possible without using a calendar table to control things. >> >> Bob Barrows >> >> William Stacey [MVP] wrote: >>> I created a solution that will do this for any arbitrary fiscal >>> years. I have to run, but will post the whole project Sunday. >>> -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" Bob,
The expression datediff(month,@fiscBegins,d)/3%4+1 should give you the fiscal quarter, if each quarter is three months long, and if @fiscBegins is the date in 1753 on which the quarter starts, and is also the first day of some month. Here's a repro script: create table t ( d datetime ) go insert into t select top 100 OrderDate from Northwind..Orders where day(OrderDate) in (1,2,15,28,29,30,31) order by CustomerID go declare @fiscBegins datetime set @fiscBegins = '17530501' -- May 1 starts fiscal year if day(@fiscBegins) > 1 begin print 'Fiscal year must start on the first of a month.' return end select d, datediff(month,@fiscBegins,d)/3%4+1 as Qtr from t order by d go drop table t Steve Kass Drew University Bob wrote: Show quote >The quarter function returns 1 to 4 but is based by default on a year >starting jan1 and ending dec 31 as far as I can tell. > >I need to find a way to determine in what quarter of a year a date is when a >year starts and ends on other dates (eg May 1 and ends April 30) as is the >case in financial years of corporations. > >As in for the year starting May and ending April 30, in what quarter is >January 21? And also for same year, what is start and end date of quarter 2? > >I suppose I can work out some sort of logic but I wonder if anyone has >pointers to bits of code that do the above so I don't have to reinvent the >wheel. By the way, working with SQL server 2005 and VS 2005. > >Any help would be appreciated. > >Bob > > > > Thanks to you all,
Bob Show quote "Bob" <bduf***@sgiims.com> wrote in message news:uu$X6coAGHA.3496@TK2MSFTNGP11.phx.gbl... > The quarter function returns 1 to 4 but is based by default on a year > starting jan1 and ending dec 31 as far as I can tell. > > I need to find a way to determine in what quarter of a year a date is when > a year starts and ends on other dates (eg May 1 and ends April 30) as is > the case in financial years of corporations. > > As in for the year starting May and ending April 30, in what quarter is > January 21? And also for same year, what is start and end date of quarter > 2? > > I suppose I can work out some sort of logic but I wonder if anyone has > pointers to bits of code that do the above so I don't have to reinvent the > wheel. By the way, working with SQL server 2005 and VS 2005. > > Any help would be appreciated. > > Bob > >  |
|||||||||||||||||||||||
Other interesting topics