You can consider using the DMV's available in SQL Server 2005

You could also use some tools which includes Microsoft Add-on tools like Performance Dashboard reports which are extremely useful in getting CPU usage reports. However, with performance dashboard reports you would get current information only.

Please check below links

http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

http://www.sql-server-performance.com/articles/per/bm_performance_dashboard_2005_p1.aspx