SQL 服务器 : Convert months in calendar year to months in fiscal year

Question

我有一个独特的问题，我根本无法解决。

所以我在 SQL Server 2005 中，我得到了以下要处理的数据:

1. FISCAL_YEAR_START_MONTH INT(财政年度的第一个月)
2. COUNT_START_MONTH INT(我们需要开始的第一个月
3. TOTAL_MONTHS
4. YEAR[1-6]_MONTHS - 这是每个日历年的月数

我需要在 6 个会计年度 + 剩余部分中分配月份

例如:

FISCAL_YEAR_START_MONTH = 7
COUNT_START_MONTH = 9
TOTAL_MONTHS = 36
YEAR1_MONTHS = 4
YEAR2_MONTHS = 12
YEAR3_MONTHS = 12
YEAR4_MONTHS = 8

应该输出

YEAR1_MONTHS YEAR2_MONTHS YEAR3_MONTHS YEAR4_MONTHS YEAR5_MONTHS YEAR6_MONTHS LEFTOVER
------------ ------------ ------------ ------------ ------------ ------------ ----------
10           12           12           2            0            0            0

我就是用sql解决不了这个问题。作为人类，很容易看出月份应该按具体情况分配到哪里，但我无法将其转化为算法。

我试着计算每年的日期，并不断计算我还剩下多少个月，但都没有解决我的问题。

任何解决方案，即使是针对上述示例的解决方案(但理想情况下是通用解决方案)都会非常有用!

编辑:修复了翻转问题，好像我收到了错误的数据

EDIT2:另一个例子，一个简单的例子:

FISCAL_YEAR_START_MONTH = 7
COUNT_START_MONTH = 5
TOTAL_MONTHS = 14
YEAR1_MONTHS = 8
YEAR2_MONTHS = 6

应该输出

YEAR1_MONTHS YEAR2_MONTHS YEAR3_MONTHS YEAR4_MONTHS YEAR5_MONTHS YEAR6_MONTHS LEFTOVER
------------ ------------ ------------ ------------ ------------ ------------ ----------
2           12            0            0            0            0            0

Answer 1

--Test table.
CREATE TABLE #CalTest (
    ExampleNumber SMALLINT IDENTITY,
    FiscalYearStartMonth SMALLINT,
    CountStartMonth SMALLINT,
    TotalMonths SMALLINT,
    Year1Months SMALLINT,
    Year2Months SMALLINT,
    Year3Months SMALLINT,
    Year4Months SMALLINT,
    Year5Months SMALLINT,
    Year6Months SMALLINT,
    Leftover SMALLINT,
)
GO

--Sample data as per the OP.
INSERT INTO #CalTest (FiscalYearStartMonth, CountStartMonth, TotalMonths, Year1Months, Year2Months, Year3Months, Year4Months, Year5Months, Year6Months, Leftover)
VALUES
    (7,9,36,4,12,12,8,0,0,0),
    (7,5,14,8,6,0,0,0,0,0)
GO

SELECT * FROM #CalTest 
GO

CREATE FUNCTION guest.PickMin
--This function simply picks the smaller of two numbers.
(
    @FirstSmallInt SMALLINT,
    @SecondSmallInt SMALLINT
)
RETURNS SMALLINT
AS
BEGIN
    DECLARE @RetVal SMALLINT

    IF @FirstSmallInt < @SecondSmallInt 
        SET @RetVal = @FirstSmallInt
    ELSE 
        SET @RetVal = @SecondSmallInt

    RETURN @RetVal
END
GO

;WITH CTE AS
(
    --Calculations for Year 1.
    SELECT 
        c.ExampleNumber, c.FiscalYearStartMonth, c.CountStartMonth, 1 AS YearNumber, c.TotalMonths,

        CASE
            WHEN c.CountStartMonth > c.FiscalYearStartMonth THEN
                guest.PickMin( 12 - (c.CountStartMonth - c.FiscalYearStartMonth), c.TotalMonths ) 
            ELSE
                c.FiscalYearStartMonth - c.CountStartMonth
        END AS Year1Months,

        --These are placeholders for Year2 - Year6.
        CAST(NULL AS SMALLINT) AS Year2Months, CAST(NULL AS SMALLINT) AS Year3Months, CAST(NULL AS SMALLINT) AS Year4Months, CAST(NULL AS SMALLINT) AS Year5Months, CAST(NULL AS SMALLINT) AS Year6Months,

        --Calculate the left over months.
        c.TotalMonths - guest.PickMin( 
            CASE
                WHEN c.CountStartMonth > c.FiscalYearStartMonth THEN
                    guest.PickMin( 12 - (c.CountStartMonth - c.FiscalYearStartMonth), c.TotalMonths ) 
                ELSE
                    c.FiscalYearStartMonth - c.CountStartMonth
            END, 
            c.TotalMonths 
            ) AS Leftover
    FROM #CalTest c

    UNION ALL
        --Calculations for Year 2
        SELECT 
        c2.ExampleNumber, c2.FiscalYearStartMonth, c2.CountStartMonth, c2.YearNumber + 1, c2.TotalMonths,
        NULL, guest.PickMin(12, c2.Leftover), NULL, NULL, NULL, NULL,
        c2.Leftover - guest.PickMin( 12, c2.Leftover)
    FROM CTE c2
    WHERE c2.YearNumber = 1

    UNION ALL
        --Calculations for Year 3
        SELECT 
        c2.ExampleNumber, c2.FiscalYearStartMonth, c2.CountStartMonth, c2.YearNumber + 1, c2.TotalMonths,
        NULL, NULL, guest.PickMin(12, c2.Leftover), NULL, NULL, NULL,
        c2.Leftover - guest.PickMin( 12, c2.Leftover)
    FROM CTE c2
    WHERE c2.YearNumber = 2

    UNION ALL
        --Calculations for Year 4
        SELECT 
        c2.ExampleNumber, c2.FiscalYearStartMonth, c2.CountStartMonth, c2.YearNumber + 1, c2.TotalMonths,
        NULL, NULL, NULL, guest.PickMin(12, c2.Leftover), NULL, NULL,
        c2.Leftover - guest.PickMin( 12, c2.Leftover)
    FROM CTE c2
    WHERE c2.YearNumber = 3

    UNION ALL
        --Calculations for Year 5
        SELECT 
        c2.ExampleNumber, c2.FiscalYearStartMonth, c2.CountStartMonth, c2.YearNumber + 1, c2.TotalMonths,
        NULL, NULL, NULL, NULL, guest.PickMin(12, c2.Leftover), NULL,
        c2.Leftover - guest.PickMin( 12, c2.Leftover)
    FROM CTE c2
    WHERE c2.YearNumber = 4

    UNION ALL
        --Calculations for Year 6
        SELECT 
        c2.ExampleNumber, c2.FiscalYearStartMonth, c2.CountStartMonth, c2.YearNumber + 1, c2.TotalMonths,
        NULL, NULL, NULL, NULL, NULL, guest.PickMin(12, c2.Leftover), 
        c2.Leftover - guest.PickMin( 12, c2.Leftover)
    FROM CTE c2
    WHERE c2.YearNumber = 5
)
SELECT 
    --Comment out the next line if you don't want it in your output.
    ExampleNumber, FiscalYearStartMonth, CountStartMonth, TotalMonths,

    --These are the output columns the OP asked for.
    --SUM() will exclude the NULL values used as placeholders in the above CTE.
    SUM(Year1Months) Year1Months,
    SUM(Year2Months) Year2Months,
    SUM(Year3Months) Year3Months,
    SUM(Year4Months) Year4Months,
    SUM(Year5Months) Year5Months,
    SUM(Year6Months) Year6Months,
    MIN(Leftover) Leftover
FROM CTE
GROUP BY ExampleNumber, FiscalYearStartMonth, CountStartMonth, TotalMonths
ORDER BY ExampleNumber

--Drop as needed.
DROP FUNCTION guest.PickMin
GO