asked on

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:

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

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

Thanks,
Mark

Note: Attached source file
EE_DATA_SRC_FILE.xlsx

Guy Hengel [angelIII / a3]

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?

SuperLight

ASKER

Hi,

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

Thanks,
Mark

Guy Hengel [angelIII / a3]

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")

slightwv (䄆 Netminder)

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?

slightwv (䄆 Netminder)

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?

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SuperLight

ASKER

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