如何计算列不同值的差异百分比

发布于 2024-12-10 15:45:25 字数 535 浏览 0 评论 0原文

在此处输入图像描述有一列 - 当前产品 - 根据库存列的值有两个值。这意味着当库存为空时，它被称为“当前产品”，当它有一定价值时，它被称为“匹配”。像这样......

==================================================================
Current Product       Stocked    AnnualSavings     Sodium
 Current product                                    450.00                    
  Match               yes          1234.00          432.00

现在SSRS 2008 R2报告中有一个钠文本框为空。当用户输入 10 那么我们必须检查钠的匹配值是否比当前产品低 10% 且等于当前产品的匹配值。

我怎样才能做到这一点...？

原文

enter image description here There is a column - Current Product - which has two values according to the value of stocked column. That means when stocked is empty then it is called 'current product' and when it has some value then it is called 'Match'. like this.....

==================================================================
Current Product       Stocked    AnnualSavings     Sodium
 Current product                                    450.00                    
  Match               yes          1234.00          432.00

Now there is a sodium text box in SSRS 2008 R2 report with a null. When a user enters 10
then we have to check match value of sodium is 10% less and equal to that of current product.

How can i do this...?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

沉溺在你眼里的海 2024-12-17 15:45:25

如果我正确理解您的要求，下面的查询将作为 SSRS 数据集运行。我想你想从表中选择任何行，其中 Stocked 字段不为空，并且钠在“当前产品”的特定范围内。该范围由用户指定为报告的参数。

在数据集查询中过滤它比在报告本身中过滤要容易得多，因为它取决于记录之间的值比较。

我认为如果我错过了这一点，在示例表中添加更多行会有所帮助。

;
WITH
(
SELECT
   [Current Product] ,
   [Stocked] ,
   [OriginalProduct] ,
   [Sodium(Mg)]
FROM
   #TempConversion7
WHERE
   MemberPurchaseDataId IN ( SELECT DISTINCT
                              MemberPurchaseDataId
                             FROM
                              #TempConversion7
                             WHERE
                              SrNo > 1 )
                              ) AS ListCTE

SELECT
    *,
    CASE WHEN CurrentProd.[Sodium(Mg)]  >=
    ( MatchProd.[Sodium(Mg)] *(1.0 - ( @MatchValueParameter * 0.01)))
    AND CurrentProd.[Sodium(Mg)]  <=
    ( MatchProd.[Sodium(Mg)] *(1.0 + ( @MatchValueParameter * 0.01)))
    THEN
    1
    ELSE 0
    END
    AS WithinPercentRange
FROM
ListCTE AS CurrentProd
FULL OUTER JOIN
ListCTE AS MatchProd
ON CurrentProd.OriginalProduct = MatchProd.OriginalProduct
AND CurrentProd.[Current Product] = 'Current Product'
AND MatchProd.[Current Product] = 'Match'

If I understand your requirements correctly the query below would work as an SSRS dataset. I think you want to select any rows from the table that have the Stocked field not null, and the Sodium within a certain range of "Current Product" The range is specified by the user as a parameter to the report.

It will be much easier to filter this in the query of the data set then in the report itself, since it depends on comparing values between records.

I think adding a few more rows to your example table would help if I missed this.

;
WITH
(
SELECT
   [Current Product] ,
   [Stocked] ,
   [OriginalProduct] ,
   [Sodium(Mg)]
FROM
   #TempConversion7
WHERE
   MemberPurchaseDataId IN ( SELECT DISTINCT
                              MemberPurchaseDataId
                             FROM
                              #TempConversion7
                             WHERE
                              SrNo > 1 )
                              ) AS ListCTE

SELECT
    *,
    CASE WHEN CurrentProd.[Sodium(Mg)]  >=
    ( MatchProd.[Sodium(Mg)] *(1.0 - ( @MatchValueParameter * 0.01)))
    AND CurrentProd.[Sodium(Mg)]  <=
    ( MatchProd.[Sodium(Mg)] *(1.0 + ( @MatchValueParameter * 0.01)))
    THEN
    1
    ELSE 0
    END
    AS WithinPercentRange
FROM
ListCTE AS CurrentProd
FULL OUTER JOIN
ListCTE AS MatchProd
ON CurrentProd.OriginalProduct = MatchProd.OriginalProduct
AND CurrentProd.[Current Product] = 'Current Product'
AND MatchProd.[Current Product] = 'Match'

回复收藏 0 原文

~没有更多了~