Home » SQL & PL/SQL » SQL & PL/SQL » Help querying status changes
icon9.gif  Help querying status changes [message #187806] Tue, 15 August 2006 14:10 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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.
Re: Help querying status changes [message #187809 is a reply to message #187807] Tue, 15 August 2006 15:11 Go to previous messageGo to next message
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...
Re: Help querying status changes [message #187873 is a reply to message #187809] Wed, 16 August 2006 02:30 Go to previous messageGo to next message
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
icon14.gif  Re: Help querying status changes [message #187959 is a reply to message #187873] Wed, 16 August 2006 09:14 Go to previous messageGo to next message
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
Re: Help querying status changes [message #188099 is a reply to message #187959] Thu, 17 August 2006 02:14 Go to previous message
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
Previous Topic: How to create Foreign key Constraint with Cascade Options
Next Topic: recycle bin
Goto Forum:
  


Current Time: Sat Dec 10 15:01:27 CST 2016

Total time taken to generate the page: 0.25215 seconds