1

We're measuring actual values against a target. We have a overall target for a day (a 24 hour period) but we need to calculate a target for any given time to measure our progress 'so far'.

Obviously we need to proportion the overall target to the given time; so if it's midday, then the progressive target is half the daily target, and at 6am the progressive target would be a quarter of the daily target - and so on.

What's the most efficient way to do this in SQL? There is an obvious solution but it's not very pretty - I'd ideally like to be able to convert a DATETIME into a total number of milliseconds in a single operation.

Kirk Broadhurst
  • 27,836
  • 16
  • 104
  • 169
  • With due respect, not very clear what you're asking. Can you please expand? This "target" - is it the sysdate, at the time of execution of a particular SP or query? Is it stored somewhere in the database? And the amount it's compared to - is it in a field, a parameter, something else? An example of what you have and what you'd like to accomplish would be useful. – Joe Pineda Aug 29 '12 at 03:11

1 Answers1

5

To get the percentage of a day that's complete, you can do this:

select datediff(MILLISECOND, CONVERT(date, getdate()), GETDATE())/864000.0

Do you really need to the millisecond?

aquinas
  • 23,318
  • 5
  • 58
  • 81
  • Fantastic! No, I don't need millisecond in this case, and minute would be enough - I'm just looking for the most reusable pattern. – Kirk Broadhurst Aug 29 '12 at 03:38