基于会计年度PostDate生成会计月度列的SQL实现求助

问题说明 #

我是SQL新手，能编写基础查询，当前需求是从AR Ledger中提取会计年度PostDate对应的服务信息，新增一列并根据PostDate的日期区间填充对应的会计年度月份编号（示例：2022年1月对应7，2月对应8，以此类推至12个月）。以下是我已编写的查询语句（要求不使用AS重命名原表和列），求实现新增列并填充对应编号的方法。

现有查询语句 #

SELECT 
    dbo.ARLedger.RecordDeleted, 
    dbo.ARLedger.LedgerType, 
    dbo.GlobalCodes.CodeName, 
    Sum(dbo.ARLedger.Amount) AS SumOfAmount, 
    dbo.ARLedger.PostedDate, 
    dbo.ARLedger.CoveragePlanId, 
    dbo.ARLedger.ClientId, 
    Count(dbo.Charges.BillingCode) AS CountOfBillingCode
FROM 
    ((dbo.ARLedger INNER JOIN dbo.Charges ON dbo.ARLedger.ChargeId = dbo.Charges.ChargeId) 
    INNER JOIN dbo.Services ON dbo.Charges.ServiceId = dbo.Services.ServiceId) 
    INNER JOIN dbo.GlobalCodes ON dbo.ARLedger.AdjustmentCode = dbo.GlobalCodes.GlobalCodeId
WHERE 
    (((dbo.ARLedger.PostedDate) Between '7/1/2021' And '6/30/2022'))
GROUP BY 
    dbo.ARLedger.RecordDeleted, 
    dbo.ARLedger.LedgerType, 
    dbo.GlobalCodes.CodeName, 
    dbo.ARLedger.PostedDate, 
    dbo.ARLedger.CoveragePlanId, 
    dbo.ARLedger.ClientId, 
    dbo.ARLedger.DateOfService, 
    dbo.Charges.RecordDeleted, 
    dbo.Services.RecordDeleted
HAVING 
    (((dbo.ARLedger.RecordDeleted) Is Null) 
    AND ((dbo.ARLedger.LedgerType)=4203) 
    AND ((dbo.Charges.RecordDeleted) Is Null) 
    AND ((dbo.Services.RecordDeleted) Is Null));

解决方案 #

根据你的会计年度规则（7月至次年6月为一个会计年度，对应编号1-12），可以通过以下两种方式新增目标列：

方法一：使用CASE语句判断月份区间 #

在SELECT列表中添加CASE逻辑，直接根据PostedDate的自然月份计算会计年度编号：

SELECT 
    dbo.ARLedger.RecordDeleted, 
    dbo.ARLedger.LedgerType, 
    dbo.GlobalCodes.CodeName, 
    Sum(dbo.ARLedger.Amount) AS SumOfAmount, 
    dbo.ARLedger.PostedDate, 
    dbo.ARLedger.CoveragePlanId, 
    dbo.ARLedger.ClientId, 
    Count(dbo.Charges.BillingCode) AS CountOfBillingCode,
    CASE
        WHEN MONTH(dbo.ARLedger.PostedDate) >=7 THEN MONTH(dbo.ARLedger.PostedDate) -6
        ELSE MONTH(dbo.ARLedger.PostedDate) +6
    END AS 会计年度月份编号
FROM 
    ((dbo.ARLedger INNER JOIN dbo.Charges ON dbo.ARLedger.ChargeId = dbo.Charges.ChargeId) 
    INNER JOIN dbo.Services ON dbo.Charges.ServiceId = dbo.Services.ServiceId) 
    INNER JOIN dbo.GlobalCodes ON dbo.ARLedger.AdjustmentCode = dbo.GlobalCodes.GlobalCodeId
WHERE 
    (((dbo.ARLedger.PostedDate) Between '7/1/2021' And '6/30/2022'))
GROUP BY 
    dbo.ARLedger.RecordDeleted, 
    dbo.ARLedger.LedgerType, 
    dbo.GlobalCodes.CodeName, 
    dbo.ARLedger.PostedDate, 
    dbo.ARLedger.CoveragePlanId, 
    dbo.ARLedger.ClientId, 
    dbo.ARLedger.DateOfService, 
    dbo.Charges.RecordDeleted, 
    dbo.Services.RecordDeleted,
    MONTH(dbo.ARLedger.PostedDate)
HAVING 
    (((dbo.ARLedger.RecordDeleted) Is Null) 
    AND ((dbo.ARLedger.LedgerType)=4203) 
    AND ((dbo.Charges.RecordDeleted) Is Null) 
    AND ((dbo.Services.RecordDeleted) Is Null));

注意：因为新增列是非聚合列，需要将MONTH(dbo.ARLedger.PostedDate)加入GROUP BY子句，保证分组逻辑合法。

方法二：使用DATEADD+DATEPART简化计算 #

通过将PostedDate往前推6个月，直接取新日期的月份作为会计年度编号，逻辑更简洁：

SELECT 
    dbo.ARLedger.RecordDeleted, 
    dbo.ARLedger.LedgerType, 
    dbo.GlobalCodes.CodeName, 
    Sum(dbo.ARLedger.Amount) AS SumOfAmount, 
    dbo.ARLedger.PostedDate, 
    dbo.ARLedger.CoveragePlanId, 
    dbo.ARLedger.ClientId, 
    Count(dbo.Charges.BillingCode) AS CountOfBillingCode,
    DATEPART(MONTH, DATEADD(MONTH, -6, dbo.ARLedger.PostedDate)) AS 会计年度月份编号
FROM 
    ((dbo.ARLedger INNER JOIN dbo.Charges ON dbo.ARLedger.ChargeId = dbo.Charges.ChargeId) 
    INNER JOIN dbo.Services ON dbo.Charges.ServiceId = dbo.Services.ServiceId) 
    INNER JOIN dbo.GlobalCodes ON dbo.ARLedger.AdjustmentCode = dbo.GlobalCodes.GlobalCodeId
WHERE 
    (((dbo.ARLedger.PostedDate) Between '7/1/2021' And '6/30/2022'))
GROUP BY 
    dbo.ARLedger.RecordDeleted, 
    dbo.ARLedger.LedgerType, 
    dbo.GlobalCodes.CodeName, 
    dbo.ARLedger.PostedDate, 
    dbo.ARLedger.CoveragePlanId, 
    dbo.ARLedger.ClientId, 
    dbo.ARLedger.DateOfService, 
    dbo.Charges.RecordDeleted, 
    dbo.Services.RecordDeleted,
    DATEPART(MONTH, DATEADD(MONTH, -6, dbo.ARLedger.PostedDate))
HAVING 
    (((dbo.ARLedger.RecordDeleted) Is Null) 
    AND ((dbo.ARLedger.LedgerType)=4203) 
    AND ((dbo.Charges.RecordDeleted) Is Null) 
    AND ((dbo.Services.RecordDeleted) Is Null));

逻辑说明：将PostedDate减6个月后，原会计年度1月（自然年7月）变为自然年1月，对应月份1；原会计年度7月（自然年1月）变为自然年7月，对应月份7，完全匹配需求。

内容的提问来源于stack exchange，提问作者Diane Null