Shaunsmith
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>advantage of the fact that "w" > "W" in ascii sorting
My guess is this is a typo.
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
simply change MAX to MIN on the status if
"warning Letter Processed"
should be
"Warning Letter Processed"
because P < S
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
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'.
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
/
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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.
ASKER
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
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
ASKER
excellent help
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.