Dagens udfordring - Beregning af arbejdstid

http://lmgtfy.com/?q=timestamps

peterolsen>

Jeg tror ikke helt at du har forstaaet problemstillingen.

Jeg tror ikke at SQL er godt til dette.

Derfor:
* hvis muligt flyt beregningen fra databasen til applikationen
* hvis ikke muligt saa lave en UDF i Java

Simple algoritme:

laes fra arbejdstids tabel ind i array/collection af klasse med start of slut

antal minutter = 0

for hvert minut mellem start og slut{
    for hver arbejdsperiode {
        hvis tid i periode {
            antal minutter ++
        }
    }
}

Hvis performance er vigtig, saa kan algoritmen forbedres en hel del.

Man kan bl.a.:
- teste om en hel timer er der og saa +60
- undlade at loebe igennem alle arbejds perioder

Men der bliver hurtigt en del if saetninger i den logik.

Hej,

Jeg prøver lige at uddybe lidt.

Jeg har en tabel på en database indeholdende to timestamps vedr. hvornår en sag er modtaget (timestamp 1) og til hvornår en sag er behandlet (timestamp 2). Disse timestamps ønsker jeg at beregne antal mellem disse timestamps indenfor arbejdstid.

Min problemstilling opstår når delta kun skal udregnes indenfor arbejdstid som er defineret i tabellen WF_WORK_CALENDAR

Tabellen WF_WORK_CALENDAR har følgende kolonner. Som det ses så er åbningstiden forskellig dag for dag.

Starttid                  Sluttid
22-01-2013 08:00:00        22-01-2013 16:00:00
23-01-2013 08:00:00        23-01-2013 16:00:00
24-01-2013 08:00:00        24-01-2013 16:00:00
25-01-2013 08:00:00        25-01-2013 15:30:00
25-01-2013 10:00:00        25-01-2013 16:00:00
25-01-2013 00:00:00        25-01-2013 00:00:00

Jeg vil gerne, at beregningen foretages i en database funktion som jeg kan genanvende.

Jeg har fundet følgende funktion som inspiration, men denne kigger dog ikke på tabel som udgangspunkt for åbningstiderne.

https://forums.oracle.com/forums/thread.jspa?messageID=10658241

CREATE OR REPLACE
  FUNCTION get_bus_minutes_between(
                                  p_start_date DATE,
                                  p_end_date DATE
                                  )
    RETURN NUMBER
    IS
        v_return NUMBER;
    BEGIN
        with t as (
                  select  case level
                            when 1 then greatest(p_start_date,trunc(p_start_date) + 9 / 24)
                            else trunc(p_start_date) + level - 15 / 24
                          end start_dt,
                          case connect_by_isleaf
                            when 1 then least(p_end_date,trunc(p_end_date) + 18 / 24)
                            else trunc(p_start_date) + level - 8 / 24
                          end end_dt
                    from  dual
                    connect by level <= trunc(p_end_date) - trunc(p_start_date) + 1
                  )
        select  sum(greatest(end_dt - start_dt,0)) * 24 * 60 work_minutes
          into  v_return
          from  t
          where trunc(start_dt) - trunc(start_dt,'iw') < 5; -- exclude weekends
        RETURN v_return;
END;

Hej,

Jeg prøver lige at uddybe lidt.

Jeg har en tabel på en database indeholdende to timestamps vedr. hvornår en sag er modtaget (timestamp 1) og til hvornår en sag er behandlet (timestamp 2). Disse timestamps ønsker jeg at beregne antal mellem disse timestamps indenfor arbejdstid.

Min problemstilling opstår når delta kun skal udregnes indenfor arbejdstid som er defineret i tabellen WF_WORK_CALENDAR

Tabellen WF_WORK_CALENDAR har følgende kolonner. Som det ses så er åbningstiden forskellig dag for dag.

Starttid                  Sluttid
22-01-2013 08:00:00        22-01-2013 16:00:00
23-01-2013 08:00:00        23-01-2013 16:00:00
24-01-2013 08:00:00        24-01-2013 16:00:00
25-01-2013 08:00:00        25-01-2013 15:30:00
25-01-2013 10:00:00        25-01-2013 16:00:00
25-01-2013 00:00:00        25-01-2013 00:00:00

Jeg vil gerne, at beregningen foretages i en database funktion som jeg kan genanvende.

Jeg har fundet følgende funktion som inspiration, men denne kigger dog ikke på tabel som udgangspunkt for åbningstiderne.

https://forums.oracle.com/ (...)

CREATE OR REPLACE
  FUNCTION get_bus_minutes_between(
                                  p_start_date DATE,
                                  p_end_date DATE
                                  )
    RETURN NUMBER
    IS
        v_return NUMBER;
    BEGIN
        with t as (
                  select  case level
                            when 1 then greatest(p_start_date,trunc(p_start_date) + 9 / 24)
                            else trunc(p_start_date) + level - 15 / 24
                          end start_dt,
                          case connect_by_isleaf
                            when 1 then least(p_end_date,trunc(p_end_date) + 18 / 24)
                            else trunc(p_start_date) + level - 8 / 24
                          end end_dt
                    from  dual
                    connect by level <= trunc(p_end_date) - trunc(p_start_date) + 1
                  )
        select  sum(greatest(end_dt - start_dt,0)) * 24 * 60 work_minutes
          into  v_return
          from  t
          where trunc(start_dt) - trunc(start_dt,'iw') < 5; -- exclude weekends
        RETURN v_return;
END;

Her er et forsøg på SQL:

select (least(A.SlutTid,C.SlutTid)
        -greatest(A.StartTid,C.StartTid)
        )*24*60 Minutter
from Arbejdstider A, WF_WORK_CALENDAR C
where C.SlutTid > A.StartTid
  and C.StartTid < A.SlutTid

Den virker desværre ikke helt...

Jeg har fundet en anden funktion som højst sandsynligt bare skal tilpasses.

Nedenstående SQL funktion returnerer tidspunkt hvor hvor jeg har et starttidpunkt og + antal arbejdsminutter.


CREATE OR REPLACE FUNCTION WF_CalcWorkDay(dStartDate date, nWorkMinutes number) return date is
  wdStart date;
  wdEnd date;
  dEndDate date;
  nLeftMinutes number;
BEGIN
if nWorkMinutes is null then
  return(null);
end if;

dEndDate := dStartDate;
nLeftMinutes := nWorkMinutes;

while nLeftMinutes >= 0 loop
BEGIN
  select startdate, enddate
    into wdStart, wdEnd
    from wf_work_calendar
  where to_char(xdate, 'YYYY-MM-DD') = to_char(dEndDate, 'YYYY-MM-DD');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    return(null);
END;
  if dStartDate > wdStart then
  /* Add part of the first workday */
  wdStart := dStartDate;
  end if;

  if 24*60*(wdEnd - wdStart) <= nLeftMinutes then
  /* Add a full workday */
  dEndDate := to_date(to_char(dEndDate, 'YYYY-MM-DD'), 'YYYY-MM-DD') + 1;
  if wdEnd - wdStart > 0 then
      nLeftMinutes := nLeftMinutes - 24*60*(wdEnd - wdStart);
  end if;
  else
  /* Add the part of the last workday */
  dEndDate := wdStart + nLeftMinutes/24/60;
  nLeftMinutes := -1;
  end if;
end loop;

  return(dEndDate);
END;

Virker ikke helt - virker det lidt?

I dine beskrivelse af åbningstider har du tider der lapper over hinanden den 25/1-13 - er det korrekt?

25-01-2013 08:00:00        25-01-2013 15:30:00
25-01-2013 10:00:00        25-01-2013 16:00:00

Jeg har efterprøvet mit SQL og det virker fint hvis der ikke er overlappende åbningstider. Så er problemet her?