• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

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

folks

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?

r
0
rutgermons
Asked:
rutgermons
  • 4
  • 4
1 Solution
 
CragCommented:
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.
0
 
rutgermonsAuthor Commented:
cold u assist with some example?
0
 
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.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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
    )
RETURNS INT
AS
BEGIN
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', ''))
END --FUNCTION


-- 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
0
 
rutgermonsAuthor Commented:
thanks scott,looks good,ill test it later.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?

R
0
 
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?
0
 
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

tia

r
0
 
rutgermonsAuthor Commented:
hello scott

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

rgds

r
0
 
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.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now