Solved

Finding Breaches in data

Posted on 2014-10-09
22
120 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
ID: 40372340
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
ID: 40373964
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
ID: 40374043
>>"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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40374067
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
ID: 40374103
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
ID: 40374115
SQL Server
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40374199
"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
ID: 40374434
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
ID: 40374474
"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
ID: 40374491
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40374557
"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
ID: 40374751
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
ID: 40383877
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
ID: 40383937
>>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
ID: 40384226
No worries I figured it out. You had the right idea using the datediff function.
0
 

Author Comment

by:Super Man05
ID: 40398407
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
ID: 40398599
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
ID: 40398619
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
ID: 40398978
Please ask another question
0
 

Author Comment

by:Super Man05
ID: 40404053
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
ID: 40404344
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

713 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