Set fiscal year, have year automatically roll over

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Set fiscal year, have year automatically roll...

Set fiscal year, have year automatically roll over

Rate Topic

Display Mode

Topic Options

Author

Message

jheltsley

Posted Tuesday, July 27, 2010 10:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 02, 2011 1:59 PM
Points: 1, Visits: 8

I would like to (1) set the fiscal year as 12/01 - 11/30 and (2) have the data automatically roll over so only data for the current fiscal year is shown. I have the basic script but I can figure out the rollover. Note the dates are yyyymm.

/*Top 5 Billable Matters*/
select top 5 c.num, m.num, m.name, sum(t.hrs)
from time t
join matter m on t.matter_uno = m.matter_uno
join matter_tm tm on m.matter_uno = tm.matter_uno
join client c on m.client_uno = c.client_uno
join personnel p on t.tk_empl_uno = p.empl_uno
where t.period between '200912' and '201011'
and tm.class in ('B')
and p.employee_num = 'xxxx'
group by c.num, m.num, m.name
order by sum(t.hrs) desc

Post #959548

bteraberry

Posted Tuesday, July 27, 2010 12:40 PM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, February 23, 2013 11:07 AM
Points: 1,103, Visits: 1,170

You're already pulling your weeks from a table "time t". Why not just add a fiscalYear column to that table and group on it and/or just add where t.fiscalYear = whatever?

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help

Post #959627

Conficker

Posted Thursday, February 23, 2012 9:09 AM

Valued Member

Group: General Forum Members
Last Login: Friday, April 19, 2013 5:36 AM
Points: 58, Visits: 243

DECLARE @MyDate DATETIME SET @MyDate = getDate()

DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME

SET @StartDate = DATEADD(dd,0, DATEDIFF(dd,0, DATEADD( mm, -(((12 + DATEPART(m, @MyDate)) - 4)%12), @MyDate ) - datePart(d,DATEADD( mm, -(((12 + DATEPART(m, @MyDate)) - 4)%12),@MyDate ))+1 ) ) SET @EndDate = DATEADD(ss,-1,DATEADD(mm,12,@StartDate ))

SELECT @StartDate,@EndDate

Post #1256750

Jeff Moden

Posted Sunday, March 04, 2012 8:33 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 32,910, Visits: 26,802

I realize that this is an older post and no one may still be listening, but I ran out of "fun" posts and thought I'd give this one a try.

The goal the OP had in mind was to use the existing query but to make it auto-magically lookup the correct fiscal year's worth of information based on the current date. The fiscal year is based on a 12/01 through 11/30 time frame for each year.

The following will do just that. It's a reformatted copy of the original query where I replaced the BETWEEN for t.period with my own devices. The code easily handles leap years and could be turned into a parameterized iTVF (inline Table Valued Function) or maybe even a view, if so desired. If you break them down, the forumlii are pretty easy to understand, as well.

 SELECT TOP 5 c.num, m.num, m.name, SUM(t.hrs) 
   FROM dbo.time t
   JOIN dbo.matter     m ON t.matter_uno  = m.matter_uno
   JOIN dbo.matter_tm tm ON m.matter_uno  = tm.matter_uno
   JOIN dbo.client     c ON m.client_uno  = c.client_uno
   JOIN dbo.personnel  p ON t.tk_empl_uno = p.empl_uno
  WHERE t.period >= CONVERT(CHAR(6),DATEADD(yy,DATEDIFF(mm,-1,GETDATE())/12  ,0),112)
    AND t.period <  CONVERT(CHAR(6),DATEADD(yy,DATEDIFF(mm,-1,GETDATE())/12+1,0),112)
    AND tm.class IN ('B')
    AND p.employee_num = 'xxxx'
  GROUP BY c.num, m.num, m.name
  ORDER BY SUM(t.hrs) DESC

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1261367

« Prev Topic | Next Topic »

Permissions