The current query only displays the current pay period we are in. I am tasked with getting the previous period as well. I can't figure out what would be the best way to filter it in after the prompt.
SELECT Table__1."WORK_CENTER",
Table__1."WORK_DATE",
Table__1."AFE",
Table__1."PAYCODE",
Table__1."PERSONNEL_NUMBER",
Table__1."REPORTED_HOURS",
Table__1."REPORTED_MINS",
Table__1."PERSONNEL_NUMBER",
Table__1."POSITION_NUMBER",
Table__1."MILES",
(
case
when dayofmonth(current_date) <= 15 then (ADD_MONTHS(NEXT_DAY(LAST_DAY(CURRENT_DATE)), -1))
else (
ADD_DAYS (
TO_DATE ((LAST_DAY(CURRENT_DATE)), 'YYYY-MM-DD'),
(
CASE
WHEN Month(current_date) IN (4, 6, 9, 11) THEN -14
WHEN Month(current_date) IN (1, 3, 5, 7, 8, 10, 12) THEN -15
ELSE -12
END
)
)
)
end
) as "begin_dt",
(
case
when dayofmonth(current_date) <= 15 then (
ADD_DAYS (
TO_DATE ((LAST_DAY(CURRENT_DATE)), 'YYYY-MM-DD'),
(
CASE
WHEN Month(current_date) IN (4, 6, 9, 11) THEN -14
WHEN Month(current_date) IN (1, 3, 5, 7, 8, 10, 12) THEN -15
ELSE -12
END
)
)
)
else (LAST_DAY(CURRENT_DATE))
end
) as "end_dt"
FROM Table__1
WHERE Table__1."WORK_CENTER" = @Prompt('enter prompt', 'A',,,)
AND Table__1."WORK_DATE" >= (
case
when dayofmonth(current_date) <= 15 then (ADD_MONTHS(NEXT_DAY(LAST_DAY(CURRENT_DATE)), -1))
else (
ADD_DAYS (
TO_DATE ((LAST_DAY(CURRENT_DATE)), 'YYYY-MM-DD'),
(
CASE
WHEN Month(current_date) IN (4, 6, 9, 11) THEN -14
WHEN Month(current_date) IN (1, 3, 5, 7, 8, 10, 12) THEN -15
ELSE -12
END
)
)
)
end
)
AND Table__1."WORK_DATE" < (
case
when dayofmonth(current_date) <= 15 then (
ADD_DAYS (
TO_DATE ((LAST_DAY(CURRENT_DATE)), 'YYYY-MM-DD'),
(
CASE
WHEN Month(current_date) IN (4, 6, 9, 11) THEN -14
WHEN Month(current_date) IN (1, 3, 5, 7, 8, 10, 12) THEN -15
ELSE -12
END
)
)
)
else (LAST_DAY(CURRENT_DATE))
end
)
order by WORK_DATE
any thoughts? I feel it would be better to do the filtering within rather than run a second query against it.
