如何从存储过程（TSQL）将四个计算值添加到结果集中？ [英] How can I add four calculated values to a result set from a Stored Proc (TSQL)?

问题描述

我需要扩展现有的存储过程（即，基于旧的存储过程创建一个新的SP），以包括一些附加的计算数据。简而言之，该报告显示MonthlySales，并且需要另外四列来填充从SP结果生成的报告：

I need to extend an existing Stored Procedure (that is, create a new SP based on the legacy one) to include some additional, calculated data. Four additional columns are needed to feed the report that is generated from the results of the SP:

，其货币值基于该行中指定的单位的Category.Subcategory值（每个Unit在结果集和报告中都有其自己的行）。

In a nutshell, the report shows MonthlySales, and the four additional columns are populated with a monetary value based on a Category.Subcategory value for the "Unit" specified in that row (each Unit has its own row in the result set and report).

新列分为四个类别：New.New，New.Assepted，Existing.Existing和Existing.Organic

The new columns are the four categories: New.New, New.Assumed, Existing.Existing, and Existing.Organic

因此，结果集中的每一行都包含单个单位，并且该单位属于这些类别之一。报告的时间段的子类别值（IOW，这四个袋子中只有一个在每一行都有一个值）。

So each row in the result set contains data for a single Unit, and that Unit belongs in one of those Category.Subcategory values for the time period being reported (IOW, only one of these four bags will have a value on each row).

三个表涉及检索此数据：

Three tables are involved in retrieving this data:

MasterUnitProjSales ，每个单元都有一个 NewBiz标志（如果为true） ，则属于新两个值家族；否则，属于现有两个家族价值观因此，可以通过此值确定Category.Subcategory值的Category部分。）

MasterUnitProjSales, which has a "NewBiz" flag for each Unit (if true, it is of the "New" family of two values; otherwise, it is of the "Existing" family of two values; thus, the Category part of the Category.Subcategory value can be determined by this valuye).

ReportingMonthlySales ，它具有MonthlySales（货币）每个单位/会员编号以及年和月数据。

ReportingMonthlySales, which has a MonthlySales (Money) field for each Unit/MemberNo, and Year and Month data.

CustomerCategoryLog ，其中包含单位/会员编号对以及类别和Subcategory字段，以及BeginDate和EndDate字段，它们记录了在Subcategory中的类别。一个单位/成员的类别在Begin / End时间范围内。

CustomerCategoryLog, which has the Unit/MemberNo pairs, as well as the Category and Subcategory fields, as well as BeginDate and EndDate fields, that record in what Subcategory.Category a Unit/Member was during the Begin/End time frame.

传统SP的工作原理是将一年的数据存储到临时表中，然后将上一年的数据存储到第二个临时表中，然后将它们组合起来并返回。

The way the legacy SP works is it stores data for one year into a temp table, then stores data for the previous year into a second temp table, then combines them, and returns that.

伴随着这种额外的皱纹，我的想法（伪SQL，我不是[T] -SQL头）是：

With this additional wrinkle, my idea (pseudo-SQL, I'm no [T]-SQL-head) is:

（ CombinedYears是一个表，是前两个临时表（ CurrentYear和 PriorYear）的合并）

("CombinedYears" is a table that is an amalgamation of the first two temp tables, "CurrentYear" and "PriorYear")

Select * from #CombinedYears CY,
NewNew = select MonthlySales from ReportingMonthlySales RMS where Category = 'New' and Subcategory = 'New' and
RMS.Unit = CY.Unit
left join CustomerCategoryLog CCL on RMS.Unit = CCL.Unit,
NewAssumed = select MonthlySales from ReportingMonthlySales RMS where Category = 'New' and Subcategory = 'Assumed' and
RMS.Unit = CY.Unit
left join CustomerCategoryLog CCL on RMS.Unit = CCL.Unit,
ExistingExisting = select MonthlySales from ReportingMonthlySales RMS where Category = 'Existing' and Subcategory = 'Existing' and
RMS.Unit = CY.Unit
left join CustomerCategoryLog CCL on RMS.Unit = CCL.Unit,
ExistingOrganic = select MonthlySales from ReportingMonthlySales RMS where Category = 'Existing' and Subcategory = 'Organic' and
RMS.Unit = CY.Unit
left join CustomerCategoryLog CCL on RMS.Unit = CCL.Unit

我知道这是错误的，尴尬的和笨拙的，但也许它可以帮助您理解表格之间的连接

I know that's wrong, awkward, and kludgy, but maybe it helps you to understand the connections between the tables, etc.

注意：该问题与我的问题，但这可能对提供上下文或背景信息没有帮助。

NOTE: This question is partially related to my question here but that may be of no assistance as far as providing context or background info.

我尝试了Charles Bretana的想法，但必须对其进行调整才能使其在LINQPad中编译。关于表的确切性质，我可能误导了您。我将在下面包括它们，然后显示我正在LINQPad中尝试的更改后的查询。

I tried Charles Bretana's idea, but had to tweak it to get it to "compile" in LINQPad. I may have misled you as to the exact nature of the tables. I will include them below, and then show the altered query that I'm trying out there in LINQPad.

CustomerCategoryLog
-------------------
MemberNo (VarChar)
Unit (VarChar)
Custno (VarChar)
Category (VarChar)
Subcategory (VarChar)
BeginDate (DateTime)
EndDate (DateTime)
ChangedBy (VarChar)
ChangedOn (DateTime)

MasterUnitProjSales
-------------------
Unit (VarChar)
CYear (Int)
CSDirector (VarChar)
ProjectedSales (Money)
NewBiz (Int)
Category (VarChar) <= this is not directly connected to the "Category" I need, and should be ignored
Segment (VarChar)

ReportingMonthlySales
---------------------
AutoID (Int)
Unit (VarChar)
MemberNo (VarChar)
NumUnits (Int)
MonthlySales (Money)
CYear (Int)
Cmonth (Int)
CreateDate (DateTime)

以下内容（由我修改，但基于Bretana' s）试图在LINQPad中生成一些数据，但要永远：

The following (modified by me, but based on Bretana's) is trying to generate some data in LINQPad, but taking "forever":

DECLARE @CYear  INT
SET @CYear = 2016

DECLARE @Cmonth INT
SET @Cmonth = 4

Select  CSDirector,
        Category,
        Segment,
        r1.unit,
        NumUnits=isnull((Select sum(NumUnits) from ReportingMonthlySales where unit=r1.unit and cyear=r1.cyear and 
        cmonth = @Cmonth),0),    
        MonthSales=isnull((Select sum(MonthlySales) from ReportingMonthlySales where unit=r1.unit and cyear=r1.cyear 
and cmonth = @Cmonth),0.00), 
        YTDSales = (Select sum(MonthlySales) From  ReportingMonthlySales where unit=r1.unit and cyear=r1.cyear and 
cmonth <= @Cmonth),
        ProjSales =  (Select ProjectedSales from MasterUnitsProjSales where UNit = r1.Unit and Cyear=r1.cyear),
        YTDProjSales = (Select ProjectedSales from MasterUnitsProjSales where UNit = r1.Unit and Cyear=r1.cyear) / 12 
* @Cmonth,
        YTDBudgetPerc = (Select sum(MonthlySales) From  ReportingMonthlySales where unit=r1.unit and cyear=r1.cyear 
and cmonth <= @Cmonth) / 
            case when (Select ProjectedSales from MasterUnitsProjSales where UNit = r1.Unit and Cyear=r1.cyear) = 
0 then 1 else ((Select ProjectedSales from MasterUnitsProjSales where UNit = r1.Unit and Cyear=r1.cyear) / 12 * @Cmonth) end
into #CombinedYears2
From    MasterUnitsProjSales r1 
where r1.Cyear=@CYear
order by r1.NewBiz,r1.Unit

Select cy.*, 
   rms.MonthlySales newnew,
   rms.MonthlySales NewAssumed,
   rms.MonthlySales ExistingExisting,
   rms.MonthlySales ExistingOrganic
from #CombinedYears2 CY 
   left join ReportingMonthlySales rms
     on rms.Unit = cy.Unit
   join CustomerCategoryLog n
     on n.Category = 'New' 
        and n.Subcategory = 'New' 
        and n.Unit = cy.Unit
   left join CustomerCategoryLog a
      on a.Category = 'New' 
        and a.Subcategory = 'Assumed' 
        and a.Unit = CY.Unit
   left join CustomerCategoryLog e
      on e.Category = 'Existing' 
        and e.Subcategory = 'Existing' 
        and e.Unit = CY.Unit
   left join CustomerCategoryLog o
      on o.Category = 'Existing' 
        and o.Subcategory = 'Organic' 
        and o.Unit = CY.Unit

UPDATE 2

在完成其他工作/将其搁置一旁之后，又回到它并重新进行攻击，这是我的新伪SQL：

UPDATE 2

After working on other things/setting this aside, then coming back to it and attacking it anew, this is my new pseudo-sql:

DECLARE @Unit varchar(30); 
DECLARE @Year Int;
DECLARE @Month Int;

SELECT MonthlySales
INTO #NewSales
FROM ReportingMonthlySales RMS
JOIN CustomerCategoryLog CCL ON RMS.Unit = CCL.Unit
WHERE RMS.Unit = @Unit AND RMS.CYear = @Year AND RMS.Cmonth = @Month AND CCL.Subcategory = 'New'

SELECT MonthlySales
INTO #AssumedSales
FROM ReportingMonthlySales RMS
JOIN CustomerCategoryLog CCL ON RMS.Unit = CCL.Unit
WHERE RMS.Unit = @Unit AND RMS.CYear = @Year AND RMS.Cmonth = @Month AND CCL.Subcategory = 'Assumed'

SELECT MonthlySales
INTO #ExistingSales
FROM ReportingMonthlySales RMS
JOIN CustomerCategoryLog CCL ON RMS.Unit = CCL.Unit
WHERE RMS.Unit = @Unit AND RMS.CYear = @Year AND RMS.Cmonth = @Month AND CCL.Subcategory = 'Existing'

SELECT MonthlySales
INTO #OrganicSales
FROM ReportingMonthlySales RMS
JOIN CustomerCategoryLog CCL ON RMS.Unit = CCL.Unit
WHERE RMS.Unit = @Unit AND RMS.CYear = @Year AND RMS.Cmonth = @Month AND CCL.Subcategory = 'Organic'

将这四个临时表合并为一个，并将MonthlySales放入相应的Category.Subcategory列，并将其作为要用于生成报告的结果集返回。

Combine these four temp tables into one, putting MonthlySales into the appropriate Category.Subcategory column, and return that as the result set to be used to generate the report.

推荐答案

这可能会帮助您一点点。该代码未经测试（因为我没有您的数据库;）），但我希望它可以使事情少一点混乱。

This might help you a tad further. The code is untested (as I don't have your database ;)), but I hope it makes things a little less messy.

您正在尝试加入CustomerCategoryLog表，但未引用查询中的任何其他表。因此，您无法完成连接，这很可能就是您无法编译的原因。

At the end; you're trying to join the CustomerCategoryLog table, but you don't reference any of the other tables in the query. As such, you cannot complete the join, which may very well be the reason to why you can't compile.

DECLARE @CYear INT = 2016;
DECLARE @Cmonth INT = 4;

WITH myDerivedData (unit, cyear, cmonth, NumUnits, MonthlySales, YTDBudgetPerc, YTDSales) AS (
    SELECT rms.unit
          ,rms.cyear
          ,rms.cmonth
          ,COALESCE(SUM(rms.NumUnits), 0) AS NumUnits
          ,COALESCE(SUM(rms.MonthlySales), 0) AS MonthlySales
          ,CASE WHEN mups2.ProjectedSales = 0 THEN 1
                ELSE mups2.ProjectedSales / 12 * @Cmonth
                END AS YTDBudgetPerc
          ,COALESCE(SUM(rms2.MonthlySales), 0) AS YTDSales
      FROM ReportingMonthlySales AS rms
      LEFT OUTER JOIN ReportingMonthlySales AS rms2
              ON rms2.unit = rms.unit
             AND rms2.cyear = rms.cyear
             AND rms2.cmonth = rms.cmonth
             AND rms2.cmonth <= @Cmonth
      LEFT OUTER JOIN MasterUnitsProjSales AS mups
              ON mups.unit = rms.unit
             AND mups.cyear = rms.cyear
      LEFT OUTER JOIN MasterUnitsProjSales AS mups2
              ON mups2.unit = rms.unit
             AND mups2.cyear = rms.cyear
     WHERE rms.cyear = @Cmonth
       AND rms.cyear = @Cyear
     GROUP BY rms.Unit, rms.cyear, rms.cmonth
)
SELECT mups.CSDirector,
      ,mups.[Category]
      ,mups.[Segment]
      ,mups.unit
      ,mdd.NumUnits
      ,mdd.MonthlySales
      ,mdd.YTDSales
      ,SUM(mups.ProjectedSales) AS ProjSales
      ,SUM(mups.ProjectedSales) / 12 * @Cmonth AS YTDProjSales
      ,mdd.YTDBudgetPerc
  INTO #CombinedYears2
  FROM MasterUnitsProjSales AS mups
 INNER JOIN myDerivedData AS mdd
         ON mdd.unit = mups.unit
        AND mdd.cyear = mups.cyear
        AND mdd.cmonth = mups.cmonth
 WHERE mups.Cyear = @CYear
 GROUP BY mups.Unit, mups.CSDirector, mups.[Category], mups.[Segment], mdd.NumUnits, mdd.MonthlySales, mdd.YTDSales, mdd.YTDBudgetPerc
 ORDER BY mups.NewBiz, mups.Unit;

SELECT cy.*, 
   rms.MonthlySales newnew,
   rms.MonthlySales NewAssumed,
   rms.MonthlySales ExistingExisting,
   rms.MonthlySales ExistingOrganic
  FROM #CombinedYears2 AS CY 
  LEFT OUTER JOIN ReportingMonthlySales AS rms
          ON rms.Unit = cy.Unit
  INNER JOIN CustomerCategoryLog AS n  /* What is this? You can't join a table like this, as it's not joined with any other table */
          ON n.Category = 'New';

这篇关于如何从存储过程（TSQL）将四个计算值添加到结果集中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！