microsoft, sql server database 2000, queries, adding time based on start dates that o`verlap


I must calculate downtime on a project,the downtime is thus the amount of stoppage there is per project

project 1      down id       start       end             total          date
project 1       1      10:00 AM      10:45 AM            :45      29-9-7
project 1       2      10:55 AM      11:00 AM            :05      29-9-7
project 1       3      10:00 AM      10:30 AM            :30      29-9-7

As you see each entry has a total downtime,this is the difference between end and start,however I dont want the times to overlap,meaning if project 1 has two down id's during the same time then I dont want to double count
if I double count the minutes will be 80 minutes
project 1      :80      

however it should only be 50 minutes if the time overlaps are eliminated

how do I achiev that?

Who is Participating?
Scott PletcherSenior DBACommented:
OK, please try code below; after testing, replace "dataTable" with your table name, of course :-) .

CREATE FUNCTION dbo.GetDowntimeMinutes(
    @project VARCHAR(30),
    @date DATETIME
DECLARE @downtime CHAR(1440)
SET @downtime = REPLICATE('0', 1440)
SELECT @downtime =
    LEFT(@downtime, DATEDIFF(MINUTE, 0, data.start)) +
    REPLICATE('1', DATEDIFF(MINUTE, 0, data.[end]) - DATEDIFF(MINUTE, 0, data.start)) +
    SUBSTRING(@downtime, DATEDIFF(MINUTE, 0, data.[end]) + 1, 1440)
FROM dataTable data
WHERE project = @project AND date = @date
RETURN 1440 - LEN(REPLACE(@downtime, '1', ''))

-- create and load sample data table
if object_id('dbo.dataTable') IS NOT NULL
    DROP TABLE dataTable
create table dataTable (
    project VARCHAR(30),
    downId INT,
    start DATETIME,
    [end] DATETIME,
    total VARCHAR(10),
    date DATETIME
set dateformat dmy
set nocount on
insert into dataTable values('project 1', 1, '10:00 AM', '10:45 AM', ':45', '29-9-7')
insert into dataTable values('project 1', 2, '10:55 AM', '11:00 AM', ':05', '29-9-7')
insert into dataTable values('project 1', 3, '10:00 AM', '10:30 AM', ':30', '29-9-7')
insert into dataTable values('project 2', 21, '08:00 AM', '08:30 AM', '', '29-9-7')
insert into dataTable values('project 2', 22, '08:30 AM', '09:00 AM', '', '29-9-7')
insert into dataTable values('project 2', 23, '08:40 AM', '09:00 AM', '', '29-9-7')
insert into dataTable values('project 2', 23, '08:50 AM', '09:15 AM', '', '29-9-7')
set nocount off

SELECT Project, CONVERT(VARCHAR(10), Date, 105) AS Date,
    dbo.GetDowntimeMinutes(project, date) AS Downtime
    SELECT DISTINCT project, CAST(CONVERT(CHAR(8), date, 112) AS DATETIME) AS date
    FROM dataTable
) AS derived
Here's the logic, the code should come easily after this:

Process all of the rows in project, start time sequence.
For each row add the time to the running total downtime so far.
Store the end time
read the next row
if the new end time is before/equal the stored end time then discard and read next row -- time does not add to total
If the start time is before the stored end time then set the start time to be the stored end time
subtract the start time from the end time to calculate the downtime for this row.
Add the down time to the running total down time

You might need to do this with a cursor.
rutgermonsAuthor Commented:
cold u assist with some example?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
I'm working on this, pretty close, using a function.  I can't think of another way right now to do it.
rutgermonsAuthor Commented:
thanks scott,looks good,ill test it seems fine,i had an additional question,say if my start date is 29-september and my last finishtimes date is on 3-oct how could i encorporate that into my querY?

Scott PletcherSenior DBACommented:
Hmm, hadn't thought about that much.  Would need more details to give a clear answer:

1) Would an individual row cross days or would just other rows have a later day?
2) Would days be skipped between start and end?  If so, should those days be ignored or counted as 1.0 or 0.0?
3) Would the final total for a range of dates be the avg %/fraction used?
rutgermonsAuthor Commented:
1)an individual row could span on more than 1 day,i.e different start/finishdates
2)days would not be skipped
3)no averages,as the downid field is always unique,ill probably use the date from my output and throw it into a case or a crosstabl statement


rutgermonsAuthor Commented:
hello scott

any feedback on allowing the finishdate to be a different date?


Scott PletcherSenior DBACommented:
Sorry, I don't have time to re-investigate this now.

The general technique, however, is to cross join to a table of sequential numbers, which can be used to "explode" days.

Not to be difficult, but it would have been much easier to consider date variations from the beginning.  For complex questions, I personally think you are better off fully stating the q on the front end so that the solution is as complete as you need it to be.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.