在Informix中计算过去8个工作日 [英] Calculate 8 working days in past in Informix

问题描述

我正在尝试用C shell编写一个脚本，以便在过去8个工作日内从Informix数据库中选择数据。
到目前为止，我的sql代码可以计算过去+周日和周六的8天，如下所示：

I'm trying to write a script in C shell for selecting data from Informix database 8 working days in past. So far I have sql code that calculate 8 days in past + Sunday and Saturday it looks like this:

select *
from ekzo 
where datzah = today-
(case
        when weekday(today) = 1 then 12
        when weekday(today) = 2 then 12
        when weekday(today) = 3 then 12
        when weekday(today) = 4 then 10
        when weekday(today) = 5 then 10
        when weekday(today) = 6 then 10
        when weekday(today) = 0 then 11
        end)

我有创建的表 prazkal具有如下所示的假期：

I have created table "prazkal" with holidays that looks like this:

datpra  01.01.2014
nazpra  Nova Godina
krapra  SRI

datpra  06.01.2014
nazpra  Bogojavljanje ili Sveta tri kralja
krapra  PON

datpra  20.04.2014
nazpra  Uskrs
krapra  NED

datpra  21.04.2014
nazpra  Uskršnji ponedjeljak
krapra  PON

...

考虑周末和节假日，我不知道如何扩展sql以计算过去的8个工作日。

I don't know how to extend my sql to calculate 8 working days in past, considering weekends and holidays.

推荐答案

我会在2个功能中做到这一点。第一个函数检查天是否是假期：

I would do it in 2 functions. First function checks if day is a holiday:

create function is_holiday(d datetime year to day)
returning boolean;
    -- define hcnt integer;

    if weekday(d) = 0 or weekday(d) = 6 then
        return 't';
    end if;

    -- code that check if 'd' is marked as holiday in calendar
    --select count(*) into hcnt from prazkal where datpra = d;
    --if hcnt > 0 then
    --  return 't';
    --end if;

    return 'f';
end function;

第二个功能将日期缩短了几天，从而省略了假期：

Second function decreases date by some days omitting holidays:

create function move_date_back(start_d datetime year to day, count_days integer)
returning datetime year to day;
define new_d datetime year to day;
define i integer;
    let i = 0;
    let new_d = start_d;

    while i < count_days
        let new_d = new_d - interval(1) day to day;
        if not is_holiday(new_d) then
            let i = i + 1;
        end if;
    end while;

    return new_d;
end function;

这篇关于在Informix中计算过去8个工作日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！