Solved

How to find a previous record and compute the date difference

Posted on 2013-10-24
6
435 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
  • 3
  • 2
6 Comments
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Machinegunner
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
Thanks Paul!  Great answer to my question(s) and really appreciate your help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now