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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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

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
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
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
SQL

From novice to tech pro — start learning today.