0

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%)
dmahapatro
  • 49,365
  • 7
  • 88
  • 117
user2912796
  • 1
  • 1
  • 2
    So is the percentage missed in relation to one year? So if I had a Plan Deploy Date of 5/1/2013 and Actual Deploy Date of 5/1/2014 did it miss by 100%? In general this seems like a weird way to quantify this since you're essentially taking an arbitrary value to define the scale. Why not just track the difference in days? – Eric J. Price Oct 23 '13 at 19:17
  • what if the Actual date and the plan date are the same? – Malachi Oct 23 '13 at 19:23
  • If the actual date and plan date are the same ...you are at 0%. You hit your date. – user2912796 Oct 24 '13 at 02:17
  • If you miss by one year...yes it is 100% off. This is the same for cost or effort. You planned to consume 1000 hrs and you "actually" consumed 3000 hours. So, 3000 - 1000 / 1000 = 200% off. Basically, you underestimated your resource consumption by 200%!!. Make sense. – user2912796 Oct 24 '13 at 02:20
  • The difference in days is correct. But it is by what % of (days, weeks, months...). By what percent (days, weeks, months...doesn't matter) did you miss by. – user2912796 Oct 24 '13 at 02:24

1 Answers1

0

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.

The difference in days is correct. But it is by what % of (days, weeks, months...). By what percent (days, weeks, months...doesn't matter) did you miss by. – user2912796

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.

Bill Hurt
  • 749
  • 1
  • 8
  • 26
  • If my answer is helpful please enough please consider marking it as the accepted answer. Otherwise you can leave another comment and let me know what about this answer is still missing. Thanks. – Bill Hurt Oct 30 '13 at 11:34