Link to home
Start Free TrialLog in
Avatar of Shaunsmith
ShaunsmithFlag for United States of America

asked on

Help in Reducing the Number of Inner Queries and Table scans

I have a query that I do a table scan thrice based on my needs .I am wondering if there is a better way to do this .


I have a Student Table with a Status Column . The status Column has two status that we are intrested in .

They are  1.'Warning Letter Sent'
          2."warning Letter Processed'


I want to  

REQUIREMENT  1.Pull out records of students who need to have **'Warning Letter Sent'***  but NOT ""warning Letter Processed'" .
REQUIREMENT  2.I need only the latest set of records when the "Warning Letter has been sent " but not processed



I have written a query that Words but its taking too long.Essentially what I have done is Found the Max Date for the " Warning Letter Sent" and Max Date for ""warning Letter Processed'
(which is two queries) and then checked if the DATE('Warning Letter Sent') > DATE ("warning Letter Processed')

This works great ..but very slow .



SELECT STD_ID
FROM
  STUDENT
WHERE
  STATUS ='Warning Letter Sent'
  AND DATE = ( SELECT MAX( DATE) FROM STUDENT WHERE STATUS ='Warning Letter Sent')
  AND DATE > ( SELECT MAX(DATE) FROM STUDENT  WHERE STATUS IN ( 'warning Letter Processed')


Thanks for your time
Avatar of Member_2_861731
Member_2_861731
Flag of Canada image

First of all... The column "Status" can only hold "one status at a time", right?

Now, let's check your requirements:

1. Pull out records of students who need to have **'Warning Letter Sent'***  but NOT ""warning Letter Processed'". If your "status" column can only have one value (i.e. Warning Letter Sent), there's no point in evaluating for a second status. A filter like: status='Warning Letter Sent' will guarantee that all the records only have the 'Warning Letter Sent' status.

2. I need only the latest set of records when the "Warning Letter has been sent " but not processed. Is this another query? Or is it in the same one?

I think that by clarifying the first requirement it will be easier to figure out the second one.

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>advantage of the fact that "w" > "W"  in ascii sorting

My guess is this is a typo.
just in case the capitalization is supposed to be the same

simply change MAX to MIN on the status if

"warning Letter Processed"

should be

"Warning Letter Processed"


because P < S
Avatar of Shaunsmith

ASKER

"LIONKING" the issue is the Student Table can have the same student Id Repeated so

Student Table  can have

StdId  Date Status

1   1/10/2010  Warning Letter Sent
1   5 /10/2010  warning Letter Processed
1  5 /10/2011   Warning Letter Sent
2   1/10/2012  Warning Letter Sent

In the above I need to get Id's 1 and 2
My post had the letters sent and processed euqle.

Here is an updated one using your test data.

I'm sure sdstubers works as well.  Didn't test which one was 'better'.

drop table tab1 purge;
create table tab1(std_id number, status varchar2(25), some_date date);

insert into tab1 values(1,'Warning Letter Sent',to_date('01/10/2010','MM/DD/YYYY'));
insert into tab1 values(1,'Warning Letter Processed',to_date('05/10/2010','MM/DD/YYYY'));
insert into tab1 values(1,'Warning Letter Sent',to_date('05/10/2011','MM/DD/YYYY'));
insert into tab1 values(2,'Warning Letter Sent',to_date('01/10/2012','MM/DD/YYYY'));
commit;


select std_id, max_date from
(
select std_id, max(some_date) max_date, sum(letter_sent) letters_sent, sum(letter_processed) letters_processed from
(
	select std_id,
		some_date,
		case when status = 'Warning Letter Sent' then 1 end letter_sent,
		case when status = 'Warning Letter Processed' then 1 end letter_processed
	from tab1
)
group by std_id
)
where  letters_sent>letters_processed or letters_processed is null
/

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
sum(letter_sent)

that seems unreliable to me.

sent
sent
sent
processed

will cause the sum  to return the last sent which is probably wrong.
note, mine doesn't work either based on the last set of data and expected results
awking00,  your solution ignores the requirement that the letters haven't been processed
In what way does it not? Can you show some data where it does not work?
sure, see the previous post.  The same example that breaks slightwv's code will break yours.
Shaunsmith ,

try this...
It will look for the last sent and make sure it occurs after the last processed (if it was processed)
This should do what you intended based on the last set of data
ee.txt
oops, awking00,  sorry, I misplaced a closing parentheses.

I think your solution should work as long as Processed and Sent don't occur on the same date value.

If they do, then Processed will be considered the last event which may or may not be appropriate
Currently, my query would not retrieve a std_id where both statuses exist for the same date. We would need some input from Shaunsmith as to how such a condition should be handled if it could exist.
Thanks for the Help Guys

The Grouping Method(from slightwv)  takes around 6 Minutes while the Partition Method( sdstuber) takes around 11 minutes .

This was better than the 2 Hours it would take for me .

I guess We have to index some columns to get it doing better than this .

Thanks for all your help
excellent help