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

Posted on 2007-10-16
Last Modified: 2012-05-05

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?

Question by:rutgermons
    LVL 5

    Expert Comment

    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.

    Author Comment

    cold u assist with some example?
    LVL 68

    Expert Comment

    I'm working on this, pretty close, using a function.  I can't think of another way right now to do it.
    LVL 68

    Accepted Solution

    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
    FROM (
        SELECT DISTINCT project, CAST(CONVERT(CHAR(8), date, 112) AS DATETIME) AS date
        FROM dataTable
    ) AS derived

    Author Comment

    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?

    LVL 68

    Expert Comment

    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?

    Author Comment

    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



    Author Comment

    hello scott

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


    LVL 68

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now