我将为表的每个记录创建SI，从存储过程中获取错误:

No column name was specified for column 6 of 'cte_Alldates'.

create proc sp_calcualtteSI
as
begin
    DECLARE @today datetime

    SET @today = dateadd(day,datediff(day,0,current_timestamp),0)

    ; WITH cte_dates AS
    (
        SELECT DISTINCT
            name, Pamount, Rateofint, cdate,
            CASE 
               WHEN ISNULL(today, @today) < DATEADD(month, 1, DATEADD(month, datediff(month, 0, cdate), 0))
                  THEN ISNULL(@today, @today)
               ELSE 
                  DATEADD(month, 1, dateadd(month, datediff(month, 0, cdate), 0))
            END AS MonthEnd,
            ISNULL(@today, @today) AS End_date
        FROM 
            tbl_intestcalculate),
    cte_Alldates AS
    (
        SELECT
            name, Pamount, Rateofint, cdate, monthEnd, @today
        FROM
            cte_dates

        UNION

        SELECT 
            name, Pamount, Rateofint, 
            DATEADD(month, number, monthEnd),
            CASE 
               WHEN DATEADD(month, number + 1, monthEnd) < @today
                  THEN DATEADD(month, number + 1, monthEnd) 
                  ELSE @today
            END, 
            @today
        FROM 
            cte_dates c
        CROSS JOIN
            (SELECT number 
             FROM master..spt_values 
             WHERE type = 'p' AND number BETWEEN 0 AND 11) a 
        WHERE
             dateadd(month, number, monthEnd) < @today
    )
    SELECT  
        name, cdate, monthEnd, Pamount, Rateofint,
        DATEDIFF(day, cdate, monthEnd) AS No_Of_Days,
        ROUND(Pamount * Rateofint * DATEDIFF(day, cdate, monthEnd) / 36500, 2) AS SI
    FROM
        cte_Alldates
END

最佳答案

CTE需要为所有列指定列名。在这种情况下，您将缺少第六列的列名。您的cte_Alldates CTE应该为:

cte_Alldates AS
(
    SELECT
        name, Pamount, Rateofint, cdate, monthEnd, [day]=@today
    FROM
        cte_dates

    UNION

    SELECT 
        name, Pamount, Rateofint, 
        DATEADD(month, number, monthEnd),
        CASE 
           WHEN DATEADD(month, number + 1, monthEnd) < @today
              THEN DATEADD(month, number + 1, monthEnd) 
              ELSE @today
        END, 
        [day]=@today
    FROM 
        cte_dates c
    CROSS JOIN
        (SELECT number 
         FROM master..spt_values 
         WHERE type = 'p' AND number BETWEEN 0 AND 11) a 
    WHERE
         dateadd(month, number, monthEnd) < @today
)

https://stackoverflow.com/questions/35239618/