?
Solved

same record with different status

Posted on 2014-08-18
10
Medium Priority
?
280 Views
Last Modified: 2014-08-30
I am looking to get accountnumbers which has status = 5  and that changed to status = 8.  There should not be any intermediate status in between these two status.  Please suggest how do I get this in single query. They have effective dates associated with each status.

Quick response is appreciated!
0
Comment
Question by:d27m11y
9 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40267983
DB2 or Oracle?

How do you log the status change?

Can you provide sample data and expected results?  A table definition wouldn't hurt...
0
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 1500 total points
ID: 40268003
Hi D27,

The only practical way to view the changes is if you actually save a new row every time the status changes.  If that's the case, you can use the OLAP extensions to serialize the rows by account number.

WITH t0
AS
(
  SELECT row_number () over (partition by account, logged_timestamp) RN, t.*
  FROM mytable
) 
SELECT * FROM t0
WHERE status = 8
  AND (select 1 FROM t0 t1 WHERE t0.account = t1.account AND t1.RN+1 = t0.RN AND t1.status = 5)

Open in new window


Something like that should work.

Kent
0
 
LVL 18

Expert Comment

by:sventhan
ID: 40268012
Try this ...

select acctno,effectivedate,
      count(*) over (partition by acctno,effectivedate
                     order by effectivedate
                     ) cnt
    from yourtable
    where cnt = 2
    status = 5 and status =8
   order by acctno,effectivedate
   /
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Accepted Solution

by:
d27m11y earned 0 total points
ID: 40268016
Am looking in DB2, as such.

select * from
(Select acct_no,
STAT_CD ,
STAT_EFF_DT as DECES_EFF_DT ,
STAT_RM_DT as DECES_REMV_DT 
FROM A
WHERE STAT_CD in ('5') ) A
join
(Select acct_no,
STAT_CD ,
STAT_EFF_DT as PRG_EFF_DT,
STAT_RM_DT as PRG_REMV_DT
FROM A
WHERE STAT_CD in ('8')) B
on a.acct_no = b.acct_no
and a.DECES_REMV_DT = b.PRG_EFF_DT
where acct_no in ('1')

Open in new window



This is what I am trying to do. Is there any easier way to do it.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 40268028
Hi D27,

All 3 of the DB2 platforms should run the query I posted above, as long as they are running a fairly recent release.


Kent
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40269483
The approach you show in your query looks sound, but I'm relying on you know the data
    (e.g. I cannot tell if this is good or not: A.DECES_REMV_DT = B.PRG_EFF_DT )

You can use 2 derived tables as you are doing, or this should also work:
SELECT
      A.*, B.*
FROM TableA A
      INNER JOIN TableA B
                  ON A.acct_no = B.acct_no
                        AND A.DECES_REMV_DT = B.PRG_EFF_DT
                        AND B.STAT_CD = '8'
WHERE A.STAT_CD = '5'
      AND A.acct_no IN ('1') -- use = if possible

Open in new window

0
 

Author Comment

by:d27m11y
ID: 40273545
@Kdo, how do I write my above mentioned query using WITH clause
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 40274043
I'm not sure what you mean.  I posted a CTE (with clause) style example.
0
 

Author Closing Comment

by:d27m11y
ID: 40294274
Served my purpose
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month16 days, 22 hours left to enroll

864 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