I have a requirement where my Job is scheduled to run on every 15th and Last day of each month. (i.e. 15th jan, 31st JAN , 15th feb, 29 th Feb, 15 th APR, 30th APR etc. etc.)
My Job creates an extract from DB2 tables. Conditions are
a) When job runs on 15th the extract should be created from First day of the month till 15th of the month
b) When it Runs on Last day of the month, it should consider the date Between 16th of the month till 30th (if April, June, sept etc) , or from 16th to 31st (if JAN, MAR, MAY, July etc)
I have a sample code but it is not applicable for all the scenario. Can anyone help please
SELECT DISTINCT
TP.GROUP_ID "GROUP",
TP.SPONSOR_NAME "GROUP NAME",
TP.DIV_ID "DIVISION",
FROM DBCYXXX.TPAA TP
WHERE
TP.ROLE IN ('P','A')
AND DATE(TD.CHN_TS)
BETWEEN
(
DATE('0001-01-01') +
YEAR(CURRENT DATE) YEARS - 1 YEARS +
MONTH(CURRENT DATE) MONTHS - 1 MONTHS+
DAY(CURRENT DATE) DAYS - 1 DAYS - 14 DAYS
)
AND
(
DATE('0001-01-01') +
YEAR(CURRENT DATE) YEARS - 1 YEARS +
MONTH(CURRENT DATE) MONTHS - 1 MONTHS +
DAY(CURRENT DATE) DAYS - 1 DAYS
)
ORDER BY TP.GROUP_ID DESC
;
TP.GROUP_ID "GROUP",
TP.SPONSOR_NAME "GROUP NAME",
TP.DIV_ID "DIVISION",
FROM DBCYXXX.TPAA TP
WHERE
TP.ROLE IN ('P','A')
AND DATE(TD.CHN_TS)
BETWEEN
(
DATE('0001-01-01') +
YEAR(CURRENT DATE) YEARS - 1 YEARS +
MONTH(CURRENT DATE) MONTHS - 1 MONTHS+
DAY(CURRENT DATE) DAYS - 1 DAYS - 14 DAYS
)
AND
(
DATE('0001-01-01') +
YEAR(CURRENT DATE) YEARS - 1 YEARS +
MONTH(CURRENT DATE) MONTHS - 1 MONTHS +
DAY(CURRENT DATE) DAYS - 1 DAYS
)
ORDER BY TP.GROUP_ID DESC
;
