Troublesome SQL Query - Missing Output

Hi,

I have a query:

SELECT HBR_NAME, REPLACE(SUBSTR(APP,11),'/','_') AS APP
                , 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,
                (end_date-start_date) * 86400 TOTAL_SECONDS 
FROM (
                SELECT HBR_NAME, APP,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 HP_CALC_LAUNCH
                
                GROUP BY HBR_NAME, APP,HBR_USER
                                

ORDER BY HBR_NAME,START_DATE
);

Open in new window


that produces the following output:

output_EE.gif
Unfortunately this is missing a number of records from the source table, source table:

src_EE.gif
If someone could help me resolve this "data" loss it would be great.

Thanks,
Mark

Note: Attached source file
EE_DATA_SRC_FILE.xlsx
SuperLightAsked:
Who is Participating?
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
as the query, as posted, does not have any WHERE or HAVING clause, it cannot be "missing" any records from the source table.

note that the inner query is doing a GROUP BY HBR_NAME, APP,HBR_USER, and has 2 operations using MAX( ) for START and END date

what would you expect as output, actually?
0
SuperLightAuthor Commented:
Hi,

In the output there should be multiple entries by User, it seems only to be referencing the user once.

Thanks,
Mark
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if all the values of APP and HBR_NAME are the same, per user, it shall not generated different output records.
and indeed, APP and HBR_NAME do have the same value for all the records, hence 1 output record per "HBR_USER" value.

please clarify why you expect several rows (based on what other "rule")
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
Look at the start_finish column for the 'missing' data:
INFO calcmgr.launch - Date/Time Started:
FATAL calcmgr.launch - Date/Time Ended:

Every other value is the same.

My SQL from your previous question only looks to see if 'Started' or 'Ended' is in the field.  That is the INSTR piece of the SQL.

I obviously missed an underlying requirement.

What is the rule for an INFO and FATAL for the same user?

Based in the data you provided here, what are the expected results for MGRIZZELL?
0
slightwv (䄆 Netminder) Commented:
Also, what about a missing "Ending" for:
INFO calcmgr.launch - Date/Time Started:      2015/06/20:08:00:34      localhost/APPpln/FINL      FN_Aggregate_Forecast      AGODWIN

What should that return?
0
slightwv (䄆 Netminder) Commented:
While we wait for the answers to the questions I asked, I'm guessing at requirements.

See if this is what you are after:
drop table tab1 purge;
create table tab1(START_FINISH varchar2(50), DATETIME date, APP varchar2(30), HBR_NAME varchar2(30), HBR_USER varchar2(30)); 
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/04/15:10:47:17','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/04/15:12:00:15','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','admin');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/04/16:16:56:05','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/04/16:18:34:15','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/05/14:11:30:23','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/05/14:12:02:15','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/06/06:16:35:58','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/06/06:17:12:10','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/06/19:19:51:21','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/06/19:20:44:22','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/06/20:08:00:34','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/07/08:08:27:41','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','MGRIZZELL');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/07/08:08:44:27','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','MGRIZZELL');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/07/15:07:40:26','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','MGRIZZELL');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/07/15:07:57:24','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','MGRIZZELL');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/08/02:09:47:16','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/08/02:10:12:36','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/08/31:15:24:08','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/08/31:18:05:01','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/09/03:16:22:19','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/09/03:18:32:41','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');
insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/09/14:16:35:53','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');
insert into tab1 values('INFO calcmgr.launch - Date/Time Ended:',to_date('2015/09/14:17:15:05','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','AGODWIN');

insert into tab1 values('INFO calcmgr.launch - Date/Time Started:',to_date('2015/10/12:09:09:46','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','MGRIZZELL');
insert into tab1 values('FATAL calcmgr.launch - Date/Time Ended:',to_date('2015/10/12:12:10:08','YYYY/MM/DD":"HH24:MI:SS'),'localhost/APPpln/FINL','FN_Aggregate_Forecast','MGRIZZELL');
commit;

select hbr_user,
	app,
	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,
	(end_date-start_date) * 86400 total_seconds 
from (
	select hbr_user,
		app,
		datetime start_date,
		case when lead(ended) over(partition by hbr_user order by hbr_user, datetime) = 1 then lead(datetime) over(partition by hbr_user order by hbr_user, datetime) end end_date
	from (
		select
			replace(substr(app,11),'/','_') as app,
			case when instr(start_finish,'Started') > 0 then 1 end started,
				case when instr(start_finish,'Ended') > 0 then 1 end ended,
			datetime,
			hbr_user
		from tab1
		order by hbr_user,datetime
	)
)
where end_date is not null
/

Open in new window

0

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:
Hi There,

Thanks for the quick response, I was able to run the SQL and I got the results I was looking for.  Your assumption on how to handle the "end_date" was correct.

Many Thanks,
Mark
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.