确定如何计算Server中的分割周

Question

我不太清楚这个词是什么，我一直称它为“分周”，但我需要知道的是。

给予：

用户将输入@StartDate和@EndDate

col_week_end_date总是在星期六结束，并且是DateTime列。

我想通过多个月或一个月的周期和求和col_payment_amt。

使用2010年9月，一个带有col_week_end_date的col_week_end_date在09年/ 04 / 2010下跌，涵盖8月29日至04年9月的一周。

付款月份是九月，但本周只有三个工作日(韦德，星期四，星期五)。因此，那一周只支付了5/5的款项。

一个月底也会发生同样的事情。在这种情况下，col_week_end_date将于2010年10月2日生效。这周只支付了五分之四的款项。

我有一种特殊的方法来总结col_payment_amt，当这种情况发生在一个月的开始和结束时。

我想不出的是，当我在一个月开始的时候，当我在一个月底的时候，如何判断，以便在运行多个月(8月至10月)时，我可以应用适当的函数。

目前，如果我只是强迫他们做一个月的报告，没有问题，有人告诉我这只是一个月的报告，但我知道最终我会被问到是否可以运行几个月。

我知道基本上是这样的：

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...

我在试着找出月顶和月底的部分。我还有其他的部分。

Answer 1

此代码在中工作(在11.50.FC6上测试了MacOS X 1.06.4，但可以在任何受支持的IDS版本和任何平台上工作)。您需要将其转换为MS SQL Server符号。

第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类型以天数为单位，因此将1添加到一个日期是后天的结果。Informix周日函数返回0，星期一返回1，周五返回5，星期六返回6。日期、月份和年份函数返回日期值的相应组件。

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

如果有人向函数传递NULL，则答案为NULL。

然后，我们计算包含“周末”日期的周一；如果周一是周日，它减去0，加上1得到星期一；如果周一是星期一，它减去6，加上1得到星期一；等等，星期五是4天之后。

在版本1中，我们计算年份和月份的表示。

如果星期一和星期五都落在参考月份，那么答案是5天；如果两个都不在参考月份，则答案是0天。如果星期五在参考月份内，则星期五日期的日期()值是月份中的天数。否则，星期一是参考月份，月中的天数为5天(星期五)。

请注意，此计算处理完全不相关的月份--如上一次测试用例所示；十月份付款的天数为零，应该在8月份计算。

Answer 2

好的，这就是我最后所做的，简单地说。

我设置了一个时间循环。我循环遍历日期范围的月份，并将数据与数字引用月份列(实际上是INT)和月份名称(9月、10月等)一起插入到TempTable中。然后从TempTable中选择数据，并有一个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

到目前为止，由于我所掌握的数据数量有限，它似乎在处理我可用的几个月中的几个星期。似乎“参考月”是关键，我需要循环并将数据放在TempTable中。我希望能有一种方法，让它在一次传递，或至少一个快速转储到一个#的诱惑，并从中选择。

我不知道它将有多高的效率，但它只是用于每月的报告，而不是真正作为一个在线互动报告。