Re: workdays between 2 dates - SPL solution

Another way of doing this is via a Stored Procedure: I have included the code that I took from an I-4GL program that (I think) Jack Parker wrote. The Engine it runs on is Informix 7.11.UD1. I don't know if this code is backward compatible to earlier releases. I left in the whole thing since it works. It uses a holiday table that contains holidays that someone has to enter at some point. The holiday table cannot contain Saturday or Sunday dates. I have triggers to prevent this. The 2 dates cannot be Saturday or Sunday (or holidays) either. However, you can program around this by moving the beginning or ending dates to the nearest Monday or Friday. I think I can explain the algorithm if you want: > > I am trying to write a SQL statement that will look at 2 dates and determine > the number of work days there are between them. I need to issue the SQL > against many rows. If I can at least exclude the week-ends that would be > great. The holidays, a bonus.... Does anyone have an answer? > Start SPL: DROP PROCEDURE k_route_info; CREATE PROCEDURE k_route_info(i_aps char(4), i_fis char (1), i_seq char(25)) RETURNING char(4), char(1), char(25), char(1), char(6), char(2), char(1), char(4), char(25), datetime year to fraction(5), datetime year to fraction(5), datetime year to fraction(5), int, int;DEFINE num_holiday INTEGER; DEFINE sw_aps_code char(4); DEFINE sw_fiscal_yr char(1); DEFINE sw_sequence_number char(25); DEFINE sw_action_code char(1); DEFINE sw_unix_id char(6); DEFINE sw_disp_type char(2); DEFINE sw_in_process char(1); DEFINE sw_dest_aps_code char(4); DEFINE sw_delivered_to char(25); DEFINE sw_receive_date datetime year to fraction(5); DEFINE sw_disp_date datetime year to fraction(5); DEFINE sw_action_date datetime year to fraction(5); DEFINE from_date DATE; DEFINE work_date DATE; DEFINE to_date DATE; DEFINE offset INTEGER; DEFINE wk_days, total_days INTEGER; DEFINE offitv INTERVAL DAY(5) TO DAY; DEFINE adjust INT; set debug file to "/users/informix/procedure.trace"; trace on; trace "begin trace."; select min(receive_date_time), max(disp_date) into from_date, to_date from status where aps_code = i_aps and fiscal_year = i_fis and sequence_number = i_seq; LET offset = WEEKDAY(from_date) - 1; LET offitv = offset UNITS DAY; LET work_date = from_date - offitv; LET total_days = to_date - work_date; LET adjust = total_days; LET adjust = (adjust / 7); LET adjust = adjust * 2; LET total_days = total_days - adjust; LET total_days = total_days - offset; SELECT count(*) INTO num_holiday FROM holiday_dates WHERE holiday > from_date AND holiday < to_date;LET total_days = total_days - num_holiday; FOREACH select aps_code, fiscal_year, sequence_number, action_code, unix_id, disp_type, in_process, dest_aps_code, delivered_to, receive_date_time, disp_date, action_date into sw_aps_code, sw_fiscal_yr, sw_sequence_number, sw_action_code, sw_unix_id, sw_disp_type, sw_in_process, sw_dest_aps_code, sw_delivered_to, sw_receive_date, sw_disp_date, sw_action_date from status where aps_code = i_aps and fiscal_year = i_fis and sequence_number = i_seq order by receive_date_time desc LET from_date = sw_receive_date; LET to_date = sw_disp_date; select count(*) into num_holiday from holiday_dates where holiday = from_date; IF num_holiday <> 0 THEN RAISE EXCEPTION -746, 0, "BEGIN DATE CONNOT BE HOLIDAY."; END IF select count(*) into num_holiday from holiday_dates where holiday = to_date; IF num_holiday <> 0 THEN RAISE EXCEPTION -746, 0, "END DATE CANNOT BE HOLIDAY."; END IF LET offset = WEEKDAY(from_date); IF offset = 0 THEN RAISE EXCEPTION -746, 0, "BEGIN DATE CANNOT BE SUNDAY."; END IF IF OFFSET = 6 THEN RAISE EXCEPTION -746, 0, "BEGIN DATE CANNOT BE SATURDAY."; END IF LET offset = WEEKDAY(to_date); IF offset = 0 THEN RAISE EXCEPTION -746, 0, "END DATE CANNOT BE SUNDAY."; END IF IF OFFSET = 6 THEN RAISE EXCEPTION -746, 0, "END DATE CANNOT BE SATURDAY."; END IF LET offset = WEEKDAY(from_date) - 1; LET offitv = offset UNITS DAY; LET work_date = from_date - offitv; LET wk_days = to_date - work_date; LET adjust = wk_days; LET adjust = (adjust / 7); LET adjust = adjust * 2; LET wk_days = wk_days - adjust; LET wk_days = wk_days - offset; SELECT count(*) INTO num_holiday FROM holiday_dates WHERE holiday > from_date AND holiday < to_date; LET wk_days = wk_days - num_holiday; RETURN sw_aps_code, sw_fiscal_yr, sw_sequence_number, sw_action_code, sw_unix_id, sw_disp_type, sw_in_process, sw_dest_aps_code, sw_delivered_to, sw_receive_date, sw_disp_date, sw_action_date, wk_days, total_days WITH RESUME; END FOREACH; trace off; END PROCEDURE with listing in '/users/informix/procedure.warnings'; end SPL donald_boothby_at_isdlan@ima.isd.state.in.us (317)232-4602 Don Boothby (DBA) State of Indiana - ISD Room N551, IGCN 100 N Senate Ave Indianapolis, IN 46204