Thanks. I added my SQL which resulting syntex error and insert stmt to insert test data.You didn't test this! You're inserting strings into a date column which will fail.
Will begin_dt always be the same on all rows of output, regardless of where the gaps are?
CLIENT_SERIAL_ID PGM_ID BEGIN_DT END_DT CLIENT_PGM_STAT_ID CLIENT_PGM_ROLE_ID 2167 669 1/1/2010 11/30/2010 963 2676 2167 669 1/1/2010 12/31/9999 963 2676
Edited by: Frank Kulash on May 29, 2012 5:25 PM
WHERE client_pgm_role_id = 2676 -- or maybe ... != 2689
I am having a problem with the syntex if I add the additional columns.What error are you getting?
The conditions are: we only look for records that has CLIENT_PGM_STAT_ID = 963 and CLIENT_PGM_ROLE_ID = 2676That sounds like a predicate/a WHERE clause.
The expected result for client 2167, pgm 669 should be ...We need to filter out the records that have CLIENT_PGM_ROLE_ID = 2689 as well, since it indicates a gap.
ORA-01841Yup, got that one myself as well, but changing NLS_DATE_LANGUAGE and NLS_DATE_FORMAT fixed that for me.
937044 wrote:Can you PLEASE read the FAQ and the post by BluShadow (at the top of the thread list)
Thanks. I updated my insert statements and added more details.
SELECT * FROM (SELECT CLIENT_SERIAL_ID, PGM_ID, BEGIN_DT, END_DT, CLIENT_PGM_STAT_ID, CLIENT_PGM_ROLE_ID, BEGIN_DT - FIRST_VALUE (BEGIN_DT) OVER (PARTITION BY CLIENT_PGM_STAT_ID) DAY_GAP FROM TEST_CLIENT_PGM WHERE CLIENT_PGM_STAT_ID = 963 AND CLIENT_PGM_ROLE_ID = 2676 ORDER BY CLIENT_SERIAL_ID, PGM_ID, BEGIN_DT);