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