时间戳

首页 > 解决方案 > 案例 当带回两行而不是一行时

sql - 案例 当带回两行而不是一行时

问题描述

大家好，我有一些我正在处理的代码，我需要做的事情我确信相对容易，但是我在 Case 声明中苦苦挣扎

我需要做什么; 将数据集带回 SSRS 中的表中，按年份、使用月份和计费层划分

问题; 计费层是 0,1,4,5 0 是历史数据，我需要将其显示为 1，然后将 0 和原始层 1 加在一起

到目前为止我做了什么；

DECLARE @UsageMonth DATE = '20170401'
DECLARE @YEAR INT = '2017'
DECLARE @BillingTier2 INT = 1;

SELECT  YEAR(UsageMonth) AS [Year],
        UsageMonth,
        --Billingtier,
        CASE WHEN billingtier = 0 THEN 1 ELSE billingtier END AS BillingTier2,
        SuppliesBilled = COUNT(DISTINCT c.CoreMPAN),
        CreditKwH = SUM(CASE WHEN c.IsCredit = 1 THEN c.ConsumptionUnits ELSE 0 END),
        DebitKwH = SUM(CASE WHEN c.IsCredit = 0 THEN c.ConsumptionUnits ELSE 0 END),
        TotalKwh = SUM(c.ConsumptionUnits),
        CreditAmount = SUM(CASE WHEN c.IsCredit = 1 THEN c.ConsumptionAmount ELSE 0 END),
        DebitAmount = SUM(CASE WHEN c.IsCredit = 0 THEN c.ConsumptionAmount ELSE 0 END),
        TotalAmount= SUM(c.ConsumptionAmount),
        UnitRate = SUM(c.ConsumptionAmount) / NULLIF(SUM(c.ConsumptionUnits), 0),
        StandingCharge = SUM(c.StandingChargeAmount),
        CCL = SUM(c.CCLAmount)

FROM    dbo.table1 c
WHERE YEAR(UsageMonth) = @YEAR
AND UsageMonth = @UsageMonth
GROUP BY YEAR(UsageMonth), UsageMonth, Billingtier, CASE WHEN billingtier = 0 THEN 1 ELSE billingtier END

我在这里遇到的问题是，这会将以前为 0 的所有列都带回一行，以及计费层实际上为 1 的另一行，因此给了我两行“1”，它应该是一个计费层 1 和 0 加在一起

预期数据

Year    UsageMonth  BillingTier2    SuppliesBilled  CreditKwH   DebitKwH    TotalKwh    CreditAmount    DebitAmount TotalAmount UnitRate    StandingCharge  CCL
2017    01/04/2017  1   3296    -4348786.008    7219573.706 2870787.699 -523979.1005    866478.4812 342499.3807 0.238118    22394.5114  10685.7546
2017    01/04/2017  4   27  -59415.7663 65843.4963  6427.73 -9269.5841  10359.9353  1090.3512   0.169632    118.5181    21.3836
2017    01/04/2017  5   515 -524035.4192    943647.2675 419611.8483 -105349.1449    189478.524  84129.3791  0.200493    6581.5  1567.7762

数据接收

Year    UsageMonth  BillingTier2    SuppliesBilled  CreditKwH   DebitKwH    TotalKwh    CreditAmount    DebitAmount TotalAmount UnitRate    StandingCharge  CCL
2017    01/04/2017  1   3124    -4348786.008    7219573.706 2870787.699 -523979.1005    866478.4812 342499.3807 0.119305    22394.5114  10685.7546
2017    01/04/2017  4   27  -59415.7663 65843.4963  6427.73 -9269.5841  10359.9353  1090.3512   0.169632    118.5181    21.3836
2017    01/04/2017  5   515 -524035.4192    943647.2675 419611.8483 -105349.1449    189478.524  84129.3791  0.200493    6581.5  1567.7762

正如您所看到的，所有的总和和数据都匹配，除非在计数时，Case 语句中缺少最初计费为 0 (172) 的耗材

这里的任何帮助将不胜感激

干杯

标签： sqlgroup-bysql-server-2016

解决方案

当您在 COUNT 函数中使用 DISTINCT 时，如下所示 -

 SuppliesBilled = COUNT(DISTINCT c.CoreMPAN)

如果有多个具有相同值的 c.CoreMPAN 可用，它会减少 COUNT 的数量。锄头你知道的。如果这不是预期的，您可以尝试从 COUNT 中删除 DISTINCT 命令，如下所示 -

 SuppliesBilled = COUNT(c.CoreMPAN)

推荐阅读