As Of Date in Oracle SQL - Back to the Future

This works:

select *
from
   (select * from ps_job where emplid = '123')
AS OF TIMESTAMP SYSDATE - 1;

Binary Search

Example of:

Function BinarySearch(&arr, &srchVal, &low As integer, &high As integer) Returns integer
  
   If (&high < &low) Then
      Return - 1; /* not found */
   End-If;
  
   &mid = &low + ((&high - &low) / 2);
   If &arr [&mid] > &srchVal Then
      Return BinarySearch(&arr, &srchVal, &low, &mid - 1);
   Else
      If &arr [&mid] < &srchVal Then
         Return BinarySearch(&arr, &srchVal, &mid + 1, &high);
      Else
         Return &mid; /* found */
      End-If;
   End-If;
  
End-Function;

I don't often post random snippets of SQL, but I thought this was a good one. Rare enough to be able to forget it easily, and perhaps will be required again.

This returns schedules based on the schedule id(s) that is/are current for a given employee during a given payment period. If there is no schedule id default to 'UOA01'.

The first part was tricky as sch_assign is an effective dated table, and has no required end date, but each row does end when the next effective date comes into effect. I solved that using a subselect to derive the end_dt. Easy enough.

The next part, defaulting in 'UOA01' was a little trickier. A union to always bring in UOA01, but then a not exists subselect to only bring in where part A of the union returned nothing.

Perhaps easy when you see the solution first, this one took me quite a few attempts, and half the afternoon. Saved for prosperity and that time in the future when I ask myself "I remember doing similar...".

Apart from doing some tricky things with dates this SQL always returns a value. There is a default value in the SQL.

select
  %DateOut(dur)
, b.sched_hrs
from
    (select
      a.schedule_id
    , a.effdt as bgn_dt
    ,
        (select min(x.effdt) - 1
        from ps_sch_assign x
        where x.emplid = a.emplid
        and x.empl_rcd = a.empl_rcd
        and x.effdt < a.effdt) as end_dt
    from %Table(sch_assign) a
    where a.emplid = %Bind(cc_a20_uoa_aet.emplid)
    and a.empl_rcd = %Bind(cc_a20_uoa_aet.empl_rcd)
    and a.schedule_id <> ' '
    and a.effdt < %Bind(cc_a20_uoa_aet.prd_end_dt)
    union
    select 
      'UOA01'
    , to_date('01/01/2000', 'dd/mm/yyyy') as bgn_dt
    , to_date('31/12/9999', 'dd/mm/yyyy') as end_dt
    from dual
    where not exists
        (select 1
        from %Table(sch_assign) a
        where a.emplid = %Bind(cc_a20_uoa_aet.emplid)
        and a.empl_rcd = %Bind(cc_a20_uoa_aet.empl_rcd)
        and a.schedule_id <> ' '
        and a.effdt < %Bind(cc_a20_uoa_aet.prd_end_dt))) a
, %Table(sch_clnd_vw) b
where b.schedule_id = a.schedule_id
and b.dur between %Bind(cc_a20_uoa_aet.prd_bgn_dt) and %Bind(cc_a20_uoa_aet.prd_end_dt)
and (b.dur <= a.end_dt or a.end_dt is null)
and b.sched_hrs > 0
order by dur