代码之家 › 专栏 › 技术社区 › Doonie Darkoo

如何在SQL Server中根据条件动态分配列的所有月份?

sql-server

Doonie Darkoo · 技术社区 · 6 年前

Product Name Renewal Date 以及付款计划 (Monthly/Quarterly/Yearly) . 如果产品的付款计划是 Yearly or Monthly 它将显示获取更新月份并显示 Rate/Amount Monthly 它应该显示 费率/金额 在每个月之前,如下所示。

ABC Yearly ,认购率 276 续签日期 2019-12-01 XYZ 每月 ,认购率 17 续签日期 2019-08-15

ProductName    RenewalMonth   Rate
------------------------------------
ABC            December       276
XYZ            January         17
XYZ            February        17
XYZ            March           17
XYZ            April           17
XYZ            May             17
XYZ            June            17
XYZ            July            17
XYZ            August          17
XYZ            September       17
XYZ            October         17
XYZ            November        17
XYZ            December        17

这是我写的查询,它返回的数据在数据库中很好,但不是12月份以外的月份 Product XYZ 'Monthly' ,对于其他付款计划,它应显示给定月份之前的费率,如数据库中所示。

样本数据如下:

CREATE TABLE ItemDefinition 
(
    ID INT NOT NULL,
    ProductName VARCHAR(50),
    PaymentPlan VARCHAR(50),
    RenewalDate DATETIME,
    UnitRate NUMERIC(18, 0)
);

INSERT INTO ItemDefinition 
VALUES (1, 'ABC', 'Yearly', '2019-12-01', 276),
       (1, 'XYZ', 'Monthly', '2019-08-15', 17);

我写的问题是

SELECT 
    ProductName, SUM(UnitRate) Rate,
    DATENAME(MONTH , DATEADD(MONTH , MONTH(RenewalDate)-1 , '1900-01-01')) RenewalMonth
FROM
    ItemDefinition 
WHERE 
    MONTH(RenewalDate) IS NOT NULL
    AND RenewalDate BETWEEN @dtStart AND @dtEnd
GROUP BY 
    ProductName, MONTH(RenewalDate)
ORDER BY 
    MONTH(RenewalDate)

1 回复 | 直到 6 年前

gotqn user3521065 6 年前

可能是这样的:

DECLARE @DateBeg DATE = '2019-01-01'
       ,@DateEnd DAte = '2020-12-01';


WITH Ranges AS
(
    SELECT *
          ,@DateBeg AS [DateBeg]
          ,@DateEnd AS [DateEnd]
    FROM ItemDefinition DS
)
SELECT *
      ,DATENAME(MONTH ,ISNULL([GeneratedDate], [RenewalDate])) AS RenewalMonth
FROM Ranges
OUTER APPLY
(
    SELECT DATEADD(MONTH, [number], [DateBeg])
    FROM 
    (
        select number 
        from master.dbo.spt_values
        where [type] = 'P'
    ) numbers
    WHERE DATEADD(MONTH, [number], [DateBeg]) < [DateEnd]
        AND [PaymentPlan] = 'Monthly'
) AutoDates ([GeneratedDate]);

DateEnd 参数,您将看到如何生成更少的月份。

start 和 end 每行的日期,并根据它生成月份。

WITH Ranges AS
(
    SELECT *
          ,@DateBeg AS [DateBeg]
          ,@DateEnd AS [DateEnd]
    FROM ItemDefinition DS
)
SELECT *
      ,DATENAME(MONTH ,ISNULL([GeneratedDate], [RenewalDate])) AS RenewalMonth
      ,IIF([PaymentPlan] = 'Monthly', [UnitRate], IIF(CONVERT(VARCHAR(7), [RenewalDate], 121) = CONVERT(VARCHAR(7), [GeneratedDate], 121), [UnitRate], NULL))
FROM Ranges
OUTER APPLY  
(
    SELECT DATEADD(MONTH, [number], [DateBeg])
    FROM 
    (
        select number 
        from master.dbo.spt_values
        where [type] = 'P'
    ) numbers
    WHERE DATEADD(MONTH, [number], [DateBeg]) < [DateEnd]
) AutoDates ([GeneratedDate]);

DECLARE @DateBeg DATE = '2019-01-01'
       ,@DateEnd DAte = '2020-12-01';


WITH Ranges AS
(
    SELECT *
          ,@DateBeg AS [DateBeg]
          ,@DateEnd AS [DateEnd]
    FROM ItemDefinition DS
)
SELECT *
      ,DATENAME(MONTH ,ISNULL([GeneratedDate], [RenewalDate])) AS RenewalMonth
      ,IIF([PaymentPlan] = 'Monthly', [UnitRate], IIF([number] = 0, [UnitRate], NULL))
FROM Ranges
OUTER APPLY  
(
    SELECT DATEADD(MONTH, [number], [DateBeg])
          ,[number]
    FROM 
    (
        select number 
        from master.dbo.spt_values
        where [type] = 'P'
    ) numbers
    WHERE DATEADD(MONTH, [number], [DateBeg]) < [DateEnd]
) AutoDates ([GeneratedDate], [number]);

ben92 6 年前

我的建议是引入一个额外的表,它将有一个记录的年度计划和12个记录的每月计划。例如:

create table PaymentPlanInterval(
    PaymentPlan VARCHAR(50),
    Interval varchar(50)
)

也许这张表可能包含2条半年付款计划记录和4条季度计划记录。

您的paymentinterval应该与您的paymentinterval连接。你好。