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
Re: Oracle dynamic datetime range
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?
Re: Oracle dynamic datetime range
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.