OraFAQ Forum: SQL & PL/SQL » Help querying status changes

Home » SQL & PL/SQL » SQL & PL/SQL » Help querying status changes

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help querying status changes [message #187806]

Tue, 15 August 2006 14:10

crweaks23
Messages: 4
Registered: May 2006
Location: New York, NY

Junior Member

Hey everyone, this is a report for corp audit due an hour ago, and I can't get an accurate query. Any help would be apprecited.

I'm working in peopleSoft, so the tables are effective dated and effective sequenced. (If more than one row is added on the same day, both rows with have the same effective date, the first with effective sequence 0, the second with effective sequence 1, etc).

There is a status field, and I need to get a report that shows every status change that results in an inactive status. (Consecutive rows changing from Active to Inactive). Note: It is possible for two rows to have consecutive inactive statuses, and in this case, only the first inactive row can be included.

The following query will give me every inactive row in the specified date range, but does not guarantee that these rows changed status:

select *
FROM PS_TBL A
where A.status = 'I'
and A.effdt between '1-dec-2005' and '31-jul-2006'

Possibly helpful, the following query will return the most recent inactive row added (to show an example of a typical PS query involving effective dating and sequencing):

Note: GUID = user ID (primary key)

select *
FROM PS_TBL A
where A.EFFDT = (SELECT MAX(EFFDT) 
                 FROM PS_TBL 
		 WHERE GUID = A.GUID 
	         AND EFFDT <= SYSDATE
	         AND STATUS = 'I') 
AND A.EFFSEQ = (SELECT MAX(EFFSEQ) 
	        FROM PS_TBL 
		WHERE GUID = A.GUID 
		AND EFFDT = A.EFFDT)

Thanks for even reading this far, hopefully help is on the way!

[Updated on: Tue, 15 August 2006 14:18]

Report message to a moderator

Re: Help querying status changes [message #187807 is a reply to message #187806]

Tue, 15 August 2006 14:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Consecutive rows
There is NO such a concept in any RDBMS without an ORDER BY clause.
The order in which rows are returned by Oracle without an ORDER BY clause is indeterminate.

Report message to a moderator

Re: Help querying status changes [message #187809 is a reply to message #187807]

Tue, 15 August 2006 15:11

crweaks23
Messages: 4
Registered: May 2006
Location: New York, NY

Junior Member

To clarify, here is an example:

We only want to count the 2nd and 4th rows (starred), because we are only including "deactivation" rows:

 GUID     Status    EFFDT     EFFSEQ     MISC DATA
 00001       I     3/01/06      1          BLAH
*00001       I     3/01/06      0
 00001       A     2/20/06      0
*00001       I     2/10/06      0
 00001       A     2/01/06      0
 00001       I     1/01/06      0

Notice a couple things here:

First, we only want to count "deactivations." Therefore, the "bottom of stack" 'I' row does not count, this record started out inactive, so this does not count as a deactivation (don't ask why it's possible, just trust that it is..).

Second, look at the "top of stack." Notice that although the record was already deactivated on 3/1/06, some miscellanious data was added to it after, which created a second 'I' row. This row also doesn't count, because no deactivation took place between the two 3/1/06 rows.

Our query result should be the following:

 GUID     Status    EFFDT     EFFSEQ 
00001       I      3/01/06       0
00001       I      2/10/06       0

I realize this is not an easy task, (trust me, I do), but if someone could solve this, I would be able to actually sleep tonight...

Report message to a moderator

Re: Help querying status changes [message #187873 is a reply to message #187809]

Wed, 16 August 2006 02:30

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Ahhhh, I love deadlines - they make a lovely noise as they fly past. Cool

Try this:

create table test (GUID varchar2(10), Status varchar2(1),  EFFDT date     ,EFFSEQ number);

insert into test values ('00001'     ,  'I'     ,to_date('3/01/06','mm/dd/rr'),      1);
insert into test values ('00001'     ,  'I'     ,to_date('3/01/06','mm/dd/rr'),      0);
insert into test values ('00001'     ,  'A'     ,to_date('2/20/06','mm/dd/rr'),      0);
insert into test values ('00001'     ,  'I'     ,to_date('2/10/06','mm/dd/rr'),      0);
insert into test values ('00001'     ,  'A'     ,to_date('2/01/06','mm/dd/rr'),      0);
insert into test values ('00001'     ,  'I'     ,to_date('1/01/06','mm/dd/rr'),      0);

SQL> select guid,status,effdt,effseq
  2  from (select guid
  3              ,status
  4              ,effdt
  5              ,effseq 
  6              ,lag(status) over (partition by guid order by effdt asc, effseq asc) prev_status
  7        from   test)
  8  where status = 'I'
  9  and   prev_Status = 'A';

GUID      |S|EFFDT    |    EFFSEQ
----------|-|---------|----------
00001     |I|10-FEB-06|         0
00001     |I|01-MAR-06|         0

Report message to a moderator

Re: Help querying status changes [message #187959 is a reply to message #187873]

Wed, 16 August 2006 09:14

crweaks23
Messages: 4
Registered: May 2006
Location: New York, NY

Junior Member

If you lived in new york, I'd buy you a beer. This is a really good function to know; I'll definately apply it to different queries.

Thank you so much. Thumbs Up

Report message to a moderator

Re: Help querying status changes [message #188099 is a reply to message #187959]

Thu, 17 August 2006 02:14

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

My brother lives in NY.
You can buy him a beer instead Cool

Report message to a moderator

Previous Topic:	How to create Foreign key Constraint with Cascade Options
Next Topic:	recycle bin

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 14:25:59 CST 2024