Finding Breaches in data

I need to develop a query to identify Processes(Jobs) whose completion times are increasing over time and approaching  their end time. The logic created is to identify Processes(Jobs) in danger of breaches in SQL.

Example: if the job starts at 2pm and will take 3 hours of duration the completion time of this job is 5pm. However, the schedule agreed time for it to completes is 7. So if we observe the that job starting a 5pm it complete at 8pm. This will be a breach.
Super Man05Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Where will "the schedule agreed time" come from?

Sample data (per table)
and
Expected Result

these 2 items lead to the fastest, & most useful, answer
0
Super Man05Author Commented:
The purpose of the activity find breaches in the times.
-- What I gathered is we need to measure the End times against the completion times.
-- Then  we have measure the ROP targets times against completion times.
-- Then We have to make a comparison with a case statement to see if the times are finishing on the completion time or later.
--Completion times are the given for each job.

I must add we have this data but it's in various places. I would like to create a temp table  and do a join.
if the ROP time green and Publisher time is red it's a infrastructure issue.
if the ROP time is red and Publisher time means it's a breach in the data not meeting the time it should arrives.Job
example.xlsx
0
PortletPaulfreelancerCommented:
>>"I must add we have this data but it's in various places. I would like to create a temp table  and do a join."

Nothing in the spreadsheet helps do that part and assume you don't need help to create the temp table.

Could you check the data please as you used text not date/time valuessample--data.pngCould you explain row 6?
ROP arrival is before EndTime, is that valid

What does the "expected result" look like?

ALSO:
In your question you ask about something that " will take 3 hours of duration
is that duration stored anywhere?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulfreelancerCommented:
I missed a vital of question:

What database is this for? (Microsoft SQL Server? Oracle? MySQL? something else?)

date/time handling differs a lot between database vendors, and date/time handling is required in this query
0
Super Man05Author Commented:
Yes exactly I have the tables but it has data in it thats private however the example is the closet thing I can provide to you that is very similar.

So the question would be how would you find a breach in the times.

I can attach the data and the format of them but I prefer it not going to the public
0
Super Man05Author Commented:
SQL Server
0
PortletPaulfreelancerCommented:
"sample data" to hide private information is perfectly fine, but it should be "representative" and accurate

in many cases simply changing any labels to something innocent is enough for privacy, but you have to be judge of what your privacy requirements will allow - I can't do that for you.
---------

for SQL Server comparison of datetime data is simple enough, but you still haven't indicated what the result should look like.

|      JOB |             DATE |       ROP TARGET |      ROP ARRIVAL |          ENDTIME |  COMPLETION TIME | DIFFSECS |
|----------|------------------|------------------|------------------|------------------|------------------|----------|
|    House | 2014-10-02 00:00 | 2014-09-10 08:00 | 2014-09-10 07:53 | 2014-09-10 20:00 | 2014-09-10 13:01 |    18060 |
| Electric | 2014-10-03 00:00 | 2014-09-10 03:00 | 2014-09-10 01:28 | 2014-09-10 15:00 | 2014-09-10 17:02 |    50520 |
|   School | 2014-10-04 00:00 | 2014-09-10 01:00 | 2014-09-10 00:00 | 2014-09-10 10:03 | 2014-09-10 15:03 |    50580 |

Open in new window


produced by:
select
        [Job]
      , convert(varchar(16), [Date]           ,120) [Date] 
      , convert(varchar(16), [Rop Target]     ,120) [Rop Target]
      , convert(varchar(16), [Rop Arrival]    ,120) [Rop Arrival]
      , convert(varchar(16), [EndTime]        ,120) [EndTime]
      , convert(varchar(16), [Completion Time],120) [Completion Time]
      , datediff(second,[Rop Target],[Completion Time]) as diffsecs
from YourTable
where [Completion Time] > [Rop Target]
order by
        [Rop Target] DESC
;

Open in new window


for reference:
    
    CREATE TABLE YourTable
    	([Job] varchar(8), [Date] datetime, [Rop Target] datetime, [Rop Arrival] datetime, [EndTime] datetime, [Completion Time] datetime)
    ;
    	
    INSERT INTO YourTable
    	([Job], [Date], [Rop Target], [Rop Arrival], [EndTime], [Completion Time])
    VALUES
    	('Car', '2014-10-01 00:00:00', '2014-09-10 06:00:00', '2014-09-10 00:00:00', '2014-09-10 11:00:00', '2014-09-10 02:00:00'),
    	('House', '2014-10-02 00:00:00', '2014-09-10 08:00:00', '2014-09-10 07:53:00', '2014-09-10 20:00:00', '2014-09-10 13:01:00'),
    	('Electric', '2014-10-03 00:00:00', '2014-09-10 03:00:00', '2014-09-10 01:28:00', '2014-09-10 15:00:00', '2014-09-10 17:02:00'),
    	('School', '2014-10-04 00:00:00', '2014-09-10 01:00:00', '2014-09-10 00:00:00', '2014-09-10 10:03:00', '2014-09-10 15:03:00'),
    	('Food', '2014-10-05 00:00:00', '2014-09-10 03:00:00', '2014-09-10 00:44:00', '2014-09-10 00:04:00', '2014-09-10 02:24:00')
    ;

**Query 1**:

    
    select
            [Job]
          , convert(varchar(16), [Date]           ,120) [Date] 
          , convert(varchar(16), [Rop Target]     ,120) [Rop Target]
          , convert(varchar(16), [Rop Arrival]    ,120) [Rop Arrival]
          , convert(varchar(16), [EndTime]        ,120) [EndTime]
          , convert(varchar(16), [Completion Time],120) [Completion Time]
          , datediff(second,[Rop Target],[Completion Time]) as diffsecs
    from YourTable
    where [Completion Time] > [Rop Target]
    order by
            [Rop Target] DESC
    

**[Results][2]**:
    
    |      JOB |             DATE |       ROP TARGET |      ROP ARRIVAL |          ENDTIME |  COMPLETION TIME | DIFFSECS |
    |----------|------------------|------------------|------------------|------------------|------------------|----------|
    |    House | 2014-10-02 00:00 | 2014-09-10 08:00 | 2014-09-10 07:53 | 2014-09-10 20:00 | 2014-09-10 13:01 |    18060 |
    | Electric | 2014-10-03 00:00 | 2014-09-10 03:00 | 2014-09-10 01:28 | 2014-09-10 15:00 | 2014-09-10 17:02 |    50520 |
    |   School | 2014-10-04 00:00 | 2014-09-10 01:00 | 2014-09-10 00:00 | 2014-09-10 10:03 | 2014-09-10 15:03 |    50580 |



  [1]: http://sqlfiddle.com/#!3/1d9eb/7

Open in new window

0
Super Man05Author Commented:
This getting good, I believe we are getting close. The result we want is to see, is the trend for overtime and how each job completes. Can we measure if it's going to breach. A breach occurs when a when the job missing it's completion time.
0
PortletPaulfreelancerCommented:
"Can we measure if it's going to breach"?

My mind reading license expired last month.

Please take this the right way: "How the heck do I know?" (I only know what you tell me about your data)

I asked this before.

Do you have an expected duration stored in a table?

only if you have some way of predicting what the finish time will be can you measure it is "going to" breach
(which is why I asked)

Please do keep in your mind that what I know about your database is inside the square brackets below:
[                                         ]
0
Super Man05Author Commented:
Do you have an expected duration stored in a table?
the store duration time is given by the developers from the job Id. However
we can determine the duration completion time by subtracting the end time - start time.

in our database we have the jobname and the start time of the job. and the end time. we measure it from a tool that
the developers create and in their table is the completion time that's given from their data associated with our jobname.
I have a temp table that has the jobs and start time and endtime of those jobs.
0
PortletPaulfreelancerCommented:
"we can determine the duration completion time by subtracting the end time - start time. "

only AFTER the end time is recorded

You asked if we could predict the end time, which we could do IF

a. we have the start time, and
b, the expected duration

Otherwise you cannot do this: "measure if it's going to breach"
0
Super Man05Author Commented:
I have that data in a table so how would you query something like that and could we use a case statement to see to the breach. But You are so right on how we can determine the duration completion time by subtracting the end time - start time. "
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Super Man05Author Commented:
I have a query but mine is not working I'm joining a table that has the endtime and start times. I have a issue because the date for the end and start times are datatypes times are t. But the target times is a is only time. Can I get through this or do you have a cool query that can get me pass this?
0
PortletPaulfreelancerCommented:
>>Can I get through this or do you have a cool query that can get me pass this?
no idea, and it would need to be a new question now

if you do make another quation you will need to spell out this in detail:
... the date for the end and start times are datatypes times are t.

e.g. what is the data type "t"? (don't answer here, just keep it in mind)
0
Super Man05Author Commented:
No worries I figured it out. You had the right idea using the datediff function.
0
Super Man05Author Commented:
Just want to say thanks you pointed me in the right direction. Now, with the data we have, could you show me a query of how we would determine the trend? I would like to know how to determine if the the time would slope over time. How would we determine the trend of line? So if it's rising to 1 or more I would like SQL to input in a column it's and bad if it's sloping steady or negative it's good. Just need an example with the times we have.
0
PortletPaulfreelancerCommented:
please ask another question.... you can use a url in that question to refer to this one, but once a question is closed at EE it is closed :)
0
Super Man05Author Commented:
How would I find the slope if my query is
      Select    
    datediff(Minutes ,[Rop Target], [Completion Time]) as diffsMin
    from YourTable

I would like to know how to determine if the the time would slope over time.
How would we determine the trend of line? From something like this
JOB |             DATE                         ROP TARGET         |      ROP ARRIVAL   |    ENDTIME           |  COMPLETION TIME |
|----------    |------------------           |------------------          |------------------           |------------------           |------------------            
|    House | 2014-10-02 00:00 | 2014-09-10 08:00 | 2014-09-10 07:53 | 2014-09-10 20:00 | 2014-09-10 13:01 |    
| Electric   | 2014-10-03 00:00 | 2014-09-10 03:00 | 2014-09-10 01:28 | 2014-09-10 15:00 | 2014-09-10 17:02 |    
|   School  | 2014-10-04 00:00 | 2014-09-10 01:00 | 2014-09-10 00:00 | 2014-09-10 10:03 | 2014-09-10 15:03
0
PortletPaulfreelancerCommented:
Please ask another question
0
Super Man05Author Commented:
I want to invoke this as a function

Select  Slope (y,x)
from (
select x
,x/4 as y
from (values (1), (2), (3), (4)) n(x)
)m

Can you help with this
0
PortletPaulfreelancerCommented:
Please ask a new question. I am unable to help on that, and the only way you will attract  experts who can is through a new question
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.