5 Replies Latest reply on May 1, 2012 2:24 PM by sb92075

Caluculating No.of missed inspections in past 12 months

Hi all,
I am trying to come up with a query to see how many missed inspections are there for a person in past 12 months. the data in my table is like this

Case_no------person_id-----Ind_inspection---inspection_month
1000----------234--------------------Y-----------------01-NOV-08
1000----------234--------------------N-----------------01-DEC-08
1000----------234--------------------Y-----------------01-JAN-09
1000----------234--------------------Y-----------------01-MAR-09
1002----------135--------------------Y-----------------01-OCT-11
1002----------135--------------------Y-----------------01-JAN-12
1002----------135--------------------N-----------------01-SEP-11
1002----------135--------------------Y-----------------01-AUG-11
1002----------135--------------------Y-----------------01-NOV-11

Can you guys help me out in calculating this.
• 1. Re: Caluculating No.of missed inspections in past 12 months
Copter wrote:
Hi all,
I am trying to come up with a query to see how many missed inspections are there for a person in past 12 months. the data in my table is like this

Case_no------person_id-----Ind_inspection---inspection_month
1000----------234--------------------Y-----------------01-NOV-08
1000----------234--------------------N-----------------01-DEC-08
1000----------234--------------------Y-----------------01-JAN-09
1000----------234--------------------Y-----------------01-MAR-09
1002----------135--------------------Y-----------------01-OCT-11
1002----------135--------------------Y-----------------01-JAN-12
1002----------135--------------------N-----------------01-SEP-11
1002----------135--------------------Y-----------------01-AUG-11
1002----------135--------------------Y-----------------01-NOV-11

Can you guys help me out in calculating this.
formulate SQL which genrates all valid inspection ocurrances
MINUS
SQL that produced above
• 3. Re: Caluculating No.of missed inspections in past 12 months
Guessing
``````where inspection_month >= add_months(trunc(sysdate),'month'),-12)
and ind_inspection = 'N'``````
Regards

Etbin
• 4. Re: Caluculating No.of missed inspections in past 12 months
I am not able to understand what do you mean by formulate.
• 5. Re: Caluculating No.of missed inspections in past 12 months
Copter wrote:
I am not able to understand what do you mean by formulate.
http://www.orafaq.com/forum/mv/msg/95011/463394/102589/#msg_463394

use code as shown in URL above as a template to produce all valid inspection dates for last 12 months