annualization help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

annualization help

Author

Topic

fish462
Starting Member

4 Posts

Posted - 2006-09-13 : 11:01:36

I am trying to write a formula for capturing an annualized count of something. So for arguments sake, let's say I am counting the number of phone calls I get from my ex-wife. Let's say I got one in January, that means I am on track to have 12 for the year...but let's say it is now June and I still have only recieved that one call. I am no longer on track to get 12, I would say I am on track to get two. Here is what I have now but it doesn't readjust as time passes...it assumes that I am going to get at least one phone call per month to work:
cast(round((TRBL_CNTS.AC1*12)+((TRBL_CNTS.AC2/2)*12)+((TRBL_CNTS.AC3/3)*12)+((TRBL_CNTS.AC4/4)*12)+((TRBL_CNTS.AC5/5)*12)+((TRBL_CNTS.AC6/6)*12)+((TRBL_CNTS.AC7/7)*12)+((TRBL_CNTS.AC8/8)*12)+((TRBL_CNTS.AC9/9)*12)+((TRBL_CNTS.AC10/10)*12)+((TRBL_CNTS.AC11/11)*12)+((TRBL_CNTS.AC12/12)*12),0)as int) AS POSTCLOSE_TRBL_ANNL

TRBL_CNTS.ACXX is the number of phone calls for each month.
Thanks for any help you can provide.
Fish

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-13 : 11:14:56

If you are using v2005 you can use a CTE to cater for the months without data
see
http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/
under Date Ranges

If v2000 you can do similar thing creating a derived table
see
http://www.nigelrivett.net/SQLTsql/FindGapsInSequence.html
for the tally table - can be used to calculate the date ranges.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-09-13 : 13:04:20

You are making this much harder than it needs to be. You need some variable (int, in your case) to indicated the current month number (1 -12). Then you projected total is just your current total pro-rated over 12 months:

select	(TRBL_CNTS.AC1
	+ TRBL_CNTS.AC2
	+ TRBL_CNTS.AC3
	+ TRBL_CNTS.AC4
	+ TRBL_CNTS.AC5
	+ TRBL_CNTS.AC6
	+ TRBL_CNTS.AC7
	+ TRBL_CNTS.AC8
	+ TRBL_CNTS.AC9
	+ TRBL_CNTS.AC10
	+ TRBL_CNTS.AC11
	+ TRBL_CNTS.AC12) * (12/@CurrentMonth)

...and you might think about normalizing your data when you a get a chance...

"I have HAD it with these muthu-f$#%in' cursors in my muthu-f$#%in' database!"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-13 : 13:40:17

You may want to modify the calculation a little to use decimal math, instead of integer math. ( 12 / 7 = 1 in integer math )


select	(TRBL_CNTS.AC1
	+ TRBL_CNTS.AC2
	+ TRBL_CNTS.AC3
	+ TRBL_CNTS.AC4
	+ TRBL_CNTS.AC5
	+ TRBL_CNTS.AC6
	+ TRBL_CNTS.AC7
	+ TRBL_CNTS.AC8
	+ TRBL_CNTS.AC9
	+ TRBL_CNTS.AC10
	+ TRBL_CNTS.AC11
	+ TRBL_CNTS.AC12 * 12.) / @CurrentMonth

CODO ERGO SUM

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-09-14 : 07:06:07

Are you saying that getting less calls from the ex-wife is a problem??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources