December 15, 2016 at 2:41 am
I have a SQL view which vertically sums a few columns containing cash amounts. A matching set of columns contain flags indicating whether or not the cash column should be included in another horizontal sum (row total) which is then also vertically summed. This is called the 'QualifyingSpend'. The flag columns contain -1 if a cash amount is to be included, 0 if not. If the columns are A,B,C and the flag columns are x,y,z then the QualifyingSpend is A*-x+B*-y+C*-z. When I open the view the overall totals are correct, but the Qualifying spend is wrong: one row in the source table has been missed from the total. But it is included in the overall total! Full SQL below.
SELECT dbo.RebateAgreements.AgreementSerial, [highlight="#ffff11"]SUM(- (((((A.[Plastics Turnover value] * ISNULL(dbo.RebateAgreements.Plastics, 0)
- A.[Heating Spares Turnover Value] * ISNULL(dbo.RebateAgreements.HeatingSpares, 0)) - A.[Boiler Turnover Value] * ISNULL(dbo.RebateAgreements.Boilers, 0))
- A.[Copper Turnover Value] * - ISNULL(dbo.RebateAgreements.Copper, 0)) - A.[Other Products Turnover Value] * ISNULL(dbo.RebateAgreements.AllOtherProducts, 0))
- A.[Radiators Turnover value] * ISNULL(dbo.RebateAgreements.Radiators, 0)) - A.[Renewables Turnover value] * ISNULL(dbo.RebateAgreements.Renewables, 0)) AS QualifyingSpend[/highlight],
SUM(A.[Boiler Turnover Value]) AS BoilerTurnover, SUM(A.[Boilers Margin £]) AS BoilerMargin, SUM(A.[Copper Turnover Value]) AS CopperTurnover, SUM(A.[Copper Margin £]) AS CopperMargin,
SUM(A.[Heating Spares Turnover Value]) AS HeatingTurnover, SUM(A.[Heating Spares Margin £]) AS HeatingMargin, SUM(A.[Other Products Turnover Value]) AS OtherTurnover,
SUM(A.[Other Products Margin £]) AS OtherMargin, SUM(A.[Plastics Turnover value]) AS PlasticsTurnover, SUM(A.[Plastics Margin £]) AS PlasticsMargin, SUM(A.[Radiators Turnover value])
AS RadiatorsTurnover, SUM(A.[Radiators Margin £]) AS RadiatorsMargin, SUM(A.[Renewables Turnover value]) AS RenewablesTurnover, SUM(A.[Renewables Margin £]) AS RenewablesMargin,
SUM(A.[All Turnover Value]) AS TotalTurnover, SUM((((((A.[Plastics Margin £] * - ISNULL(dbo.RebateAgreements.Plastics, 0)
- A.[Heating Spares Margin £] * ISNULL(dbo.RebateAgreements.HeatingSpares, 0)) - A.[Boilers Margin £] * ISNULL(dbo.RebateAgreements.Boilers, 0))
- A.[Copper Margin £] * - ISNULL(dbo.RebateAgreements.Copper, 0)) - A.[Other Products Margin £] * ISNULL(dbo.RebateAgreements.AllOtherProducts, 0))
- A.[Radiators Margin £] * ISNULL(dbo.RebateAgreements.Radiators, 0)) - A.[Renewables Margin £] * ISNULL(dbo.RebateAgreements.Renewables, 0)) AS QualifyingMargin, SUM(A.[All Margin £])
AS TotalMargin
FROM dbo.RebateAgreements INNER JOIN
dbo.RebateAccounts ON dbo.RebateAgreements.AgreementSerial = dbo.RebateAccounts.AgreementSerial INNER JOIN
dbo.CustomerRebateTracker01Pivot AS A ON dbo.RebateAccounts.CustomerAccount = A.CustomerAccount
WHERE (dbo.RebateAccounts.Include = - 1) AND (dbo.RebateAgreements.AgreementStatus <> N'Not required') AND (A.PostingMonth >= N'1512')
GROUP BY dbo.RebateAgreements.AgreementSerial
HAVING (dbo.RebateAgreements.AgreementSerial = 2600)
December 15, 2016 at 3:53 am
Could it be the additional minus before the ISNULL on the copper line which is inverting the value " - A.[Copper Turnover Value][highlight="#ffff11"] * - ISNULL[/highlight](dbo.RebateAgreements.Copper, 0)) " with the SUM for qualifying spend? This is isn't present in the other elements for this sum.
December 15, 2016 at 4:10 am
Yes, that is an error (since corrected) but doesn't explain the problem. What I am seeing is this sum performed correctly:
2+3+4=9
...but when I multiply by the flags:
(2 x 1) + (3 x 1) + (4 x 1) = 5
That is, the record that supplied the 4 has somehow been dropped. By looking at the result I can always identify specific records that have been excluded when performing the second calculation. I have broken the original into two views, the first supplying all the totals, the second performing the calculation. Same result.
December 15, 2016 at 4:18 am
Sensible formatting throws up a difference:
SELECT
ra.AgreementSerial,
SUM(- (((((A.[Plastics Turnover value] * ISNULL(ra.Plastics, 0) -- [*]
- A.[Heating Spares Turnover Value] * ISNULL(ra.HeatingSpares, 0)) - A.[Boiler Turnover Value] * ISNULL(ra.Boilers, 0))
- A.[Copper Turnover Value] * - ISNULL(ra.Copper, 0)) - A.[Other Products Turnover Value] * ISNULL(ra.AllOtherProducts, 0))
- A.[Radiators Turnover value] * ISNULL(ra.Radiators, 0)) - A.[Renewables Turnover value] * ISNULL(ra.Renewables, 0)) AS QualifyingSpend,
SUM(A.[Boiler Turnover Value]) AS BoilerTurnover, SUM(A.[Boilers Margin £]) AS BoilerMargin,
SUM(A.[Copper Turnover Value]) AS CopperTurnover, SUM(A.[Copper Margin £]) AS CopperMargin,
SUM(A.[Heating Spares Turnover Value]) AS HeatingTurnover, SUM(A.[Heating Spares Margin £]) AS HeatingMargin,
SUM(A.[Other Products Turnover Value]) AS OtherTurnover, SUM(A.[Other Products Margin £]) AS OtherMargin,
SUM(A.[Plastics Turnover value]) AS PlasticsTurnover, SUM(A.[Plastics Margin £]) AS PlasticsMargin,
SUM(A.[Radiators Turnover value]) AS RadiatorsTurnover, SUM(A.[Radiators Margin £]) AS RadiatorsMargin,
SUM(A.[Renewables Turnover value]) AS RenewablesTurnover, SUM(A.[Renewables Margin £]) AS RenewablesMargin,
SUM(A.[All Turnover Value]) AS TotalTurnover,
SUM((((((A.[Plastics Margin £] * - ISNULL(ra.Plastics, 0) -- [* -]
- A.[Heating Spares Margin £] * ISNULL(ra.HeatingSpares, 0)) - A.[Boilers Margin £] * ISNULL(ra.Boilers, 0))
- A.[Copper Margin £] * - ISNULL(ra.Copper, 0)) - A.[Other Products Margin £] * ISNULL(ra.AllOtherProducts, 0))
- A.[Radiators Margin £] * ISNULL(ra.Radiators, 0)) - A.[Renewables Margin £] * ISNULL(ra.Renewables, 0)) AS QualifyingMargin,
SUM(A.[All Margin £]) AS TotalMargin
FROM dbo.RebateAgreements ra
INNER JOIN dbo.RebateAccounts rc
ON ra.AgreementSerial = rc.AgreementSerial
INNER JOIN dbo.CustomerRebateTracker01Pivot AS A
ON rc.CustomerAccount = A.CustomerAccount
WHERE (rc.Include = - 1)
AND (ra.AgreementStatus <> N'Not required')
AND (A.PostingMonth >= N'1512')
AND (ra.AgreementSerial = 2600)
GROUP BY ra.AgreementSerial
--HAVING (ra.AgreementSerial = 2600)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 15, 2016 at 5:01 am
SELECT
ra.AgreementSerial,
SUM(- (((((PlasticsTV - HeatingSparesTV) - BoilerTV) - CopperTV) - OtherTV) - RadiatorsTV) - RenewablesTV) AS QualifyingSpend,
SUM(A.[Boiler Turnover Value]) AS BoilerTurnover, SUM(A.[Boilers Margin £]) AS BoilerMargin,
SUM(A.[Copper Turnover Value]) AS CopperTurnover, SUM(A.[Copper Margin £]) AS CopperMargin,
SUM(A.[Heating Spares Turnover Value]) AS HeatingTurnover, SUM(A.[Heating Spares Margin £]) AS HeatingMargin,
SUM(A.[Other Products Turnover Value]) AS OtherTurnover, SUM(A.[Other Products Margin £]) AS OtherMargin,
SUM(A.[Plastics Turnover value]) AS PlasticsTurnover, SUM(A.[Plastics Margin £]) AS PlasticsMargin,
SUM(A.[Radiators Turnover value]) AS RadiatorsTurnover, SUM(A.[Radiators Margin £]) AS RadiatorsMargin,
SUM(A.[Renewables Turnover value]) AS RenewablesTurnover, SUM(A.[Renewables Margin £]) AS RenewablesMargin,
SUM(A.[All Turnover Value]) AS TotalTurnover,
SUM((((((A.[Plastics Margin £] * - ISNULL(ra.Plastics, 0)
- A.[Heating Spares Margin £] * ISNULL(ra.HeatingSpares, 0)) - A.[Boilers Margin £] * ISNULL(ra.Boilers, 0))
- A.[Copper Margin £] * - ISNULL(ra.Copper, 0)) - A.[Other Products Margin £] * ISNULL(ra.AllOtherProducts, 0))
- A.[Radiators Margin £] * ISNULL(ra.Radiators, 0)) - A.[Renewables Margin £] * ISNULL(ra.Renewables, 0)) AS QualifyingMargin,
SUM(A.[All Margin £]) AS TotalMargin
FROM dbo.RebateAgreements ra
INNER JOIN dbo.RebateAccounts rc
ON ra.AgreementSerial = rc.AgreementSerial
INNER JOIN dbo.CustomerRebateTracker01Pivot AS A
ON rc.CustomerAccount = A.CustomerAccount
CROSS APPLY (
-- Use a CROSS APPLY block like this to simplify your calculations
SELECT
PlasticsTV = A.[Plastics Turnover value] * ISNULL(ra.Plastics, 0),
HeatingSparesTV = A.[Heating Spares Turnover Value] * ISNULL(ra.HeatingSpares, 0),
BoilerTV = A.[Boiler Turnover Value] * ISNULL(ra.Boilers, 0),
CopperTV = A.[Copper Turnover Value] * - ISNULL(ra.Copper, 0),
OtherTV = A.[Other Products Turnover Value] * ISNULL(ra.AllOtherProducts, 0),
RadiatorsTV = A.[Radiators Turnover value] * ISNULL(ra.Radiators, 0),
RenewablesTV = A.[Renewables Turnover value] * ISNULL(ra.Renewables, 0)
) x
WHERE (rc.Include = - 1)
AND (ra.AgreementStatus <> N'Not required')
AND (A.PostingMonth >= N'1512')
AND (ra.AgreementSerial = 2600)
GROUP BY ra.AgreementSerial
--HAVING (ra.AgreementSerial = 2600)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 15, 2016 at 5:02 am
Also:
SELECT
ra.AgreementSerial,
0-SUM( -- check that your brackets are unambiguous
(A.[Plastics Turnover value] * x.Plastics)
- (A.[Heating Spares Turnover Value] * x.HeatingSpares)
- (A.[Boiler Turnover Value] * x.Boilers)
- (A.[Copper Turnover Value] * x.copper)
- (A.[Other Products Turnover Value] * x.AllOtherProducts)
- (A.[Radiators Turnover value] * x.Radiators)
- (A.[Renewables Turnover value] * x.Renewables)
) AS QualifyingSpend,
SUM(A.[Boiler Turnover Value]) AS BoilerTurnover, SUM(A.[Boilers Margin £]) AS BoilerMargin,
SUM(A.[Copper Turnover Value]) AS CopperTurnover, SUM(A.[Copper Margin £]) AS CopperMargin,
SUM(A.[Heating Spares Turnover Value]) AS HeatingTurnover, SUM(A.[Heating Spares Margin £]) AS HeatingMargin,
SUM(A.[Other Products Turnover Value]) AS OtherTurnover, SUM(A.[Other Products Margin £]) AS OtherMargin,
SUM(A.[Plastics Turnover value]) AS PlasticsTurnover, SUM(A.[Plastics Margin £]) AS PlasticsMargin,
SUM(A.[Radiators Turnover value]) AS RadiatorsTurnover, SUM(A.[Radiators Margin £]) AS RadiatorsMargin,
SUM(A.[Renewables Turnover value]) AS RenewablesTurnover, SUM(A.[Renewables Margin £]) AS RenewablesMargin,
SUM(A.[All Turnover Value]) AS TotalTurnover,
SUM(
(A.[Plastics Margin £] * x.Plastics)
- (A.[Heating Spares Margin £] * x.HeatingSpares)
- (A.[Boilers Margin £] * x.Boilers)
- (A.[Copper Margin £] * x.copper)
- (A.[Other Products Margin £] * x.AllOtherProducts)
- (A.[Radiators Margin £] * x.Radiators)
- (A.[Renewables Margin £] * x.Renewables)
) AS QualifyingMargin,
SUM(A.[All Margin £]) AS TotalMargin
FROM dbo.RebateAgreements ra
INNER JOIN dbo.RebateAccounts rc
ON ra.AgreementSerial = rc.AgreementSerial
INNER JOIN dbo.CustomerRebateTracker01Pivot AS A
ON rc.CustomerAccount = A.CustomerAccount
CROSS APPLY (
-- Use a CROSS APPLY block like this to simplify your calculations
SELECT
Plastics = ISNULL(ra.Plastics, 0),
HeatingSpares = ISNULL(ra.HeatingSpares, 0),
Boilers = ISNULL(ra.Boilers, 0),
Copper = 0 - ISNULL(ra.Copper, 0),
AllOtherProducts = ISNULL(ra.AllOtherProducts, 0),
Radiators = ISNULL(ra.Radiators, 0),
Renewables = ISNULL(ra.Renewables, 0)
) x
WHERE (rc.Include = - 1)
AND (ra.AgreementStatus <> N'Not required')
AND (A.PostingMonth >= N'1512')
AND (ra.AgreementSerial = 2600)
GROUP BY ra.AgreementSerial
--HAVING (ra.AgreementSerial = 2600)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply