How can I add four calculated values to a result s

问题:

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:

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).

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

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, 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, which has a MonthlySales (Money) field for each Unit/MemberNo, and Year and Month data.

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.

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.

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

("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.

UPDATE

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)

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

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'

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.

回答1:

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.

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';

回答2:

I think this should work... You have way too many unnessary joins.

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

回答3:

If your original stored procedure does not already execute an INSERT ... EXEC statement, a very simple approach is the following:

Original stored procedure:

create procedure myproc as
begin
    select 1 as id union all select 2;
end
GO

"Extending" stored procedure:

create proc myproc2 as
begin

    create table #temp (id int);
    -- Get the results of the original s.p.
    insert #temp exec myproc;

    -- Add more columns:
    alter table #temp add double_id int;

    -- Populate the new columns
    update #temp set double_id = 2*id;

    -- Return the extended data set
    select * from #temp;
end
GO

回答4:

in Sql Server 2012,

CREATE procedure myproc @orderid int
as
begin
    select 1 as id union all select @orderid;
end
GO

EXEC .myproc @orderid = 43671
WITH RESULT SETS
(
  (
    id        INT      NOT NULL

  )
);