CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
MrBeanzy
MrBeanzy

Reputation: 2296

Working Out Utilization Time MSSQL Query

I'm trying to work out the best way of doing this, but would appreciate some input I have a table that contains a StartDateTime and EndDateTime. I need to work out the total time in HH:mm:ss thats utilized. And also time that's not accounted for. To find the overall utilization of a operation. Hope that's clear?

Data example ...

StartDateTime           EndDateTime
2013-09-02 11:42:49.337 2013-09-02 11:47:56.520
2013-09-02 11:47:56.527 2013-09-02 12:02:51.150
2013-09-02 12:02:51.160 2013-09-02 12:11:53.543
2013-09-02 12:11:53.550 2013-09-02 12:17:35.063
2013-09-02 12:17:35.083 2013-09-02 12:22:20.860
2013-09-02 12:22:20.890 2013-09-02 12:29:54.490
2013-09-02 12:29:54.520 2013-09-02 12:34:31.873
2013-09-02 12:34:31.897 2013-09-02 12:41:25.097
2013-09-02 12:41:25.123 2013-09-02 12:47:54.280
2013-09-02 12:47:54.750 2013-09-02 12:52:40.387

Upvotes: 0

Views: 48

Answers (1)

podiluska
podiluska

Reputation: 51494

To find the time used

select convert(datetime,sum(convert(float, EndTime-StartTime)))  from yourtable

To find the time missing, subtract that from

select Max(EndTime)-min(StartTime) from yourtable

ie:

select convert(datetime,sum(convert(float, EndTime-StartTime))) , 
       Max(EndTime)-min(StartTime) - convert(datetime,sum(convert(float, EndTime-StartTime))) 
from yourtable

This assumes there are no overlapping elements of time.

Upvotes: 3

Related Questions