How Can I Add Four Calculated Values To A Result Set From A Stored Proc (tsql)?

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

Solution 1:

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

Solution 2:

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@CYearINT=2016;
DECLARE@CmonthINT=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
          ,CASEWHEN mups2.ProjectedSales =0THEN1ELSE mups2.ProjectedSales /12*@CmonthENDAS YTDBudgetPerc
          ,COALESCE(SUM(rms2.MonthlySales), 0) AS YTDSales
      FROM ReportingMonthlySales AS rms
      LEFTOUTERJOIN ReportingMonthlySales AS rms2
              ON rms2.unit = rms.unit
             AND rms2.cyear = rms.cyear
             AND rms2.cmonth = rms.cmonth
             AND rms2.cmonth <=@CmonthLEFTOUTERJOIN MasterUnitsProjSales AS mups
              ON mups.unit = rms.unit
             AND mups.cyear = rms.cyear
      LEFTOUTERJOIN MasterUnitsProjSales AS mups2
              ON mups2.unit = rms.unit
             AND mups2.cyear = rms.cyear
     WHERE rms.cyear =@CmonthAND rms.cyear =@CyearGROUPBY 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*@CmonthAS YTDProjSales
      ,mdd.YTDBudgetPerc
  INTO #CombinedYears2
  FROM MasterUnitsProjSales AS mups
 INNERJOIN myDerivedData AS mdd
         ON mdd.unit = mups.unit
        AND mdd.cyear = mups.cyear
        AND mdd.cmonth = mups.cmonth
 WHERE mups.Cyear =@CYearGROUPBY mups.Unit, mups.CSDirector, mups.[Category], mups.[Segment], mdd.NumUnits, mdd.MonthlySales, mdd.YTDSales, mdd.YTDBudgetPerc
 ORDERBY mups.NewBiz, mups.Unit;

SELECT cy.*, 
   rms.MonthlySales newnew,
   rms.MonthlySales NewAssumed,
   rms.MonthlySales ExistingExisting,
   rms.MonthlySales ExistingOrganic
  FROM #CombinedYears2 AS CY 
  LEFTOUTERJOIN ReportingMonthlySales AS rms
          ON rms.Unit = cy.Unit
  INNERJOIN 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';

Solution 3:

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

Original stored procedure:

createprocedure myproc asbeginselect1as id unionallselect2;
end
GO

"Extending" stored procedure:

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

    -- Add more columns:altertable #temp add double_id int;

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

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

Solution 4:

in Sql Server 2012,

CREATEprocedure myproc @orderidintasbeginselect1as id unionallselect@orderid;
end
GO

EXEC .myproc @orderid=43671WITHRESULT SETS
(
  (
    id        INTNOTNULL

  )
);