Calculate % Accuracy:
(Actual Date - Plan Date) / Plan Date ~ 4.2% Over or Under depending on the dates.
I have a datediff for the dividend, but not the divisor.
I can perform in excel but not in sql.
How accurate is your estimate?
Plan Deploy Date = 5/1/2013
Actual Deploy Date = 6/15/2013
Algorithm = (Actual Date - Plan Date) / Plan Date
(6/15/2013 - 5/1/2013) / 5/1/2013 *100.0 = 10.9% (missed plan date by approx 11%)
You need to use the number of days budgeted for the project as the divisor and not the planned date of completion.
Dividing by a date makes no mathematical sense, in SQL Server or any other context because dates are an interval measurement scale. That means that there is no meaningful zero, and thus proportions of time can only make sense for small subsets of time.
In your example, since you say that missing a project by a year would be missing it by 100% your calculation should be: (6/15/2013 - 5/1/2013) / 365 * 100 Or in SQL Server something like: SELECT (DATEDIFF(DAY,'2013-06-15','2013-05-01') / 365) * 100
That gives you the ratio of the number of days taken to complete the project and the number of days anticipated.
If you were to replace the 365 in that sql statement with the date 2013-05-01, you would get something very different from what you expect, since again time has no real zero, so sql server has arbitrarily picked one.
In your hours budgeted example, you would never express (3000 - 1000) / 1000 as (3000 - 1000) / 5/1/2013
But that's exactly what you're attempting in your date calculation, just in a different time scale.
It does matter. You plug in 365 when you want the result in days and when the dividend is in days, just like you did for hours. If you switch to another time interval for the dividend you must also switch in the divisor.