SQLTeam.com | Weblogs | Forums

Normalize transactions data - Oracle sql

oracle
tsql

#1

Hi there,

I have a table of changes in user statuses such as:

insert_time status
1/1/2017 0:00 AVAILABLE
1/1/2017 0:15 BUSY
1/1/2017 0:30 NOT AVAILABLE
1/1/2017 1:30 AVAILABLE
1/1/2017 3:10 BUSY
1/1/2017 5:00 NOT AVAILABLE

for example: this user was available between 00:00 and 00:15 and busy from 00:15 to 00:30 and so on. In order to analyze the data I need to transform it to this structure:

day hour available minutes not available minutes busy minutes
1/1/2017 0 15 30 15
1/1/2017 1 30 30 0
1/1/2017 2 60 0 0
1/1/2017 3 10 0 50
1/1/2017 4 0 0 60
Including data for hours that status has not been changed.
Any idea to how can I do it in an Oracle SQL query ?

Thanks, E


#2

This is a Microsoft SQL Server forum, so you may not find folk here that are familiar with Oracle.