?
Solved

How to find a previous record and compute the date difference

Posted on 2013-10-24
6
Medium Priority
?
446 Views
Last Modified: 2013-10-25
Hello,
I have a record example and some sql that I want to find out how to find the last record of each group (caseno) and see if there is a date difference > 1 day.
If there is a date difference > 1 day then put the flag = 'Y', if not, flag = 'N'.
This is based on the 'tid_action' column = 'EA'.  So look for each row = 'EA' and using the create_date of the most recent row - date_begin of the next recent 'EA', as highlighted in the example data file.
Is this better to be accomplished in a cursor or can this be done in another way.
Other file is the sql that retrieved the records in the output file, I started to create the cursor but don't know if this was the best way to approach this or not.
Thanks!
Data-Output.xlsx
Suzy.sql
0
Comment
Question by:Machinegunner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 39599056
Can you try this.
SELECT t1.*, 
       t4.Difference 
  FROM your_table t1 
       left join (SELECT CASENO, 
                         TID_CREATE_DATE, 
                         CASE 
                           WHEN TID_CREATE_DATE - TID_DATE_BEGIN > 1 THEN 'Y' 
                           ELSE 'N' 
                         END Difference 
                    FROM (SELECT CASENO, 
                                 MAX(CASE 
                                       WHEN rn1 = 1 THEN TID_DATE_BEGIN 
                                     END) TID_DATE_BEGIN, 
                                 MAX(CASE 
                                       WHEN rn2 = 1 THEN TID_CREATE_DATE 
                                     END) TID_CREATE_DATE 
                            FROM (SELECT *, 
                                         ROW_NUMBER() 
                                           over ( 
                                             PARTITION BY CASENO 
                                             ORDER BY TID_CREATE_DATE)      rn1, 
                                         ROW_NUMBER() 
                                           over ( 
                                             PARTITION BY CASENO 
                                             ORDER BY TID_CREATE_DATE DESC) rn2 
                                    FROM your_table 
                                   WHERE TID_ACTION = 'EA') t2 
                           WHERE rn1 = 1 
                             AND rn2 = 1 
                           GROUP BY CASENO) t3) t4 
              ON t1.CASENO = t4.CASENO 
                 AND t1.TID_CREATE_DATE = t4.TID_CREATE_DATE 

Open in new window

By the way, what is your oracle version?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39599319
Observations on your existing query:
a. you are using a Full Outer Join - but there appears to be no reason for this, plus it looks like you are then applying where conditions on that table (via the field TID_CREATE_DATE) and those where conditions will suppress any effects of that full outer join anyway. Suggest you try with an inner join.

b. this is not a good method for choosing a date range, it involves applying TRUNC() -twice- to all rows of data
         AND TRUNC(TID_CREATE_DATE) >= TO_DATE('01/01/2013','mm/dd/yyyy')
         AND TRUNC(TID_CREATE_DATE) <= TO_DATE('09/30/2013','mm/dd/yyyy')

There is a far better, more efficient method which requires a small change in thinking. Instead of seeking the last day of the month (e.g. '09/30/2013') use "less than the first day of the next month"
e.g.
         AND TID_CREATE_DATE >= TO_DATE('01/01/2013','mm/dd/yyyy')
         AND TID_CREATE_DATE <  TO_DATE('10/01/2013','mm/dd/yyyy')

So this means you typically add an extra day to the higher date. This method is fully reliable and does not require you to alter the data to suit the conditions.
 see: "Beware of Between"
and:
http://en.wikipedia.org/wiki/Sargable

Rules of thumb
Avoid applying functions on data values in a sql condition.
Avoid non-sargable predicates and replace them with sargable equivalents.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39599330
By the way, I had this alternative query prepared, but I'm now not confident after I have re-read the question. In the case information provided the sequence of tid_action is EA ER ER EA and for that sequence the following will work, but if sequences like this are possible EA ER EA ER EA EA  it may not meet expectations.

The results achieved from supplied data:
| CASENO | CTE_DESC | TID_SEQUENCE | POS_DESC |  SEV_DESC | TID_ACTION |                IRR_DESC | TID_DATE_BEGIN | TID_DATE_END | TID_CREATE_DATE | DIFFERENCE |
|--------|----------|--------------|----------|-----------|------------|-------------------------|----------------|--------------|-----------------|------------|
|   3439 |   Waiver |           15 |     Home | HCB - LOC |         EA | Meets Medical Necessity |     2013-05-16 |   2013-05-17 |      2013-05-13 |     (null) |
|   3439 |   Waiver |           15 |     Home | HCB - LOC |         ER |       Extension Request |     2013-05-16 |   2014-05-15 |      2013-05-14 |     (null) |
|   3439 |   Waiver |           16 |     Home | HCB - LOC |         EA | Meets Medical Necessity |     2013-05-16 |   2014-05-15 |      2013-05-16 |          N |
|  13728 |   Waiver |           15 |     Home | HCB - LOC |         EA | Meets Medical Necessity |     2013-04-22 |   2014-04-23 |      2013-04-16 |     (null) |
|  13728 |   Waiver |           15 |     Home | HCB - LOC |         ER |       Extension Request |     2013-04-24 |   2014-04-23 |      2013-04-23 |     (null) |
|  13728 |   Waiver |           15 |     Home | HCB - LOC |         ER |       Extension Request |     2013-04-24 |   2014-04-23 |      2013-04-23 |     (null) |
|  13728 |   Waiver |           16 |     Home | HCB - LOC |         EA | Meets Medical Necessity |     2013-04-24 |   2014-04-23 |      2013-04-24 |          Y |

Open in new window

Produced by the following query. However please note as I didn't have access to your original tables I could not test applying this anywhere but 'on top of' your existing query result.

with the cautions mentioned above here is what I was thinking of:
SELECT
      CASENO
    , CTE_DESC
    , TID_SEQUENCE
    , POS_DESC
    , SEV_DESC
    , TID_ACTION
    , IRR_DESC
    , to_char(TID_DATE_BEGIN,'YYYY-MM-DD')  TID_DATE_BEGIN
    , to_char(TID_DATE_END,'YYYY-MM-DD')    TID_DATE_END
    , to_char(TID_CREATE_DATE,'YYYY-MM-DD') TID_CREATE_DATE

    , CASE WHEN ( nxt_case <> caseno OR nxt_case IS NULL )
                  AND trunc(TID_CREATE_DATE,'DD') - trunc(min_ea,'DD') > 1
              THEN 'Y'
           WHEN ( nxt_case <> caseno OR nxt_case IS NULL )
                  AND trunc(TID_CREATE_DATE,'DD') - trunc(min_ea,'DD') < 2
              THEN 'N'
       END AS DIFFERENCE

FROM (
      SELECT
              derived.*
            , min(CASE WHEN TID_ACTION = 'EA' THEN TID_DATE_BEGIN END)
               over (partition BY CASENO) AS min_ea
            , lead(CASENO) over (ORDER BY CASENO, TID_CREATE_DATE) AS nxt_case
      FROM derived
           -- existing query here instead of this table
     )
;   

    -- http://sqlfiddle.com/#!4/9d0ab/3 

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Machinegunner
ID: 39601105
Thanks for the replies and help.
Oracle version?  we are using 11g.

A couple of questions, due to my Oracle experience..

1.  What does the keyword 'over partition' mean?
2.  lead(CASENO) OVER (ORDER BY CASENO, TID_CREATE_DATE), the 'lead'?

I did the changes and additions as required and ran the query.  Here is the whole query
attached.
Suzy2.sql
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39601758
Here is the formal documentation for "analytic functions"
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm#SQLRF06174
they are sometimes also called "windowing functions"

let's take MIN() as the first example, I assume you have seen this before and typically it would simply be used like this:

select caseno, MIN(case_date) from case_history group by caseno

here MIN() produces just one value for each caseno, and it is used in association with the group by clause.

Now we use the "analytic" version of MIN() which requires adding an OVER()

select caseno, case_date, MIN(case_date) over (partition by caseno) from case_history

Notice there is no "group by" now, this means we will get more rows, but here the minimum case_date is made available to every row by the MIN() function.

sample:
case_history
caseno case_date
case11 2012-02-01
case11 2012-10-01
case11 2013-03-01
case11 2012-09-01

select caseno, MIN(case_date) from case_history group by caseno
caseno MIN(case_date)
case11 2012-02-01


select caseno, case_date, MIN(case_date) over (partition by caseno) from case_history
caseno case_date    MIN(case_date) over (partition by caseno)
case11 2012-02-01 2012-02-01
case11 2012-10-01 2012-02-01
case11 2013-03-01 2012-02-01
case11 2012-09-01 2012-02-01

so, "partition by" is "similar" to group by, it is instructing MIN() to perform that calculation across a range of row as determined by what we specify as the "partition" - i.e. in this example by caseno - but now we are getting that minimum date repeated on each row of our result.

You can use MIN() MAX() SUM() COUNT() and some others this way as "analytic functions"
--------------

Here are Ora 10 references for LEAD and LAG:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions074.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions070.htm

LEAD and LAG are both very similar in nature, but they go in opposite directions, and these can also use the "OVER (partition by ..." but also - importantly - they use "ORDER BY ...)"

LEAD "looks ahead" in your results (as determined by an order you specify)
LAG "looks behind" in your results (as determined by an order you specify)

To begin with think of LEAD as being "next record" and LAG and "previous record"

In your query I proposed the following:

LEAD(CASENO) OVER (ORDER BY CASENO, TID_CREATE_DATE) AS nxt_case

So what we are doing here is ordering the data by caseno, tid_create_date, and then this function will let us know what the next row will hold for caseno. In other words we will know if we have reached the end of the current caseno if this row's caseno is different to the next row's caseno.

A good way of understanding this would be to display "nxt_case" as a column in your query.

Please note both LEAD and LAG are actually even smarter than what I have just described, they can for example "look back" or "look ahead" more then one row, and it wasn't required in your query to use "partition by"but both LEAD/LAG allow this too.

This is a nice overview of LEAD/LAG:
http://www.oracle-base.com/articles/misc/lag-lead-analytic-functions.php

and while there also look at analytic functions in general:
http://www.oracle-base.com/articles/misc/analytic-functions.php
0
 

Author Closing Comment

by:Machinegunner
ID: 39601886
Thanks Paul!  Great answer to my question(s) and really appreciate your help.
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question