Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Mohammad Rafi.

Asked: January 14, 2019 - 8:09 pm UTC

Last updated: January 18, 2019 - 11:41 am UTC

Version: 12.2.0.20.64

Viewed 1000+ times

You Asked

Hi,

I am looking for some help on the SQL given in Live SQL Link ( https://livesql.oracle.com/apex/f?p=590:43:100370634308121:::43:P43_ID:169399633855075789353470383313660811797 ). Below is the SQL which I have created. There is one column named STREAM_REFRESH_DATE. Currently it is showing date whenever HRS_DIFFERENCE > 10. But I want to show that date from STREAM_JOB_1 to STREAM_JOB_10 , similarly for others.

WITH A ( STREAM_NAME, RUN_ID, STREAM_JOB_ID, START_TIME, COMPLETE_TIME ) AS 
    ( 
        SELECT 'STREAM_JOB_10', 
            '11111111', 
            '30', 
            CAST ( '13-JAN-19 12.49.14.006840000 AM' AS TIMESTAMP ), 
            CAST ( '13-JAN-19 01.23.58.518754000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_9', 
            '11111111', 
            '29', 
            CAST ( '13-JAN-19 12.30.31.092133000 AM' AS TIMESTAMP ), 
            CAST ( '13-JAN-19 12.49.11.860234000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_8', 
            '11111111', 
            '28', 
            CAST ( '13-JAN-19 12.23.30.519221000 AM' AS TIMESTAMP ), 
            CAST ( '13-JAN-19 12.30.20.660338000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_7', 
            '11111111', 
            '27', 
            CAST ( '13-JAN-19 12.16.46.414043000 AM' AS TIMESTAMP ), 
            CAST ( '13-JAN-19 12.23.28.408903000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_6', 
            '11111111', 
            '26', 
            CAST ( '12-JAN-19 11.44.42.610016000 PM' AS TIMESTAMP ), 
            CAST ( '13-JAN-19 12.16.44.225525000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_5', 
            '11111111', 
            '25', 
            CAST ( '12-JAN-19 11.25.56.546745000 PM' AS TIMESTAMP ), 
            CAST ( '12-JAN-19 11.44.32.897426000 PM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_4', 
            '11111111', 
            '24', 
            CAST ( '12-JAN-19 11.03.25.611492000 PM' AS TIMESTAMP ), 
            CAST ( '12-JAN-19 11.25.53.561345000 PM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_3', 
            '11111111', 
            '23', 
            CAST ( '12-JAN-19 10.03.26.816367000 PM' AS TIMESTAMP ), 
            CAST ( '12-JAN-19 11.03.23.454802000 PM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_2', 
            '11111111', 
            '22', 
            CAST ( '12-JAN-19 09.16.33.937077000 PM' AS TIMESTAMP ), 
            CAST ( '12-JAN-19 10.03.24.599359000 PM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_1', 
            '11111111', 
            '21', 
            CAST ( '12-JAN-19 08.31.53.090829000 PM' AS TIMESTAMP ), 
            CAST ( '12-JAN-19 09.16.29.704729000 PM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_10', 
            '11111111', 
            '30', 
            CAST ( '12-JAN-19 04.54.15.204137000 AM' AS TIMESTAMP ), 
            CAST ( '12-JAN-19 05.29.04.933231000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_9', 
            '11111111', 
            '29', 
            CAST ( '12-JAN-19 04.34.17.166108000 AM' AS TIMESTAMP ), 
            CAST ( '12-JAN-19 04.54.13.028900000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_8', 
            '11111111', 
            '28', 
            CAST ( '12-JAN-19 04.27.39.468042000 AM' AS TIMESTAMP ), 
            CAST ( '12-JAN-19 04.34.15.035219000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_7', 
            '11111111', 
            '27', 
            CAST ( '12-JAN-19 04.20.24.390687000 AM' AS TIMESTAMP ), 
            CAST ( '12-JAN-19 04.27.37.324884000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_6', 
            '11111111', 
            '26', 
            CAST ( '12-JAN-19 03.50.27.163671000 AM' AS TIMESTAMP ), 
            CAST ( '12-JAN-19 04.20.22.179774000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_5', 
            '11111111', 
            '25', 
            CAST ( '12-JAN-19 03.33.25.138657000 AM' AS TIMESTAMP ), 
            CAST ( '12-JAN-19 03.50.24.951625000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_4', 
            '11111111', 
            '24', 
            CAST ( '12-JAN-19 03.11.51.491252000 AM' AS TIMESTAMP ), 
            CAST ( '12-JAN-19 03.33.22.174462000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_3', 
            '11111111', 
            '23', 
            CAST ( '12-JAN-19 02.18.06.351222000 AM' AS TIMESTAMP ), 
            CAST ( '12-JAN-19 03.11.42.504240000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_2', 
            '11111111', 
            '22', 
            CAST ( '12-JAN-19 01.29.42.824729000 AM' AS TIMESTAMP ), 
            CAST ( '12-JAN-19 02.18.04.854562000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_2', 
            '11111111', 
            '22', 
            CAST ( '12-JAN-19 12.39.17.824683000 AM' AS TIMESTAMP ), 
            CAST ( '12-JAN-19 01.24.03.245613000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_1', 
            '11111111', 
            '21', 
            CAST ( '11-JAN-19 11.55.26.614900000 PM' AS TIMESTAMP ), 
            CAST ( '12-JAN-19 12.39.02.660684000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_10', 
            '11111111', 
            '30', 
            CAST ( '11-JAN-19 03.27.36.605861000 AM' AS TIMESTAMP ), 
            CAST ( '11-JAN-19 04.00.53.448994000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_9', 
            '11111111', 
            '29', 
            CAST ( '11-JAN-19 03.11.22.907790000 AM' AS TIMESTAMP ), 
            CAST ( '11-JAN-19 03.27.34.498352000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_8', 
            '11111111', 
            '28', 
            CAST ( '11-JAN-19 03.05.29.913192000 AM' AS TIMESTAMP ), 
            CAST ( '11-JAN-19 03.11.20.766575000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_7', 
            '11111111', 
            '27', 
            CAST ( '11-JAN-19 02.58.36.771560000 AM' AS TIMESTAMP ), 
            CAST ( '11-JAN-19 03.05.19.101087000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_6', 
            '11111111', 
            '26', 
            CAST ( '11-JAN-19 02.30.17.317887000 AM' AS TIMESTAMP ), 
            CAST ( '11-JAN-19 02.58.34.654754000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_5', 
            '11111111', 
            '25', 
            CAST ( '11-JAN-19 02.13.19.408260000 AM' AS TIMESTAMP ), 
            CAST ( '11-JAN-19 02.30.15.078490000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_4', 
            '11111111', 
            '24', 
            CAST ( '11-JAN-19 01.53.04.823566000 AM' AS TIMESTAMP ), 
            CAST ( '11-JAN-19 02.13.16.631077000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_3', 
            '11111111', 
            '23', 
            CAST ( '11-JAN-19 01.05.55.439894000 AM' AS TIMESTAMP ), 
            CAST ( '11-JAN-19 01.53.02.618141000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_2', 
            '11111111', 
            '22', 
            CAST ( '11-JAN-19 12.19.31.222547000 AM' AS TIMESTAMP ), 
            CAST ( '11-JAN-19 01.05.51.316795000 AM' AS TIMESTAMP ) 
        FROM DUAL 
        UNION ALL 
        SELECT 'STREAM_JOB_1', 
            '11111111', 
            '21', 
            CAST ( '10-JAN-19 11.34.22.818929000 PM' AS TIMESTAMP ), 
            CAST ( '11-JAN-19 12.19.19.064114000 AM' AS TIMESTAMP ) 
        FROM DUAL 
    ) 
    , 
    B AS -- for calculating LAST_DAY_START_TIME and DIFF 
    ( 
        SELECT STREAM_NAME, 
            RUN_ID, 
            STREAM_JOB_ID, 
            START_TIME, 
            COMPLETE_TIME, 
            NVL ( LEAD ( START_TIME, 1 ) OVER ( ORDER BY START_TIME DESC ), START_TIME )              AS LAST_DAY_START_TIME, 
            START_TIME - NVL ( LEAD ( START_TIME, 1 ) OVER ( ORDER BY START_TIME DESC ), START_TIME ) AS DIFF 
        FROM A 
        ORDER BY START_TIME DESC 
    ) 
SELECT STREAM_NAME, 
    RUN_ID, 
    STREAM_JOB_ID, 
    START_TIME, 
    COMPLETE_TIME, 
    LAST_DAY_START_TIME, 
    ABS ( 24 * EXTRACT ( DAY FROM DIFF ) + EXTRACT ( HOUR FROM DIFF ) ) HRS_DIFFERENCE, 
    CASE 
        WHEN ABS ( 24 * EXTRACT ( DAY FROM DIFF ) + EXTRACT ( HOUR FROM DIFF ) ) > 10 
        THEN TRUNC ( START_TIME ) 
        ELSE NULL 
    END AS DW_REFRESH_START_DATE  
FROM B

and Chris said...

I'm not sure exactly which date you're wanting to show.

If you want to repeat the last non-null value for the dw_refresh_start_date, you can use last_value. With the ignore nulls option.

For example:

alter session set nls_timestamp_format = 'DD-MON-YY HH.MI.SS.FF PM';

WITH A ( STREAM_NAME, RUN_ID, STREAM_JOB_ID, START_TIME, COMPLETE_TIME ) AS
(
SELECT 'STREAM_JOB_10',
'11111111',
'30',
CAST ( '13-JAN-19 12.49.14.006840000 AM' AS TIMESTAMP ),
CAST ( '13-JAN-19 01.23.58.518754000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_9',
'11111111',
'29',
CAST ( '13-JAN-19 12.30.31.092133000 AM' AS TIMESTAMP ),
CAST ( '13-JAN-19 12.49.11.860234000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_8',
'11111111',
'28',
CAST ( '13-JAN-19 12.23.30.519221000 AM' AS TIMESTAMP ),
CAST ( '13-JAN-19 12.30.20.660338000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_7',
'11111111',
'27',
CAST ( '13-JAN-19 12.16.46.414043000 AM' AS TIMESTAMP ),
CAST ( '13-JAN-19 12.23.28.408903000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_6',
'11111111',
'26',
CAST ( '12-JAN-19 11.44.42.610016000 PM' AS TIMESTAMP ),
CAST ( '13-JAN-19 12.16.44.225525000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_5',
'11111111',
'25',
CAST ( '12-JAN-19 11.25.56.546745000 PM' AS TIMESTAMP ),
CAST ( '12-JAN-19 11.44.32.897426000 PM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_4',
'11111111',
'24',
CAST ( '12-JAN-19 11.03.25.611492000 PM' AS TIMESTAMP ),
CAST ( '12-JAN-19 11.25.53.561345000 PM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_3',
'11111111',
'23',
CAST ( '12-JAN-19 10.03.26.816367000 PM' AS TIMESTAMP ),
CAST ( '12-JAN-19 11.03.23.454802000 PM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_2',
'11111111',
'22',
CAST ( '12-JAN-19 09.16.33.937077000 PM' AS TIMESTAMP ),
CAST ( '12-JAN-19 10.03.24.599359000 PM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_1',
'11111111',
'21',
CAST ( '12-JAN-19 08.31.53.090829000 PM' AS TIMESTAMP ),
CAST ( '12-JAN-19 09.16.29.704729000 PM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_10',
'11111111',
'30',
CAST ( '12-JAN-19 04.54.15.204137000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 05.29.04.933231000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_9',
'11111111',
'29',
CAST ( '12-JAN-19 04.34.17.166108000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 04.54.13.028900000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_8',
'11111111',
'28',
CAST ( '12-JAN-19 04.27.39.468042000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 04.34.15.035219000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_7',
'11111111',
'27',
CAST ( '12-JAN-19 04.20.24.390687000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 04.27.37.324884000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_6',
'11111111',
'26',
CAST ( '12-JAN-19 03.50.27.163671000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 04.20.22.179774000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_5',
'11111111',
'25',
CAST ( '12-JAN-19 03.33.25.138657000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 03.50.24.951625000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_4',
'11111111',
'24',
CAST ( '12-JAN-19 03.11.51.491252000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 03.33.22.174462000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_3',
'11111111',
'23',
CAST ( '12-JAN-19 02.18.06.351222000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 03.11.42.504240000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_2',
'11111111',
'22',
CAST ( '12-JAN-19 01.29.42.824729000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 02.18.04.854562000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_2',
'11111111',
'22',
CAST ( '12-JAN-19 12.39.17.824683000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 01.24.03.245613000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_1',
'11111111',
'21',
CAST ( '11-JAN-19 11.55.26.614900000 PM' AS TIMESTAMP ),
CAST ( '12-JAN-19 12.39.02.660684000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_10',
'11111111',
'30',
CAST ( '11-JAN-19 03.27.36.605861000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 04.00.53.448994000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_9',
'11111111',
'29',
CAST ( '11-JAN-19 03.11.22.907790000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 03.27.34.498352000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_8',
'11111111',
'28',
CAST ( '11-JAN-19 03.05.29.913192000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 03.11.20.766575000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_7',
'11111111',
'27',
CAST ( '11-JAN-19 02.58.36.771560000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 03.05.19.101087000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_6',
'11111111',
'26',
CAST ( '11-JAN-19 02.30.17.317887000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 02.58.34.654754000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_5',
'11111111',
'25',
CAST ( '11-JAN-19 02.13.19.408260000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 02.30.15.078490000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_4',
'11111111',
'24',
CAST ( '11-JAN-19 01.53.04.823566000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 02.13.16.631077000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_3',
'11111111',
'23',
CAST ( '11-JAN-19 01.05.55.439894000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 01.53.02.618141000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_2',
'11111111',
'22',
CAST ( '11-JAN-19 12.19.31.222547000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 01.05.51.316795000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_1',
'11111111',
'21',
CAST ( '10-JAN-19 11.34.22.818929000 PM' AS TIMESTAMP ),
CAST ( '11-JAN-19 12.19.19.064114000 AM' AS TIMESTAMP )
FROM DUAL
),
B AS -- for calculating LAST_DAY_START_TIME and DIFF
(
SELECT STREAM_NAME,
RUN_ID,
STREAM_JOB_ID,
START_TIME,
COMPLETE_TIME,
NVL ( LEAD ( START_TIME, 1 ) OVER ( ORDER BY START_TIME DESC ), START_TIME ) AS LAST_DAY_START_TIME,
START_TIME - NVL ( LEAD ( START_TIME, 1 ) OVER ( ORDER BY START_TIME DESC ), START_TIME ) AS DIFF
FROM A
ORDER BY START_TIME DESC
), START_DATES as (
SELECT STREAM_NAME,
RUN_ID,
STREAM_JOB_ID,
START_TIME,
COMPLETE_TIME,
LAST_DAY_START_TIME,
DIFF, 
ABS ( 24 * EXTRACT ( DAY FROM DIFF ) + EXTRACT ( HOUR FROM DIFF ) ) HRS_DIFFERENCE,
CASE
WHEN ABS ( 24 * EXTRACT ( DAY FROM DIFF ) + EXTRACT ( HOUR FROM DIFF ) ) > 10
THEN TRUNC ( START_TIME )
ELSE NULL
END AS DW_REFRESH_START_DATE
FROM B
)
  select stream_name, start_time,
         dw_refresh_start_date,
         last_value ( dw_refresh_start_date )
           ignore nulls over (
             order by start_time
           ) last_date
  from   start_dates
  order  by start_time;

STREAM_NAME     START_TIME                        DW_REFRESH_START_DATE   LAST_DATE              
STREAM_JOB_1    10-JAN-19 11.34.22.818929000 PM   <null>                  <null>                 
STREAM_JOB_2    11-JAN-19 12.19.31.222547000 AM   <null>                  <null>                 
STREAM_JOB_3    11-JAN-19 01.05.55.439894000 AM   <null>                  <null>                 
STREAM_JOB_4    11-JAN-19 01.53.04.823566000 AM   <null>                  <null>                 
STREAM_JOB_5    11-JAN-19 02.13.19.408260000 AM   <null>                  <null>                 
STREAM_JOB_6    11-JAN-19 02.30.17.317887000 AM   <null>                  <null>                 
STREAM_JOB_7    11-JAN-19 02.58.36.771560000 AM   <null>                  <null>                 
STREAM_JOB_8    11-JAN-19 03.05.29.913192000 AM   <null>                  <null>                 
STREAM_JOB_9    11-JAN-19 03.11.22.907790000 AM   <null>                  <null>                 
STREAM_JOB_10   11-JAN-19 03.27.36.605861000 AM   <null>                  <null>                 
STREAM_JOB_1    11-JAN-19 11.55.26.614900000 PM   11-JAN-2019 00:00:00    11-JAN-2019 00:00:00   
STREAM_JOB_2    12-JAN-19 12.39.17.824683000 AM   <null>                  11-JAN-2019 00:00:00   
STREAM_JOB_2    12-JAN-19 01.29.42.824729000 AM   <null>                  11-JAN-2019 00:00:00   
STREAM_JOB_3    12-JAN-19 02.18.06.351222000 AM   <null>                  11-JAN-2019 00:00:00   
STREAM_JOB_4    12-JAN-19 03.11.51.491252000 AM   <null>                  11-JAN-2019 00:00:00   
STREAM_JOB_5    12-JAN-19 03.33.25.138657000 AM   <null>                  11-JAN-2019 00:00:00   
STREAM_JOB_6    12-JAN-19 03.50.27.163671000 AM   <null>                  11-JAN-2019 00:00:00   
STREAM_JOB_7    12-JAN-19 04.20.24.390687000 AM   <null>                  11-JAN-2019 00:00:00   
STREAM_JOB_8    12-JAN-19 04.27.39.468042000 AM   <null>                  11-JAN-2019 00:00:00   
STREAM_JOB_9    12-JAN-19 04.34.17.166108000 AM   <null>                  11-JAN-2019 00:00:00   
STREAM_JOB_10   12-JAN-19 04.54.15.204137000 AM   <null>                  11-JAN-2019 00:00:00   
STREAM_JOB_1    12-JAN-19 08.31.53.090829000 PM   12-JAN-2019 00:00:00    12-JAN-2019 00:00:00   
STREAM_JOB_2    12-JAN-19 09.16.33.937077000 PM   <null>                  12-JAN-2019 00:00:00   
STREAM_JOB_3    12-JAN-19 10.03.26.816367000 PM   <null>                  12-JAN-2019 00:00:00   
STREAM_JOB_4    12-JAN-19 11.03.25.611492000 PM   <null>                  12-JAN-2019 00:00:00   
STREAM_JOB_5    12-JAN-19 11.25.56.546745000 PM   <null>                  12-JAN-2019 00:00:00   
STREAM_JOB_6    12-JAN-19 11.44.42.610016000 PM   <null>                  12-JAN-2019 00:00:00   
STREAM_JOB_7    13-JAN-19 12.16.46.414043000 AM   <null>                  12-JAN-2019 00:00:00   
STREAM_JOB_8    13-JAN-19 12.23.30.519221000 AM   <null>                  12-JAN-2019 00:00:00   
STREAM_JOB_9    13-JAN-19 12.30.31.092133000 AM   <null>                  12-JAN-2019 00:00:00   
STREAM_JOB_10   13-JAN-19 12.49.14.006840000 AM   <null>                  12-JAN-2019 00:00:00


PS - when sharing LiveSQL scripts, you need to save your session. This will give you a URL you can share.

Copying from your browser's address bar gives an URL tied to your specific session. Which we can't see :(

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

It works

Mohammad Rafi Ansari, January 15, 2019 - 6:39 pm UTC

Thanks Chris. This was help. I will alter it little bit as per my need.

Connor McDonald
January 16, 2019 - 2:54 am UTC

glad we could help

Updated Script

Mohammad Rafi Ansari, January 15, 2019 - 6:51 pm UTC

WITH A ( STREAM_NAME, RUN_ID, STREAM_JOB_ID, START_TIME, COMPLETE_TIME ) AS
(
SELECT 'STREAM_JOB_10',
'11111111',
'30',
CAST ( '13-JAN-19 12.49.14.006840000 AM' AS TIMESTAMP ),
CAST ( '13-JAN-19 01.23.58.518754000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_9',
'11111111',
'29',
CAST ( '13-JAN-19 12.30.31.092133000 AM' AS TIMESTAMP ),
CAST ( '13-JAN-19 12.49.11.860234000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_8',
'11111111',
'28',
CAST ( '13-JAN-19 12.23.30.519221000 AM' AS TIMESTAMP ),
CAST ( '13-JAN-19 12.30.20.660338000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_7',
'11111111',
'27',
CAST ( '13-JAN-19 12.16.46.414043000 AM' AS TIMESTAMP ),
CAST ( '13-JAN-19 12.23.28.408903000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_6',
'11111111',
'26',
CAST ( '12-JAN-19 11.44.42.610016000 PM' AS TIMESTAMP ),
CAST ( '13-JAN-19 12.16.44.225525000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_5',
'11111111',
'25',
CAST ( '12-JAN-19 11.25.56.546745000 PM' AS TIMESTAMP ),
CAST ( '12-JAN-19 11.44.32.897426000 PM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_4',
'11111111',
'24',
CAST ( '12-JAN-19 11.03.25.611492000 PM' AS TIMESTAMP ),
CAST ( '12-JAN-19 11.25.53.561345000 PM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_3',
'11111111',
'23',
CAST ( '12-JAN-19 10.03.26.816367000 PM' AS TIMESTAMP ),
CAST ( '12-JAN-19 11.03.23.454802000 PM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_2',
'11111111',
'22',
CAST ( '12-JAN-19 09.16.33.937077000 PM' AS TIMESTAMP ),
CAST ( '12-JAN-19 10.03.24.599359000 PM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_1',
'11111111',
'21',
CAST ( '12-JAN-19 08.31.53.090829000 PM' AS TIMESTAMP ),
CAST ( '12-JAN-19 09.16.29.704729000 PM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_10',
'11111111',
'30',
CAST ( '12-JAN-19 04.54.15.204137000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 05.29.04.933231000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_9',
'11111111',
'29',
CAST ( '12-JAN-19 04.34.17.166108000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 04.54.13.028900000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_8',
'11111111',
'28',
CAST ( '12-JAN-19 04.27.39.468042000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 04.34.15.035219000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_7',
'11111111',
'27',
CAST ( '12-JAN-19 04.20.24.390687000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 04.27.37.324884000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_6',
'11111111',
'26',
CAST ( '12-JAN-19 03.50.27.163671000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 04.20.22.179774000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_5',
'11111111',
'25',
CAST ( '12-JAN-19 03.33.25.138657000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 03.50.24.951625000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_4',
'11111111',
'24',
CAST ( '12-JAN-19 03.11.51.491252000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 03.33.22.174462000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_3',
'11111111',
'23',
CAST ( '12-JAN-19 02.18.06.351222000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 03.11.42.504240000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_2',
'11111111',
'22',
CAST ( '12-JAN-19 01.29.42.824729000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 02.18.04.854562000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_2',
'11111111',
'22',
CAST ( '12-JAN-19 12.39.17.824683000 AM' AS TIMESTAMP ),
CAST ( '12-JAN-19 01.24.03.245613000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_1',
'11111111',
'21',
CAST ( '11-JAN-19 11.55.26.614900000 PM' AS TIMESTAMP ),
CAST ( '12-JAN-19 12.39.02.660684000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_10',
'11111111',
'30',
CAST ( '11-JAN-19 03.27.36.605861000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 04.00.53.448994000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_9',
'11111111',
'29',
CAST ( '11-JAN-19 03.11.22.907790000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 03.27.34.498352000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_8',
'11111111',
'28',
CAST ( '11-JAN-19 03.05.29.913192000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 03.11.20.766575000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_7',
'11111111',
'27',
CAST ( '11-JAN-19 02.58.36.771560000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 03.05.19.101087000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_6',
'11111111',
'26',
CAST ( '11-JAN-19 02.30.17.317887000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 02.58.34.654754000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_5',
'11111111',
'25',
CAST ( '11-JAN-19 02.13.19.408260000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 02.30.15.078490000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_4',
'11111111',
'24',
CAST ( '11-JAN-19 01.53.04.823566000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 02.13.16.631077000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_3',
'11111111',
'23',
CAST ( '11-JAN-19 01.05.55.439894000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 01.53.02.618141000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_2',
'11111111',
'22',
CAST ( '11-JAN-19 12.19.31.222547000 AM' AS TIMESTAMP ),
CAST ( '11-JAN-19 01.05.51.316795000 AM' AS TIMESTAMP )
FROM DUAL
UNION ALL
SELECT 'STREAM_JOB_1',
'11111111',
'21',
CAST ( '10-JAN-19 11.34.22.818929000 PM' AS TIMESTAMP ),
CAST ( '11-JAN-19 12.19.19.064114000 AM' AS TIMESTAMP )
FROM DUAL
)
,
B AS -- for calculating LAST_DAY_START_TIME and DIFF
(
SELECT STREAM_NAME,
RUN_ID,
STREAM_JOB_ID,
START_TIME,
COMPLETE_TIME,
NVL ( LEAD ( START_TIME, 1 ) OVER ( ORDER BY START_TIME DESC ), START_TIME ) AS LAST_DAY_START_TIME,
START_TIME - NVL ( LEAD ( START_TIME, 1 ) OVER ( ORDER BY START_TIME DESC ), START_TIME ) AS DIFF
FROM A
ORDER BY START_TIME DESC
)
SELECT STREAM_NAME,
RUN_ID,
STREAM_JOB_ID,
START_TIME,
COMPLETE_TIME,
LAST_DAY_START_TIME,
ABS ( 24 * EXTRACT ( DAY FROM DIFF ) + EXTRACT ( HOUR FROM DIFF ) ) HRS_DIFFERENCE,
CASE
WHEN START_TIME < TRUNC ( START_TIME ) + 17 / 24 --Start time is less than 5PM
THEN TRUNC ( START_TIME ) - 1 --Then assume DW refresh from the previous day
ELSE TRUNC ( START_TIME ) --Else assume DW refresh from the same day
END AS DW_REFRESH_START_DATE
FROM B


------------------------------------------------------

STREAM_NAME RUN_ID STREAM_JOB_ID START_TIME DW_REFRESH_START_DATE
STREAM_JOB_10 11111111 30 13-JAN-19 12.49.14.006840000 AM 12-JAN-19
STREAM_JOB_9 11111111 29 13-JAN-19 12.30.31.092133000 AM 12-JAN-19
STREAM_JOB_8 11111111 28 13-JAN-19 12.23.30.519221000 AM 12-JAN-19
STREAM_JOB_7 11111111 27 13-JAN-19 12.16.46.414043000 AM 12-JAN-19
STREAM_JOB_6 11111111 26 12-JAN-19 11.44.42.610016000 PM 12-JAN-19
STREAM_JOB_5 11111111 25 12-JAN-19 11.25.56.546745000 PM 12-JAN-19
STREAM_JOB_4 11111111 24 12-JAN-19 11.03.25.611492000 PM 12-JAN-19
STREAM_JOB_3 11111111 23 12-JAN-19 10.03.26.816367000 PM 12-JAN-19
STREAM_JOB_2 11111111 22 12-JAN-19 09.16.33.937077000 PM 12-JAN-19
STREAM_JOB_1 11111111 21 12-JAN-19 08.31.53.090829000 PM 12-JAN-19
STREAM_JOB_10 11111111 30 12-JAN-19 04.54.15.204137000 AM 11-JAN-19
STREAM_JOB_9 11111111 29 12-JAN-19 04.34.17.166108000 AM 11-JAN-19
STREAM_JOB_8 11111111 28 12-JAN-19 04.27.39.468042000 AM 11-JAN-19
STREAM_JOB_7 11111111 27 12-JAN-19 04.20.24.390687000 AM 11-JAN-19
STREAM_JOB_6 11111111 26 12-JAN-19 03.50.27.163671000 AM 11-JAN-19
STREAM_JOB_5 11111111 25 12-JAN-19 03.33.25.138657000 AM 11-JAN-19
STREAM_JOB_4 11111111 24 12-JAN-19 03.11.51.491252000 AM 11-JAN-19
STREAM_JOB_3 11111111 23 12-JAN-19 02.18.06.351222000 AM 11-JAN-19
STREAM_JOB_2 11111111 22 12-JAN-19 01.29.42.824729000 AM 11-JAN-19
STREAM_JOB_2 11111111 22 12-JAN-19 12.39.17.824683000 AM 11-JAN-19
STREAM_JOB_1 11111111 21 11-JAN-19 11.55.26.614900000 PM 11-JAN-19
STREAM_JOB_10 11111111 30 11-JAN-19 03.27.36.605861000 AM 10-JAN-19
STREAM_JOB_9 11111111 29 11-JAN-19 03.11.22.907790000 AM 10-JAN-19
STREAM_JOB_8 11111111 28 11-JAN-19 03.05.29.913192000 AM 10-JAN-19
STREAM_JOB_7 11111111 27 11-JAN-19 02.58.36.771560000 AM 10-JAN-19
STREAM_JOB_6 11111111 26 11-JAN-19 02.30.17.317887000 AM 10-JAN-19
STREAM_JOB_5 11111111 25 11-JAN-19 02.13.19.408260000 AM 10-JAN-19
STREAM_JOB_4 11111111 24 11-JAN-19 01.53.04.823566000 AM 10-JAN-19
STREAM_JOB_3 11111111 23 11-JAN-19 01.05.55.439894000 AM 10-JAN-19
STREAM_JOB_2 11111111 22 11-JAN-19 12.19.31.222547000 AM 10-JAN-19
STREAM_JOB_1 11111111 21 10-JAN-19 11.34.22.818929000 PM 10-JAN-19
Chris Saxon
January 18, 2019 - 11:41 am UTC

Are you asking a question or showing us your final solution?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.