Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Set fiscal year, have year automatically roll over Expand / Collapse
Author
Message
Posted Tuesday, July 27, 2010 10:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 2, 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
Posted Tuesday, July 27, 2010 12:40 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 1:49 PM
Points: 1,104, Visits: 1,174
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
Posted Thursday, February 23, 2012 9:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 4:20 AM
Points: 69, Visits: 363
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
Posted Sunday, March 4, 2012 8:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1261367
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse