user3191160 user3191160 - 2 months ago 7
SQL Question

How to select values for count in oracle sql

thanks to a friend of mine, I have been able to obtain the following code that gives me a count of dates/times based on a difference of 30 mins. If the difference is less than 30 mins than the count stays the same otherwise if its greater than the count goes up by 1.

SELECT
CASE
WHEN DATE2 - LAG(DATE2) over (PARTITION BY NAME, TRUNC(DATE2) ORDER BY DATE2) <= 1 / 48
THEN NULL
ELSE 1
END AS COUNT1
FROM TABLE1


What I forgot to ask him to include was the values that have been counted as the start time and end time for that count as this will allow me to workout how long was spent, but I am not sure how I can add this based on the code above so not attempted anything.

I would appreciate if somebody could please advise on how I can bring something like this as sql is not my strongest point.

The following is some sample data:

NAME | DATE2
Humpty Dumpty | 21-JUL-16 09:27:24
Humpty Dumpty | 21-JUL-16 09:27:24
Humpty Dumpty | 21-JUL-16 09:27:24
Humpty Dumpty | 21-JUL-16 09:27:24
Humpty Dumpty | 21-JUL-16 09:31:31
Humpty Dumpty | 21-JUL-16 09:31:31
Humpty Dumpty | 21-JUL-16 09:31:31
Humpty Dumpty | 21-JUL-16 09:31:31
Humpty Dumpty | 21-JUL-16 16:42:03
Humpty Dumpty | 21-JUL-16 16:42:03
Humpty Dumpty | 21-JUL-16 16:42:03
Humpty Dumpty | 21-JUL-16 16:42:03


When I ran the above query I get the following:

Count1
1
(null)
(null)
(null)
(null)
(null)
(null)
(null)
1
(null)
(null)
(null)


which gives me the value but I also need to know the first value for when the 1 is counted and the last value before the next one is counted.

From the above my results should look like:

Count1 | Start_time | End_Time
1 | 21-JUL-16 09:27:24 | 21-JUL-16 09:31:31
1 | 21-JUL-16 16:42:03 | 21-JUL-16 16:42:03


One thing to point out is if there is another value for the same date within half hour, then the start time is also the end time..

Answer

You want to identify the different groups. Because you are using the lag() method, I will continue with that. The next step is a cumulative sum, then aggregation:

SELECT NAME, TRUNC(DATE2), MIN(DATE2), MAX(DATE2)
FROM (SELECT t1.*,
             SUM(COUNT1) OVER (PARTITION BY NAME, TRUNC(DATE2) ORDER BY DATE2) as grp
      FROM (SELECT t1.*,
                   (CASE WHEN DATE2 - LAG(DATE2) over (PARTITION BY NAME, TRUNC(DATE2) ORDER BY DATE2) <= 1/48  
                         THEN 0 ELSE 1
                    END) AS COUNT1
            FROM TABLE1 t1
           ) t1
      ) t1
GROUP BY NAME, TRUNC(DATE2), grp;