RhythmAddict112
Programmer
Hi all. My situation is this...
I have a query that selects 'requester names' from a table from first day of the month to the last day of the month...Something like:
Which with those particular dates returns me 1359 rows.
This is the problem; for each of those results, I need to check for 4 different circumstances. In other words, I have to count how many requests have been: Submitted, Rejected, Approved or are still pending. Each of these conditions is indicated by WHERE/AND clauses on multiple columns for each condition. For example, to check approved I have the following:
Here is where the problem is....I could easily have 4 queries to do this with, Each one checking 1349 rows for conditions such as the ones above. However, this is all being displayed on an ASP page - so that just isn't cutting it in the performance department. Is there a way for me to do this all within the query? I'm not sure if this is possible, but logically I guess it would be something like a for each loop(?)
For each Requester
in table_x
where dateSubmitted between (01-JAN-04) AND (31-JAN-04)
--Check Approved
Count how many requesters meet condition 1 AND condition 2, etc
--Check Rejected
Count how many requesters meet condition 1 AND conditon 2, etc
--Check Submitted
Count how many requesters meet condition 1 AND condition 2, etc
--Check Pending
Count how many requesters meet condition 1 AND condition 2, etc
of course thats just some pseudo code. Basically, I'd need to do this in the most efficient way possible. I could put all this logic into a stored procedure if I could just nail the SQL syntax down - but first things first. Just for the record (get it, record?) I'm using Oracle 9i. Thank you for all your help
I have a query that selects 'requester names' from a table from first day of the month to the last day of the month...Something like:
Code:
SELECT DISTINCT cReqName, cReqID
FROM tbl_OpenWindowRequests
WHERE dSubmit between ('01-JAN-2004') and ('31-JAN-2004')
OR dApprRej between ('01-JAN-2004') and ('31-JAN-2004')Which with those particular dates returns me 1359 rows.
This is the problem; for each of those results, I need to check for 4 different circumstances. In other words, I have to count how many requests have been: Submitted, Rejected, Approved or are still pending. Each of these conditions is indicated by WHERE/AND clauses on multiple columns for each condition. For example, to check approved I have the following:
Code:
SELECT DISTINCT CREQNAME, COUNT (CreqName) Approved
FROM tbl_OpenWindowRequests
WHERE dSubmit between ('01-JAN-2004') and ('31-JAN-2004')
OR dApprRej between ('01-JAN-2004') and ('31-JAN-2004')
AND (iApprStatus=-1)
AND ((iAppr2Status IS NULL) OR (iAppr2Status=-1))
AND ((iBatch Is Null) OR (iBatch > 0))
AND tbl_OpenWindowRequests.cRegion = 'UP'
AND tbl_OpenWindowRequests.cChannel = '2'Here is where the problem is....I could easily have 4 queries to do this with, Each one checking 1349 rows for conditions such as the ones above. However, this is all being displayed on an ASP page - so that just isn't cutting it in the performance department. Is there a way for me to do this all within the query? I'm not sure if this is possible, but logically I guess it would be something like a for each loop(?)
For each Requester
in table_x
where dateSubmitted between (01-JAN-04) AND (31-JAN-04)
--Check Approved
Count how many requesters meet condition 1 AND condition 2, etc
--Check Rejected
Count how many requesters meet condition 1 AND conditon 2, etc
--Check Submitted
Count how many requesters meet condition 1 AND condition 2, etc
--Check Pending
Count how many requesters meet condition 1 AND condition 2, etc
of course thats just some pseudo code. Basically, I'd need to do this in the most efficient way possible. I could put all this logic into a stored procedure if I could just nail the SQL syntax down - but first things first. Just for the record (get it, record?) I'm using Oracle 9i. Thank you for all your help