CODEWITHSUNDEEP
sqloracle-database
mayank
mayank

Reputation: 25

How to get start date and end date from the given date column according to task completion in a day (oracle)

Here in dummy data, you can see there are two columns Task_Completion and Time_stamp.there is a java scheduler that runs whenever the task is finish, for example, scheduler run on 15-FEB-2016 5 times and on 17-FEB only time So I wanted a query that calculates the start_time and end_time from the given column time_stamp

Task_Completion                     Time_stamp
true                     15-FEB-16 11.37.56.013000000 AM
true                     15-FEB-16 11.42.55.593000000 AM
true                     15-FEB-16 11.47.48.970000000 AM
true                     15-FEB-16 12.21.57.587000000 PM
true                     15-FEB-16 12.26.55.767000000 PM 
true                     17-FEB-16 10.24.03.320000000 PM
true                     18-FEB-16 10.19.04.333000000 PM

So the output must be like

       start_time                                      end_time
15-FEB-16 11.37.56.013000000 AM          15-FEB-16 11.47.48.970000000 AM
15-FEB-16 12.21.57.587000000 PM          15-FEB-16 12.26.55.767000000 PM 
17-FEB-16 10.21.33.320000000 PM          17-FEB-16 10.26.33.320000000 PM
18-FEB-16 10.16.33.333000000 PM          18-FEB-16 10.21.33.333000000 PM

There are two conditions

condition 1. if there is a 0-5min gap in time_stamp for example on 15-Feb then start_time will be 11.37.56.013000000 and end_time will be 11.47.48.970000000 but if there is not, then again check is there any schedule runs on that day like on 15-Feb at 12.21.57.587000000(start_time) and 12.26.55.767000000(end_time)

condition2. if the schedule runs only once in a day then avg that time_stamp and add/sub 2.30 min from that time (i.e.) no need to check again

For Example

17-Feb 10.21.33.320000000(start_time) and 10.26.33.320000000(end_time) same as 18-Feb

So my logic was, to do a union of both condition1 and condition2 and this is I what end up with ...

--------------------condition2-------------------

select count(*), min(time_stamp) - interval '150' second start_date, max(time_stamp) + interval '150' second end_date
from serverstatus 
group by to_char(time_stamp,'dd-mon-yy')
having count(*)=1
UNION ALL

------------condition1 query--------------------------

I need Condition1 Query

Upvotes: 0

Views: 120

Answers (1)

MT0
MT0

Reputation: 167972

SELECT start_time - CASE num_grp_per_day
                      WHEN 1
                      THEN INTERVAL '150' SECOND
                      ELSE INTERVAL '0' SECOND
                    END AS start_time,
       end_time   + CASE num_grp_per_day
                      WHEN 1
                      THEN INTERVAL '150' SECOND
                      ELSE INTERVAL '0' SECOND
                    END AS end_time
FROM   (
  SELECT DISTINCT
         MIN( time_stamp ) OVER ( PARTITION BY grp ) AS start_time,
         MAX( time_stamp ) OVER ( PARTITION BY grp ) AS end_time,
         COUNT( DISTINCT grp ) OVER ( PARTITION BY TRUNC( time_stamp ) ) AS num_grp_per_day
  FROM   (
    SELECT time_stamp,
           SUM( diff ) OVER ( ORDER BY time_stamp ) AS grp
    FROM   (
      SELECT time_stamp,
             CASE
               WHEN time_stamp - LAG( time_stamp ) OVER ( ORDER BY time_stamp )
                      <= INTERVAL '5' MINUTE
               THEN 0
               ELSE 1
             END AS diff
      FROM   your_table
    )
  )
)
ORDER BY start_time;

or, if you just want to expand all zero-width groups to a 5-minute interval (and not just when there is one per day) then:

SELECT MIN( time_stamp ) - CASE COUNT(*)
                             WHEN 1
                             THEN INTERVAL '150' SECOND
                             ELSE INTERVAL '0' SECOND
                           END AS start_time,
       MAX( time_stamp ) + CASE COUNT(*)
                             WHEN 1
                             THEN INTERVAL '150' SECOND
                             ELSE INTERVAL '0' SECOND
                           END AS end_time
FROM   (
  SELECT time_stamp,
         SUM( diff ) OVER ( ORDER BY time_stamp ) AS grp
  FROM   (
    SELECT time_stamp,
           CASE
             WHEN time_stamp - LAG( time_stamp ) OVER ( ORDER BY time_stamp )
                    <= INTERVAL '5' MINUTE
             THEN 0
             ELSE 1
           END AS diff
    FROM   your_table
  )
)
GROUP BY grp;

Upvotes: 1

Related Questions