Hello,
I have a basic function that count the number of Mondays between two dates:
create or replace FUNCTION COUNT_MONDAYS(p_startdate DATE, p_enddate DATE) RETURN NUMBER
AS
v_NUMBEROFDAYS NUMBER;
BEGIN
SELECT COUNT(1) INTO v_NUMBEROFDAYS
FROM dual
WHERE TO_CHAR(p_startdate+LEVEL-1, 'D', 'nls_date_language=ENGLISH') IN ('2')
CONNECT BY TRUNC(p_startdate)+LEVEL-1 <= TRUNC(p_enddate);
RETURN v_NUMBEROFDAYS;
END;
What I'd like to do is add an 'if then' to give me the count of days depending on the day provided. For example, I have a schedule table. Schedules can be Mon - Fri and some can be a mix of days. I'd like to be able to count the number of individual days based on the schedule.
I have attempted this however the statement hangs when I run it in SQL workshop. Can you give any advice?
create or replace FUNCTION FN_COUNT_DAYS(p_Day VARCHAR2, p_startdate DATE, p_enddate DATE) RETURN NUMBER
AS
v_NUMBEROFDAYS NUMBER;
BEGIN
IF p_DAY = 'Monday' then
SELECT COUNT(1) INTO v_NUMBEROFDAYS
FROM tran_schedule
WHERE TO_CHAR(p_startdate+LEVEL-1, 'D', 'nls_date_language=ENGLISH') IN ('2')
CONNECT BY TRUNC(p_startdate)+LEVEL-1 <= TRUNC(p_enddate);
RETURN v_NUMBEROFDAYS;
ELSE
IF p_DAY = 'Tuesday' then
SELECT COUNT(1) INTO v_NUMBEROFDAYS
FROM tran_schedule
WHERE TO_CHAR(p_startdate+LEVEL-1, 'D', 'nls_date_language=ENGLISH') IN ('3')
CONNECT BY TRUNC(p_startdate)+LEVEL-1 <= TRUNC(p_enddate);
RETURN v_NUMBEROFDAYS;
ELSE
IF p_DAY = 'Wednesday' then
SELECT COUNT(1) INTO v_NUMBEROFDAYS
FROM tran_schedule
WHERE TO_CHAR(p_startdate+LEVEL-1, 'D', 'nls_date_language=ENGLISH') IN ('4')
CONNECT BY TRUNC(p_startdate)+LEVEL-1 <= TRUNC(p_enddate);
RETURN v_NUMBEROFDAYS;
END IF;
END IF;
END IF;
END;
schedule table:
create table Schedule
(schedule_id number(38,0)constraint schedule_schedule_id_PK primary key,
run_id number(38,0) not null,
constraint schedule_run_id_fk foreign key (run_id) references run(run_id),
day varchar2(10)not null,
arrival_time char(5)not null,
dropoff_loc varchar2(100)not null,
collection_time char(5)not null,
pickup_loc varchar2(100)not null);
Thank you
The 'connect by' approach is going to work when you have a single row to work with. That's why your working example has a select-from-dual. We are then synthesising rows out of this single row.
So when you replace that with 'tran_schedule' you need to first be extracting a single row from this table.
I'm not entirely sure what the contents of tran_schedule is here, and where it fits into what your requirement is - I'm making a *guess* that for a given schedule you want to find the 'DAY' and use that for the passed start/end.
So you'd have something like:
SELECT COUNT(1) INTO v_NUMBEROFDAYS
FROM ( select day from tran_schedule where run_id = p_run_id ) x
WHERE TO_CHAR(p_startdate+LEVEL-1, 'DAY', 'nls_date_language=ENGLISH') = x.day
CONNECT BY TRUNC(p_startdate)+LEVEL-1 <= TRUNC(p_enddate);