代码之家 › 专栏 › 技术社区 › D.S.

确定如何在SQL Server中计算分割周

datetime sql-server-2005

D.S. · 技术社区 · 14 年前

鉴于:

@StartDate 和 @EndDate

col_week_end_date 将始终在星期六结束,并且是DateTime列。

col_payment_amt

使用2010年9月 列付款金额 列周结束日期 2010年4月9日,涵盖8月29日至9月4日这一周。

同样的事情也会发生在月末。在这种情况下

我有一种特别的方法来计算 列付款金额 当这种情况发生在一个月的开始和结束。

目前如果我只是强迫他们运行一个月的报告,没有问题,我已经被告知这只是一个月的报告,但我知道

我知道基本上是这样的:

SELECT sum(CASE WHEN at-top-of-month-with-split-week THEN....
                WHEN at-bottom-of-month-with-split-week THEN...
                ELSE col_payment_amt END) as PayTotal
FROM...
WHERE....
GROUP BY...

我想弄清楚月底和月顶的部分。我的其他部分。

2 回复 | 直到 14 年前

Jonathan Leffler 14 年前

这段代码在IBMInformixDynamicServer中工作(在MacOSX1.06.4的11.50.FC6上进行了测试,但可以在任何支持的IDS版本和任何平台上工作)。您需要将其转换为mssqlserver符号。

版本2-仅以月份编号标识的参考月份

CREATE FUNCTION NumWeekDaysInRefMonth(eow DATE DEFAULT TODAY, ref_month INTEGER)
    RETURNING INT AS numdays;
    DEFINE monday DATE;
    DEFINE friday DATE;
    DEFINE mon_month INTEGER;
    DEFINE fri_month INTEGER;

    IF eow IS NULL OR ref_month IS NULL THEN RETURN NULL; END IF;

    LET monday = eow - WEEKDAY(eow) + 1;
    LET friday = monday + 4;
    LET mon_month = MONTH(monday);
    LET fri_month = MONTH(friday);

    IF mon_month = ref_month AND fri_month = ref_month THEN
        -- All in same month: 5 days count.
        RETURN 5;
    END IF;
    IF mon_month != ref_month AND fri_month != ref_month THEN
        -- None in the same month: 0 days count.
        RETURN 0;
    END IF;
    -- Some of the days are in the same month, some are not.
    IF mon_month = ref_month THEN
        -- End of month
        RETURN 5 - DAY(friday);
    ELSE
        -- Start of month
        RETURN DAY(friday);
    END IF;

END FUNCTION;

SELECT NumWeekDaysInRefMonth('2010-09-04',  9) answer, 3 AS expected FROM dual;
SELECT NumWeekDaysInRefMonth('2010-09-04',  8) answer, 2 AS expected FROM dual;
SELECT NumWeekDaysInRefMonth('2010-10-02',  9) answer, 4 AS expected FROM dual;
SELECT NumWeekDaysInRefMonth('2010-10-02', 10) answer, 1 AS expected FROM dual;
SELECT NumWeekDaysInRefMonth('2010-10-02',  8) answer, 0 AS expected FROM dual;
SELECT NumWeekDaysInRefMonth('2010-10-09', 10) answer, 5 AS expected FROM dual;

这与上一个版本(见下文)的逻辑基本相同;只需一个月号即可确定您感兴趣的计费月份,而不是完整的日期。

版本1-完整参考日期

CREATE FUNCTION NumWeekDaysInRefMonth(eow DATE DEFAULT TODAY, ref DATE DEFAULT TODAY)
    RETURNING INT AS numdays;
    DEFINE mon DATE;
    DEFINE fri DATE;
    DEFINE v_mon INTEGER;
    DEFINE v_fri INTEGER;
    DEFINE v_ref INTEGER;

    IF eow IS NULL OR ref IS NULL THEN RETURN NULL; END IF;

    LET mon = eow - WEEKDAY(eow) + 1;
    LET fri = mon + 4;
    LET v_mon = YEAR(mon) * 100 + MONTH(mon);
    LET v_fri = YEAR(fri) * 100 + MONTH(fri);
    LET v_ref = YEAR(ref) * 100 + MONTH(ref);

    IF v_mon = v_ref AND v_fri = v_ref THEN
        -- All in same month: 5 days count.
        RETURN 5;
    END IF;
    IF v_mon != v_ref AND v_fri != v_ref THEN
        -- None in the same month: 0 days count.
        RETURN 0;
    END IF;
    -- Some of the days are in the same month, some are not.
    IF v_mon = v_ref THEN
        -- End of month
        RETURN 5 - DAY(fri);
    ELSE
        -- Start of month
        RETURN DAY(fri);
    END IF;

    -- Month-end wrapping
    -- 26 27 28 29 30 31  1  2  3  4  5  6  Jan, Mar, May, Jul, Aug, Oct, Dec
    -- 25 26 27 28 29 30  1  2  3  4  5  6  Apr, Jun, Sep, Nov
    -- 24 25 26 27 28 29  1  2  3  4  5  6  Feb - leap year
    -- 23 24 25 26 27 28  1  2  3  4  5  6  Feb
    -- Mo Tu We Th Fr Sa Su Mo Tu We Th Fr
    -- Su Mo Tu We Th Fr Sa Su Mo Tu We Tu
    -- Sa Su Mo Tu We Th Fr Sa Su Mo Tu We
    -- Fr Sa Su Mo Tu We Th Fr Sa Su Mo Tu
    -- Th Fr Sa Su Mo Tu We Th Fr Sa Su Mo
    -- We Th Fr Sa Su Mo Tu We Th Fr Sa Su
    -- Tu We Th Fr Sa Su Mo Tu We Th Fr Sa

END FUNCTION;

SELECT NumWeekDaysInRefMonth('2010-09-04', '2010-09-01') answer, 3 AS expected FROM dual;
SELECT NumWeekDaysInRefMonth('2010-09-04', '2010-08-01') answer, 2 AS expected FROM dual;
SELECT NumWeekDaysInRefMonth('2010-10-02', '2010-09-01') answer, 4 AS expected FROM dual;
SELECT NumWeekDaysInRefMonth('2010-10-02', '2010-10-01') answer, 1 AS expected FROM dual;
SELECT NumWeekDaysInRefMonth('2010-10-02', '2010-08-01') answer, 0 AS expected FROM dual;
SELECT NumWeekDaysInRefMonth('2010-10-09', '2010-10-01') answer, 5 AS expected FROM dual;

解释

Informix DATE类型以天为单位,因此将1添加到日期后一天。Informix WEEKDAY函数返回0表示星期天,1表示星期一,…,5表示星期五,6表示星期六。DAY、MONTH和YEAR函数返回日期值的相应组件。

代码允许一周中的任何一天作为付款的参考日期(不必是星期六)。类似地,尽管版本1的示例使用月份的第一天作为该月份的参考日,但是您可以提供必需月份内的任何日期作为参考日;在版本2中,这简化为传递必需的月份号。

如果有人将NULL传递给函数,则答案为NULL。

然后我们计算包含“周末”日期的星期一;如果星期天是星期天,则减去0,再加1得到星期一;如果星期天是星期天,则减去6,再加1得到星期一;以此类推,星期五是4天后。

在版本1中,我们计算年和月的表示形式。

如果星期一和星期五都在参考月份,那么答案是5天;如果星期一和星期五都不在参考月份,那么答案是0天。如果星期五在参考月份内,则星期五日期的DAY()值是该月份的天数。否则,星期一在参考月份内,该月份的天数为5天(星期五)。

请注意,此计算处理的是完全不相关的月份—如最后一个测试用例所示;10月份支付的款项中有零天应计入8月份。

D.S. 14 年前

好吧,这就是我最后要做的。

我设置了一个WHILE循环。我循环查看日期范围的月份,并将数据插入一个tentable中,同时插入一个数字参考月份列(实际上是INT)和月份名称(九月、十月等)。从诱人的我,然后选择数据,并有一个CASE语句,决定使用哪个计算。所以它读起来像这样。。。

                -- Top of the month, Split Week
select sum(case when datepart(d,col_week_end_date) <= 7 AND RefMonth = month(col_ween_end_date)
                then ...Top of Month calc...

                -- Middle of the month, entire week is in Reference Month
                when datepart(d,col_week_end_date) > 7 AND RefMonth = month(col_week_end_date)
                then ...Just SUM the column as usual...

                -- Bottom of month, Split Week
                when RefMonth < month(col_week_end_date)
                then ...Bottom of Month calc...
          ) end as MonthlySum,
       col_RefMonth,
       col_RefMonthName
from dbo.TempTable
group by col_RefMonth, col_RefMonthName
order by col_RefMonth

到目前为止,由于我拥有的数据量有限,它似乎在处理我所拥有的月份中的分周工作。似乎“参考月”是关键,我需要循环,把数据放在一个诱人的地方。我希望能有一种方法让它在一个单一的通行证,或至少一个快速倾倒到一个#诱惑和选择从那里。