Solved

Finding Breaches in data

Posted on 2014-10-09
22
106 Views
Last Modified: 2014-10-25
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.
0
Comment
Question by:Super Man05
  • 11
  • 10
22 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 

Author Comment

by:Super Man05
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>"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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 

Author Comment

by:Super Man05
Comment Utility
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
 

Author Comment

by:Super Man05
Comment Utility
SQL Server
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
"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
 

Author Comment

by:Super Man05
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
"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
 

Author Comment

by:Super Man05
Comment Utility
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
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.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
"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
 

Accepted Solution

by:
Super Man05 earned 0 total points
Comment Utility
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
 

Author Closing Comment

by:Super Man05
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>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
 

Author Comment

by:Super Man05
Comment Utility
No worries I figured it out. You had the right idea using the datediff function.
0
 

Author Comment

by:Super Man05
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 

Author Comment

by:Super Man05
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Please ask another question
0
 

Author Comment

by:Super Man05
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

8 Experts available now in Live!

Get 1:1 Help Now