Set fiscal year, have year automatically roll over

Question

Set fiscal year, have year automatically roll over

jheltsley

SSC Enthusiast

Points: 193
More actions
July 27, 2010 at 10:40 am

#238133

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

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[/url]

Conficker SSCommitted Points: 1567 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)