Oracle SQL query help

Hello,

I need help with writing query in Oracle SQL to get data from the next and previous row based on the conditions. I tried using LAG and LEAD functions but not getting the results as expected. I am attaching an excel file showing how the current setup of the data with the rules that needs to be applied. I have also included the expected result. Please help
Sample-Request.xls
angel7170Asked:
Who is Participating?
 
sdstuberCommented:
Try this...


SELECT appl_id,
       request_begin_ts,
       CASE
           WHEN request_end_ts IS NOT NULL THEN request_end_ts
           WHEN last_status = 'P' THEN NULL
           WHEN last_status = 'C' THEN last_modified_ts
       END
           request_end_ts
  FROM (SELECT x.*, ROW_NUMBER() OVER(PARTITION BY appl_id, request_end_ts ORDER BY created_ts) rn
          FROM (SELECT appl_id,
                       CASE WHEN requested_by_worker_no IS NOT NULL AND curr_rn = 107000 THEN created_ts END
                           request_begin_ts,
                       CASE
                           WHEN requested_by_worker_no IS NOT NULL AND curr_rn = 107000
                           THEN
                               FIRST_VALUE(
                                   CASE WHEN curr_rn IN (100001, 250461) THEN created_ts END IGNORE NULLS)
                               OVER(PARTITION BY appl_id
                                    ORDER BY created_ts
                                    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
                       END
                           request_end_ts,
                       created_ts,
                       LAST_VALUE(status_cd)
                       OVER(PARTITION BY appl_id ORDER BY created_ts ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
                           last_status,
                       LAST_VALUE(last_modified_ts)
                       OVER(PARTITION BY appl_id ORDER BY created_ts ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
                           last_modified_ts
                  FROM yourtable t) x
         WHERE request_begin_ts IS NOT NULL)
 WHERE rn = 1

Open in new window

1
 
sdstuberCommented:
>>Take the FIRST record's CREATED_TS and consider it as REQUEST_BEGIN_TS.

Does that mean we should ignore the value in the REQUEST_BEGIN_TS  column?
In your example you have it populated with the value in CREATED_TS, but your comment implies we should use CREATED_TS instead and not actually use the already supplied REQUEST_BEGIN_TS.

Is that correct?
0
 
angel7170Author Commented:
Sorry, I forgot to remove "REQUEST_BEGIN_TS" and "REQUEST_END_TS" column from the current setup. Those two columns I tried to create it by using LAG and LEAD functions but doesn't seem to work. So please ignore those columns

Yes, I need to populate the REQUEST_BEGIN_TS with the value in CREATED_TS. Thank you
0
 
angel7170Author Commented:
Awesome! Honestly, I spent 2 weeks on this one query but still with no luck. Thank you so much for the query as it is working as expected.

You guys are genius! Thank you
0
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.

All Courses

From novice to tech pro — start learning today.