How can I add four calculated values to a result set from a Stored Proc (TSQL)?

B. Clay Shannon

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.

Patrik Birgersson

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Java

Hsqldb - Return a Result Set from Stored Proc

From Dev

How can I set CosmosDBTrigger parameters with values from the app values stored in the Function App?

From Dev

How do I Populate a Result View Column with Value from Stored Proc in SQL?

From Java

How can I set values to specific attributes stored inside of an ArrayList?

From Dev

How can I add values to an array stored in a map?

From Dev

How can I add new row to database with values stored in ObservableCollection?

From Dev

How can I add a "custom" row to the top of a select result set?

From Dev

How to return multiple values from stored procedure within tsql query

From Dev

How can I add already calculated standard error values to each bar in a bar plot (ggplot)?

From Dev

TSQL How to calculate exam result based on values from multiple rows

From Dev

How can I print the result from this code with four places after the decimal point?

From Mysql

How can I SELECT a row with MAX(value) from a derived table where the values are all calculated sums?

From Dev

Selecting unique values from the result set using MySQL stored procedures

From Dev

How can I merge a result set from a matrix table?

From Dev

How can I set path of vim from the shell command result

From Dev

Getting a bool or int result from a Stored Proc

From Dev

Result from Stored proc different to SSMS Query

From Dev

How can I determine the Stored Proc used on an SSRS page?

From Dev

How do I add the values calculated in a for loop in a array?

From Java

sql server stored proc, can't get result set back in java

From Dev

SQL - How to add a calculated column based on values from other columns

From Dev

SSIS - Insert result set from stored proc into table on another DB server

From Dev

How do I set column values to null where they are 'null' in TSQL?

From Dev

how can i calculated values after Worksheet.Calculate()?

From Dev

How can I add a calculated column to a Pandas dataframe?

From Dev

How can I add a property calculated by a JavaScript extension with C#?

From Dev

How can I add a calculated column with different rows to a dataframe?

From Dev

How can I add a custom fee in woocommerce that is calculated after tax

From Dev

How can I get a resultset and a RAISERROR result from a SQL Server Stored Procedure when reading with Dapper?

Related Related

  1. 1

    Hsqldb - Return a Result Set from Stored Proc

  2. 2

    How can I set CosmosDBTrigger parameters with values from the app values stored in the Function App?

  3. 3

    How do I Populate a Result View Column with Value from Stored Proc in SQL?

  4. 4

    How can I set values to specific attributes stored inside of an ArrayList?

  5. 5

    How can I add values to an array stored in a map?

  6. 6

    How can I add new row to database with values stored in ObservableCollection?

  7. 7

    How can I add a "custom" row to the top of a select result set?

  8. 8

    How to return multiple values from stored procedure within tsql query

  9. 9

    How can I add already calculated standard error values to each bar in a bar plot (ggplot)?

  10. 10

    TSQL How to calculate exam result based on values from multiple rows

  11. 11

    How can I print the result from this code with four places after the decimal point?

  12. 12

    How can I SELECT a row with MAX(value) from a derived table where the values are all calculated sums?

  13. 13

    Selecting unique values from the result set using MySQL stored procedures

  14. 14

    How can I merge a result set from a matrix table?

  15. 15

    How can I set path of vim from the shell command result

  16. 16

    Getting a bool or int result from a Stored Proc

  17. 17

    Result from Stored proc different to SSMS Query

  18. 18

    How can I determine the Stored Proc used on an SSRS page?

  19. 19

    How do I add the values calculated in a for loop in a array?

  20. 20

    sql server stored proc, can't get result set back in java

  21. 21

    SQL - How to add a calculated column based on values from other columns

  22. 22

    SSIS - Insert result set from stored proc into table on another DB server

  23. 23

    How do I set column values to null where they are 'null' in TSQL?

  24. 24

    how can i calculated values after Worksheet.Calculate()?

  25. 25

    How can I add a calculated column to a Pandas dataframe?

  26. 26

    How can I add a property calculated by a JavaScript extension with C#?

  27. 27

    How can I add a calculated column with different rows to a dataframe?

  28. 28

    How can I add a custom fee in woocommerce that is calculated after tax

  29. 29

    How can I get a resultset and a RAISERROR result from a SQL Server Stored Procedure when reading with Dapper?

HotTag

Archive