Oracle dynamic datetime range

[Nawtyspawn]

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

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.

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.

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

[abhijit]

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

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

Am I missing something?

[Nawtyspawn]

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.