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