i need to write a batch which will pick up only those records(or set them as DO_SENDMAIL=1) which are expiring exactly after 6 months (calendar months and not 180 days).
Batch will run only from Monday to Friday.
batch runs on 4 JUN 2018 (Mon) |
ID | DAY | expiry date | DO_SENDMAIL |
1 | Thu | 29-Nov | 0
|
2 | Fri | 30-Nov | 0
|
3 | Sat | 1-Dec | 0
|
4 | Sun | 2-Dec | 1
|
5 | Mon | 3-Dec | 1
|
6 | Tue | 4-Dec | 1
|
7 | Wed | 5-Dec | 0
|
| | | |
if batch runs on 1 JUN 2018 (Fri) |
ID | DAY | expiry date | DO_SENDMAIL |
1 | Thu | 29-Nov | 0
|
2 | Fri | 30-Nov | 0
|
3 | Sat | 1-Dec | 1
|
4 | Sun | 2-Dec | 0
|
5 | Mon | 3-Dec | 0
|
6 | Tue | 4-Dec | 0
|
7 | Wed | 5-Dec | 0
|
The above 2 are the expected results on different days.
Here is the code i have written
CREATE TABLE TEST
( "ID_COL" NUMBER,
"EXPIRY_DT" DATE
) ;
REM INSERTING into TEST
SET DEFINE OFF;
Insert into TEST (ID_COL,EXPIRY_DT) values (1,to_date('29-NOV-18','DD-MON-RR'));
Insert into TEST (ID_COL,EXPIRY_DT) values (2,to_date('30-NOV-18','DD-MON-RR'));
Insert into TEST (ID_COL,EXPIRY_DT) values (3,to_date('01-DEC-18','DD-MON-RR'));
Insert into TEST (ID_COL,EXPIRY_DT) values (4,to_date('02-DEC-18','DD-MON-RR'));
Insert into TEST (ID_COL,EXPIRY_DT) values (5,to_date('03-DEC-18','DD-MON-RR'));
Insert into TEST (ID_COL,EXPIRY_DT) values (6,to_date('04-DEC-18','DD-MON-RR'));
Insert into TEST (ID_COL,EXPIRY_DT) values (7,to_date('05-DEC-18','DD-MON-RR'));
Insert into TEST (ID_COL,EXPIRY_DT) values (9,to_date('08-DEC-18','DD-MON-RR'));
Insert into TEST (ID_COL,EXPIRY_DT) values (10,to_date('09-DEC-18','DD-MON-RR'));
Insert into TEST (ID_COL,EXPIRY_DT) values (11,to_date('10-DEC-18','DD-MON-RR'));
Insert into TEST (ID_COL,EXPIRY_DT) values (8,to_date('17-DEC-18','DD-MON-RR'));
My solution
WITH T AS (
select
id_col,
EXPIRY_DT as orig_exp_dt,
to_char(EXPIRY_DT,'day') as orig_exp_day,
to_char(EXPIRY_DT,'D') as orig_exp_day_no,
CASE
WHEN to_char(EXPIRY_DT,'D') = 1 THEN EXPIRY_DT + 2
WHEN to_char(EXPIRY_DT,'D') = 2 THEN EXPIRY_DT + 1
ELSE EXPIRY_DT END CALC_EXP_DT
from test
order by EXPIRY_DT
)
select
id_col,
orig_exp_dt,
orig_exp_day_no,
orig_exp_day
,months_between(CALC_EXP_DT,trunc(sysdate )) as mnth_btw
,case
when months_between(CALC_EXP_DT,trunc(sysdate ))=6 then 1
else 0
end do_sendmail
from t
where months_between(CALC_EXP_DT,trunc(sysdate ))=6;
I tested the code by running on different dates.It gave expected result.
Can you point out if i am missing anything or is there any better way to write?