Determine how to calculate split weeks in SQL Server

Question

Im not quite sure what the term is, I have been calling it "Split Weeks" but here is what I need to find out.

Given:

User will input @StartDate and @EndDate

col_week_end_date will always end on a Saturday, and is a DateTime column.

I want to cycle through either multiple or a single month(s) and sum col_payment_amt

Using the month of September 2010, a col_payment_amt with a col_week_end_date falls on 09/04/2010, which covers the week of Aug 29 - Sep 04.

The payment month is September, but only 3 workdays fall w/i this week (Wed, Thurs, Fri). So only 3/5ths of the payment is made for that week.

The same thing happens with the end of a month. In this case, the col_week_end_date falls on 10/02/2010. Only 4/5ths of the payment will be made for this week.

I have a particular way to sum the col_payment_amt when this happens at the beginning of a month, and also at the end.

What I can't figure out is how to tell when I am at the start of a month, and when i am at the end of a month so I can apply the appropriate function, when running the report for multiple months (Aug - Oct).

Currently if I just force them to run the report for a single month, no problems, and I have been told it is only a monthly report, but I know eventually I will be asked if it can be run for multiple months.

I know it is basically something like:

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

I'm trying to figure out the at-top-of-month and at-bottom-of-month parts. The other parts I have.

Answer 1

Ok so here is what I ended up doing in a nutshell.

I set up a WHILE loop. I loop through the months for the date range, and insert the data into a TempTable along with a numeric Reference Month column (well, really an INT), and the month name (September, October etc...). From the TempTable I then select the data and have a CASE statement which determines which calculation to use. So it reads something like this...

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

So far with the limited amount of data I have, it appears to be working with the split weeks in the months I have available. Seems the "Reference Month" was the key, and I needed to loop through and put the data in a TempTable. I was hoping there would be a way to get it in a single pass, or a least a quick dump into a #TempTable and select from there.

I'm not sure how efficient it will be, but it is only for monthly reporting and not really as an online interactive report.

Answer 2

This code works in IBM Informix Dynamic Server (tested on 11.50.FC6 for MacOS X 1.06.4, but would work on any supported version of IDS, and any platform). You will need to translate into MS SQL Server notation.

Version 2 - reference month identified by month number only

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;

Test cases

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;

This has much the same logic as the previous version (below); it takes just a month number to identify which billing month you are interested in, rather than a full date.

Version 1 - full reference date

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;

Test cases

These tests are equivalent to the previous set.

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;

Explanation

The Informix DATE type counts in days, so adding 1 to a DATE gives the day after. The Informix WEEKDAY function returns 0 for Sunday, 1 for Monday, ..., 5 for Friday, 6 for Saturday. The DAY, MONTH and YEAR functions return the corresponding component of a DATE value.

The code allows any day of the week as the reference date for the payment (it does not have to be Saturday). Similarly, although the examples for version 1 use the first of the month as the reference day for the month, you can supply any date within the requisite month as the reference date; in version 2, that is simplified to passing in the requisite month number.

If anyone passes a NULL into the function, the answer is NULL.

Then we calculate the Monday of the week containing the 'end of week' date; if the week day is Sunday, it subtracts 0 and adds 1 to get Monday; if the week day is Sturday, it subtracts 6 and adds 1 to get Monday; etc. Friday is 4 days later.

In version 1, then we calculate a representation for the year and month.

If both Monday and Friday fall in the reference month, then the answer is 5 days; if neither falls in the reference month, the answer is 0 days. If the Friday is within the reference month, then the DAY() value of the Friday date is the number of days in the month. Otherwise, the Monday is within the reference month, and the number of days in the month is 5 - DAY(Friday).

Note that this calculation deals with completely unrelated months - as shown by the last but one test case; there are zero days of a payment made October that should be counted in August.