You Asked
Hi All,
I need a query for the below scenario.
I am working for a retail application so there is an option for conducting an audit for suppliers.
Scenario - While conducting an audit i found a child labor issue so for this case i have to send auto mail every six months until the child reaches the age 18
with the help of audit date + sysdate i can identify the no of months covered till now so i wrote a condition like below for auto email.
select at.aupplier,at.audit_code from (select
at.audit_code,
trunc(trunc(sysdate-at.audit_date)/30) as frequency
from audit_table )
WHERE Frequency in (6,12,18,24,30,36,42,48,54,60,
66,72,78,84,90,96,102,108,114,120,
126,132,138,144,150,156,162,168,174,180,
186,192,198,204,210
);
and current_age identifying from different table
select min(current_age) from (
SELECT
CASE
WHEN DOB IS NULL
THEN
(trunc(months_between(sysdate,audit_date)/12)+AGE_BASED_ON_MEDICAL)
ELSE
(trunc(months_between(sysdate, DOB) /12))
END AS CURRENT_AGE
FROM audit_child_labour_details
WHERE AUDIT_CODE = PI_AUDIT_CODE
);
If current_Age < 18 then mail will be triggered.
Is there any better way instead of hard coding the months.
and Connor said...
WHERE Frequency in (6,12,18,24,30,36,42,48,54,60,
66,72,78,84,90,96,102,108,114,120,
126,132,138,144,150,156,162,168,174,180,
186,192,198,204,210
);
can be written as
where mod(Frequency,6)=0 and frequency <= 210
Other forms could be:
where to_char(audit_date,'ddmm') in
(to_char(sysdate,'ddmm'),
to_char(add_months(sysdate,-6),'ddmm')
)
but in this one, if the audit date is the last day of the month you might get an extra email. Or something like
where mod(trunc(sydate-audit_date),180) = 0
and sysdate-audit_date < 365.25*18
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment