I have a function that will extract records from a databaase with specific dates and times. One of the parameters passed is a date. This functions runs twice a day On of the parameters passed is AM or PM). My functions works for morning (times are around midnight to noon). But for the PM i get do records retrived ( times are from 1pm to 10pm).
I might add that records are inserted every day once after 8 am and after noon. I need to tailor my function for these times.
Below is is my current function.
create or replace FUNCTION "F_ASSESS_CCAPLOAD" (pidm_in in number, sdate in date, run in varchar2)
return varchar2 authid definer
is
return_ASSESCORE varchar2(4);
mmath number(4);
mmeng number(4);
sordate date ;
test_score number(4) ;
test_pidm number(9) ;
cursor get_ASSESMENT is
SELECT
CASE WHEN EXISTS( select distinct 'AM; '
from szrccap a where
szrccap_pidm = pidm_in
and to_date( szrccap_load_date) between to_date(sdate ) - (+13/24) and to_date(sdate ) + (12/24))
THEN 'Y'
ELSE 'N'
END
FROM dual ;
cursor get_ASSESMENT2 is
SELECT
case when exists( select distinct 'PM; ' from szrccap a where
szrccap_pidm = pidm_in
and to_date( szrccap_load_date) between to_date(sdate ) + (12/24) and to_date(sdate ) + (21/24))
THEN 'Y2'
ELSE 'N2'
END
FROM dual ;
begin
if run = 'AM' then
goto cohort1;
elsif
run = 'PM' then
goto cohort2 ;
end if;
<<cohort1>>
open get_ASSESMENT ;
fetch get_ASSESMENT into return_ASSESCORE;
close get_ASSESMENT;
goto fini;
<<cohort2>>
open get_ASSESMENT2 ;
fetch get_ASSESMENT2 into return_ASSESCORE;
close get_ASSESMENT2;
goto fini;
<<fini>>
return return_ASSESCORE ;
end;
So you want to find the morning or afternoon/evening values on a given date?
If so you can use the AM format mask!
This returns AM for times before noon. And PM for those after. So you're looking for rows that fall on this date, matching this mask:
alter session set nls_date_format = 'DD MON HH24:MI';
var dt_var varchar2(12);
var am_pm varchar2(2);
exec :dt_var := '01-JAN-2019';
exec :am_pm := 'AM';
with rws as (
select date'2019-01-01' + level/6 dt
from dual
connect by level <= 10
)
select * from rws
where to_date(:dt_var, 'DD-MON-YYYY') <= dt
and dt < to_date(:dt_var, 'DD-MON-YYYY') + 1
and to_char ( dt, 'AM' ) = :am_pm;
DT
01 JAN 04:00
01 JAN 08:00
exec :dt_var := '01-JAN-2019';
exec :am_pm := 'PM';
with rws as (
select date'2019-01-01' + level/6 dt
from dual
connect by level <= 10
)
select * from rws
where to_date(:dt_var, 'DD-MON-YYYY') <= dt
and dt < to_date(:dt_var, 'DD-MON-YYYY') + 1
and to_char ( dt, 'AM' ) = :am_pm;
DT
01 JAN 12:00
01 JAN 16:00
01 JAN 20:00
PS - goto is kinda... icky... You can do this all with a single statement!
Addenda:
========
Can all this be reduced to
select min(x)
into return_ASSESCORE
from
( select 'AM' x
from szrccap a
where szrccap_pidm = pidm_in
and szrccap_load_date >= trunc(sdate)
and szrccap_load_date < trunc(sdate)+12/24
and rownum = 1
union all
select 'PM'
from szrccap a
where szrccap_pidm = pidm_in
and szrccap_load_date >= trunc(sdate)+12/24
and szrccap_load_date < trunc(sdate)+21/24
and rownum = 1
)
where rownum = 1;
return_ASSESCORE will contain AM or PM or null as a result.