Oracle dynamic datetime range

I'm looking for a way to group dates as per below.

Code:

---


User    DateTime              Qty
a        May 29 11:40 PM        5
a        May 30 01:00 AM        6
a        May 30 11:55 PM        4
a        May 31 12:20 AM        9


---

I'm looking to have the following result.

Code:

---


User    FirstDate            Total
a        May 29                11
a        May 30                13


---

My only issue is isolating on the date range. I have no schedule and the first / last datetime will always be different. The only thing i can isolate on is that there will be a period without transactions for at least 10 hours to seperate these groups.


Here is the code i have so far.

Code:

---

select user, Min(trans_date), sum(qty)
FROM
(SELECT tr.user, qty, to_char(tr.TransDate,'DD-Mon HH:MI AM') as trans_date,
to_char(LEAD(tr.TransDate) OVER(ORDER BY tr.user, tr.TransDate ASC), 'DD-Mon HH:MI AM') as Next_Date,
(LEAD(tr.TransDate) OVER (ORDER BY tr.user, tr.TransDate ASC) - tr.TransDate) * 1440 as DateDiff
FROM Table1 tr
where tr.TransDate >= trunc(sysdate-3)
order by tr.user ASC)
where DateDiff >= 600
group by user
order by user

---

Hello NawtySpawn,
Wouldn't the result be displayed
like this?

Code:

---

User        FirstDate        Total
a        May 29                5
a        May 30                10
a        May 31                9

---

Am I missing something?

That would be the issue. I can't seem to get it to display correctly when the date range is over multiple days. I was attempting to compare the preivous lines in the query to check for a period without transactions for 10+ hours to seperate but failed.