We do audits on our production lines any random day.
Operators enter sample size and number of defects per production line (JAR 1 or 2 or 3, etc.) and defect_display (J013 Ripped Lable) into a tabular form.
For reporting purposes, quality manager enters a date range into start_date and end_date fields and wants to see the calculation of DPMU (defects per million) per week within that range and display like this as the month progresses:
| Week Ending | DPMU |
|---|
| 04-JULY-2014 | 256142 |
| 11-JULY-2014 | 152465 |
| 18-JULY-2014 | |
| 25-JULY-2014 | |
These work for date range entered, but I need to split date range by week.
TOTAL COUNT
SELECT COUNT(A.AUDIT_INFO_ID)
FROM QA_AUDIT_INFO A, QA_AUDIT_DTL B
WHERE A.AUDIT_INFO_ID = B.AUDIT_INFO_ID AND
A.AUDIT_DATE BETWEEN :P920_START_DT AND :P920_END_DT
AND UPPER(A.PRDN_LINE) = UPPER(:P920_PRDN_LINE)
AND UPPER(A.AUDIT_TYPE) = UPPER(:P920_AUDIT_TYPE)
TOTAL DEFECTS
SELECT COUNT(A.AUDIT_INFO_ID)
FROM QA_AUDIT_INFO A, QA_AUDIT_DTL B
WHERE A.AUDIT_INFO_ID = B.AUDIT_INFO_ID AND
A.AUDIT_DATE BETWEEN :P920_START_DT AND :P920_END_DT
AND UPPER(B.DEFECT_DISPLAY) = UPPER(:P920_DEFECT_DISPLAY)
AND UPPER(A.PRDN_LINE) = UPPER(:P920_PRDN_LINE)
TOTAL SAMPLE SIZE
SELECT SUM(B.SAMPLE_SZ)
FROM QA_AUDIT_INFO A, QA_AUDIT_DTL B
WHERE A.AUDIT_INFO_ID = B.AUDIT_INFO_ID AND
A.AUDIT_DATE BETWEEN :P920_START_DT AND :P920_END_DT
AND UPPER(B.DEFECT_DISPLAY) = UPPER(:P920_DEFECT_DISPLAY)
AND UPPER(A.PRDN_LINE) = UPPER(:P920_PRDN_LINE)
AND UPPER(A.AUDIT_TYPE) = UPPER(:P920_AUDIT_TYPE)
DPMU
SELECT ROUND(:P920_EC_TOTAL_DEFECTS/:P920_TOTAL_SAMPLE_SZ*C.DEFECT_VALUE*1000000*:P920_EC_TOTAL_DEFECTS/:P920_TC_TOTAL_COUNT,0)
FROM QA_AUDIT_INFO A, QA_AUDIT_DTL B, QA_DEFECT_CODES C
WHERE A.AUDIT_INFO_ID = B.AUDIT_INFO_ID AND
A.AUDIT_DATE BETWEEN :P920_START_DT AND :P920_END_DT
AND UPPER(B.DEFECT_DISPLAY) = UPPER(:P920_DEFECT_DISPLAY)
AND UPPER(A.PRDN_LINE) = UPPER(:P920_PRDN_LINE)
Then I'll need to chart it per defect_display like this for all defects within the date range, something like this:
SELECT NULL LINK,
DEFECT_DISPLAY LABEL,
DPMU VALUE
FROM QA_GET_DPMU
WHERE GET_DPMU_ID = :P920_GET_DPMU_ID
ORDER BY DEFECT_DISPLAY
Thanks in advance,
Alex
Application Express 4.2.2.00.11