Funky Oracle SQL Pivot Problem

Hi There,

I have a table within an Oracle Database:

SQLTABLE.gif
The data in the table has been loaded from a log file and this contains details about the execution of calculations that are entered in a chronological order.  The calculations start/finish time can overlap because the calculations have different durations.

I am wondering if it is possible to create a Crosstab query that will contain:
START_FINISH values as Column Headers
HBR_NAME values as the Rows
DATETIME value as the values within the CrossTab

For example:

SQLTABLE_CALC.gif(Note: the last column I can derive - but if SQL can do this then I'd like to do it)

When I've had to process files like this before I used a script to parse through file to create the correct format, I'm hoping I can achieve the same within SQL.

Many Thanks in advance,
Mark
SuperLightAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Seems like it should be pretty simple.

Can you provide actual data instead of an image?  Then we can create a actual test case and provide tested code.
SuperLightAuthor Commented:
As a text file?
slightwv (䄆 Netminder) Commented:
Text file, Excel, anything where we don't have to retype everything.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SuperLightAuthor Commented:
Here's a sample txt file (if you want/need a different format let me know).

Also, there will be additional users not just admin.

Many Thanks,
Mark
CalcDataFile.txt
slightwv (䄆 Netminder) Commented:
That doesn't have the time portion for the dates.

Can you also provide examples of other users?

How do you want the data from individual users displayed?  The provided expected results don't show a user.
SuperLightAuthor Commented:
Sorry about that - here's a better file

Thanks
EE_CalcLaunchSourceFile.xlsx
slightwv (䄆 Netminder) Commented:
Thanks.  That should work.

Now about the expected results?  How should it look for the multiple users?
slightwv (䄆 Netminder) Commented:
In the users field, should the periods at the end be ignored or are they different users?
SuperLightAuthor Commented:
If the output could look like this...

new-output.gif
It would be great.

Thanks,
Mark
slightwv (䄆 Netminder) Commented:
I only used the first few test records.  I also removed the '.' from the end of the user data.

Here is what I came up with:
drop table tab1 purge;
create table tab1(START_FINISH varchar2(50), DATETIME date, HBR_NAME varchar2(50), HBR_USER varchar2(10));

insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/04/01:10:40:02','YYYY/MM/DD":"HH24:MI:SS'),'FN_ActualLoad_PopulateActuals_HMCTS_LAA','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/04/01:11:27:23','YYYY/MM/DD":"HH24:MI:SS'),'FN_DefaultForecastDriver_Inflation','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/04/01:11:27:24','YYYY/MM/DD":"HH24:MI:SS'),'FN_DefaultForecastDriver_Inflation','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/04/01:11:27:56','YYYY/MM/DD":"HH24:MI:SS'),'FN_DefaultForecastDriver_RunRate','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/04/01:11:27:58','YYYY/MM/DD":"HH24:MI:SS'),'FN_DefaultForecastDriver_RunRate','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/04/01:11:29:34','YYYY/MM/DD":"HH24:MI:SS'),'FN_ForecastFutureYear','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/04/01:11:37:10','YYYY/MM/DD":"HH24:MI:SS'),'FN_ForecastFutureYear','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/04/01:11:49:54','YYYY/MM/DD":"HH24:MI:SS'),'FN_ActualLoad_PopulateActuals_HMCTS_LAA','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/04/01:12:26:22','YYYY/MM/DD":"HH24:MI:SS'),'FN_AggregateAdjustments_FrmEntity','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/04/01:12:27:37','YYYY/MM/DD":"HH24:MI:SS'),'FN_AggregateAdjustments_FrmEntity','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/04/01:12:28:58','YYYY/MM/DD":"HH24:MI:SS'),'FN_AggregateAdjustments_FrmEntity','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/04/01:12:30:00','YYYY/MM/DD":"HH24:MI:SS'),'FN_AggregateAdjustments_FrmEntity','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/04/10:16:20:42','YYYY/MM/DD":"HH24:MI:SS'),'FcstLD_Data_Exp','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/04/10:16:40:27','YYYY/MM/DD":"HH24:MI:SS'),'FcstLD_Data_Exp','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/04/14:14:09:44','YYYY/MM/DD":"HH24:MI:SS'),'Clr_Act_FCst_Mar','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/04/14:18:41:18','YYYY/MM/DD":"HH24:MI:SS'),'Clr_Act_FCst_Mar','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/04/15:09:58:19','YYYY/MM/DD":"HH24:MI:SS'),'FN_Aggregate_Budget','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/04/15:10:13:10','YYYY/MM/DD":"HH24:MI:SS'),'FN_Aggregate_Budget','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/04/15:10:19:00','YYYY/MM/DD":"HH24:MI:SS'),'FN_Aggregate_Actual','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/04/15:10:47:17','YYYY/MM/DD":"HH24:MI:SS'),'FN_Aggregate_Forecast','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/04/15:11:55:55','YYYY/MM/DD":"HH24:MI:SS'),'FN_ALB_Actuals_Process','USER_123');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/04/15:12:00:15','YYYY/MM/DD":"HH24:MI:SS'),'FN_Aggregate_Forecast','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/04/15:12:04:35','YYYY/MM/DD":"HH24:MI:SS'),'FN_Aggregate_Actual','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/04/15:12:28:58','YYYY/MM/DD":"HH24:MI:SS'),'FN_ALB_Actuals_Process','USER_123');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/04/16:16:56:05','YYYY/MM/DD":"HH24:MI:SS'),'FN_Aggregate_Forecast','USER_123');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/04/16:18:34:15','YYYY/MM/DD":"HH24:MI:SS'),'FN_Aggregate_Forecast','USER_123');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/04/17:14:46:25','YYYY/MM/DD":"HH24:MI:SS'),'FN_AggregateAdjustments_SmartView','USER_456');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/04/17:15:00:39','YYYY/MM/DD":"HH24:MI:SS'),'FN_AggregateAdjustments_SmartView','USER_456');
commit;

select hbr_name, hbr_user, start_date, end_date,
                to_char(extract(hour from to_timestamp(end_date)- to_timestamp(start_date)),'fm00')||':'||
                to_char(extract(minute from to_timestamp(end_date)- to_timestamp(start_date)),'fm00')||':'||
                to_char(extract(second from to_timestamp(end_date)- to_timestamp(start_date)),'fm00') total_time
from (
                select hbr_name,
                                hbr_user,
                                max(case when instr(start_finish,'Started') > 0 then datetime end) start_date,
                                max(case when instr(start_finish,'Ended') > 0 then datetime end) end_date
                from tab1
                group by hbr_name, 
                                hbr_user
)
order by start_date
/

Open in new window

slightwv (䄆 Netminder) Commented:
I was looking for a way to clean up the time difference.

I forgot about numtodsinterval.

Try this:
select hbr_name, hbr_user, start_date, end_date,
                to_char(extract(hour from numtodsinterval(end_date-start_date, 'day')),'fm00')||':'||
                to_char(extract(minute from numtodsinterval(end_date-start_date, 'day')),'fm00')||':'||
                to_char(extract(second from numtodsinterval(end_date-start_date, 'day')),'fm00') total_time
from (
                select hbr_name,
                                hbr_user,
                                max(case when instr(start_finish,'Started') > 0 then datetime end) start_date,
                                max(case when instr(start_finish,'Ended') > 0 then datetime end) end_date
                from tab1
                group by hbr_name, 
                                hbr_user
)
order by start_date
/

Open in new window

SuperLightAuthor Commented:
This is great - thanks!

Because of the way I want to load the data for "Total_Time" could you include the conversion a number.

Regards,
Mark
slightwv (䄆 Netminder) Commented:
For the first record, your expected results show:  01:09:52
What do you want it to be?
slightwv (䄆 Netminder) Commented:
If the number of days between them, it is easy:

select hbr_name, hbr_user, start_date, end_date, end_date-start_date days_between
from
...


Then you can convert that into any number you want, like total hours, minutes or seconds between with simple math.
SuperLightAuthor Commented:
I would want to see the HH:MI:SS converted to something like 1.0445 - depending upon what the time is
SuperLightAuthor Commented:
or seconds.  I can the calculate the minutes and get the numeric time, for example:

00:01:15 = 75 secs

I can then

75 / 60 = 1.25
slightwv (䄆 Netminder) Commented:
For seconds, simple math:

select hbr_name, hbr_user, start_date, end_date, (end_date-start_date) * 86400 seconds_between
 from
 ...
slightwv (䄆 Netminder) Commented:
If the end game is hours, why go to seconds first?  Just go straight to hours...

 (end_date-start_date) * 24 hours_between
SuperLightAuthor Commented:
Hi,

Seconds is good, just struggling with  the syntax!

Mark
slightwv (䄆 Netminder) Commented:
>>just struggling with  the syntax!

Getting it to work or understanding what I posted?
SuperLightAuthor Commented:
Where to make the change to the original script to get the seconds
slightwv (䄆 Netminder) Commented:
I posted it.  I just included the outer select piece that needed changed.

Here is the full thing:
select hbr_name, hbr_user, start_date, end_date, (end_date-start_date) * 86400 seconds_between
from (
                select hbr_name,
                                hbr_user,
                                max(case when instr(start_finish,'Started') > 0 then datetime end) start_date,
                                max(case when instr(start_finish,'Ended') > 0 then datetime end) end_date
                from tab1
                group by hbr_name, 
                                hbr_user
)
order by start_date
/

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SuperLightAuthor Commented:
I realised where I was making the mistake just before you posted up.

Thanks for your prompt responses and great answer.

Mark
SuperLightAuthor Commented:
Extremely helpful and attentive.

Mark
slightwv (䄆 Netminder) Commented:
Glad to help!
SuperLightAuthor Commented:
Hi slightwv,

I've raised question about the SQL Query you helped (aka built) me with earlier - I thought as you're familiar with it I'd ping you and give you a chance to earn some more points! ;o).

It seems to behave like a "select distinct", therefore is a user has multiple entries for a entry in the HBR_NAME field it will only show one (or that appears to be the behaviour).

thanks,
Mark
SuperLightAuthor Commented:
The new question is titled: "Troublesome SQL Query - Missing Output"

Laters
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.