asked on SQL Margin Calculation gives divide by zero errors
I have a table showing Revenue (R), Cost (C) & Margin (M) values by Product Category for each period 01 > 12 like in the attached
I have substituted the NULL values with 0 using CASE WHEN SUM(C03) IS NULL THEN 0 ELSE SUM(C03) END
In each period margin calc I use CASE to avoid the Divide by Zero errors when the Revenue values are 0 like
CAST (CASE WHEN Sum([R01])= 0 OR Sum([R01]) IS NULL OR Sum([C01]) = 0 OR Sum([C01]) IS NULL THEN 0 ELSE
((Sum([R01]) - Sum([C01]))/(Sum([R01])))*
However I cannot see how is best to create the margin values using the totals.
What I need is ((RTotal-CTotal)/RTotal)*1
Driving me crazy, thanks.
I have substituted the NULL values with 0 using CASE WHEN SUM(C03) IS NULL THEN 0 ELSE SUM(C03) END
In each period margin calc I use CASE to avoid the Divide by Zero errors when the Revenue values are 0 like
CAST (CASE WHEN Sum([R01])= 0 OR Sum([R01]) IS NULL OR Sum([C01]) = 0 OR Sum([C01]) IS NULL THEN 0 ELSE
((Sum([R01]) - Sum([C01]))/(Sum([R01])))*
However I cannot see how is best to create the margin values using the totals.
What I need is ((RTotal-CTotal)/RTotal)*1
Driving me crazy, thanks.
Period R01 C01 M01% R02 C02 M02% R03 C03 M03% R04 C04 M04% ....... R12 C12 M12% RTotal CTotal MTotal%
Product A 50 20 60 100 20 80 100 0 100 0 0 0 0 0 0 250 40 ???????
Microsoft SQL ServerSQL
Last Comment
PortletPaul
Use
case isnull(column1,0)=0 then 1 else isnull(column1,0) end
case isnull(column1,0)=0 then 1 else isnull(column1,0) end
To avoid divide by zero error
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
Your assumption is correct.
The data, query and expected result are now attached.
Thanks.
Data---Expected-Result.xlsx
The data, query and expected result are now attached.
Thanks.
DECLARE @FromDate DATE = '2015-01-01', @ToDate DATE = '2015-06-30';
--DROP Temp TABLE if it exists
IF object_id('tempdb..#GLUK') is not null BEGIN DROP TABLE #GLUK END
-- Select the required GL Transactions by Date Range
/* Create #GLUK */
CREATE TABLE #GLUK (Account NVARCHAR (20),[Year] INT, [Per] VARCHAR(3),[Posting Date] DATE, [Product Series] NVARCHAR (20),[Country] NVARCHAR (20),[Amount] DECIMAL(10,2))
INSERT INTO #GLUK (Account,[Year],[Per],[Posting Date],[Product Series],[Country],[Amount])
SELECT [G_L Account No_],YEAR([Posting Date]) AS [Year],dbo.IDEALfnPER ([Posting Date]) AS [Per],[Posting Date],[Global Dimension 2 Code] AS [Product Series],[Global Dimension 1 Code] AS [Country],[Amount]
FROM [IDEAL_LIVE].[dbo].[UK$G_L Entry] AS GLE_UK
WHERE [Posting Date] >= @FromDate AND [Posting Date] <= @ToDate
--Exclude Inter Company.
AND [G_L Account No_] NOT IN ('52115','51115')
--REVENUE BY PRODUCT SERIES
--DROP Temp TABLE if it exists
IF object_id('tempdb..#PS_REV_UK') is not null BEGIN DROP TABLE #PS_REV_UK END
/* Create #PS_REV_UK */
CREATE TABLE #PS_REV_UK ([Product Series] NVARCHAR (20),[R01] DECIMAL (10,2),[R02] DECIMAL (10,2),[R03] DECIMAL (10,2),[R04] DECIMAL (10,2),[R05] DECIMAL (10,2),[R06] DECIMAL (10,2),
[R07] DECIMAL (10,2),[R08] DECIMAL (10,2),[R09] DECIMAL (10,2),[R10] DECIMAL (10,2),[R11] DECIMAL (10,2),[R12] DECIMAL (10,2))
INSERT INTO #PS_REV_UK ([Product Series],[R01],[R02],[R03],[R04],[R05],[R06],[R07],[R08],[R09],[R10],[R11],[R12])
SELECT [Product Series],
Sum([01])*-1 AS 'R01',Sum([02])*-1 AS 'R02',Sum([03])*-1 AS 'R03',Sum([04])*-1 AS 'R04',
Sum([05])*-1 AS 'R05',Sum([06])*-1 AS 'R06',Sum([07])*-1 AS 'R07',Sum([08])*-1 AS 'R08',
Sum([09])*-1 AS 'R09',Sum([10])*-1 AS 'R10',Sum([11])*-1 AS 'R11',Sum([12])*-1 AS 'R12'
From #GLUK
PIVOT (SUM(Amount) FOR Per in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) AS PTable
WHERE Account LIKE '511%'
GROUP BY [Product Series];
--COST BY PRODUCT SERIES
--DROP Temp TABLE if it exists
IF object_id('tempdb..#PS_COST_UK') is not null BEGIN DROP TABLE #PS_COST_UK END
/* Create #PS_COST_UK */
CREATE TABLE #PS_COST_UK ([Product Series] NVARCHAR (20),[C01] DECIMAL (10,2),[C02] DECIMAL (10,2),[C03] DECIMAL (10,2),[C04] DECIMAL (10,2),[C05] DECIMAL (10,2),[C06] DECIMAL (10,2),
[C07] DECIMAL (10,2),[C08] DECIMAL (10,2),[C09] DECIMAL (10,2),[C10] DECIMAL (10,2),[C11] DECIMAL (10,2),[C12] DECIMAL (10,2))
INSERT INTO #PS_COST_UK ([Product Series],[C01],[C02],[C03],[C04],[C05],[C06],[C07],[C08],[C09],[C10],[C11],[C12])
SELECT [Product Series],
Sum([01]) AS 'C01',Sum([02]) AS 'C02',Sum([03]) AS 'C03',Sum([04]) AS 'C04',
Sum([05]) AS 'C05',Sum([06]) AS 'C06',Sum([07]) AS 'C07',Sum([08]) AS 'C08',
Sum([09]) AS 'C09',Sum([10]) AS 'C10',Sum([11]) AS 'C11',Sum([12]) AS 'C12'
From #GLUK
PIVOT (SUM(Amount) FOR Per in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) AS PTable
WHERE Account LIKE '521%'
GROUP BY [Product Series];
/*****PRODUCT SERIES OUTPUT*****/
--Join the two and calculate the margin.
SELECT RC.[Product Series],
-- Remove NULLS using Function. Required to avoid Divide by Zero Errors
[dbo].[IDEALfnNULLS to Zero](Sum([R01])) AS 'R01',
CAST (CASE WHEN Sum([R01])= 0 OR Sum([R01]) IS NULL OR Sum([C01]) = 0 OR Sum([C01]) IS NULL THEN 0 ELSE
((Sum([R01]) - Sum([C01]))/(Sum([R01])))*100 END AS DECIMAL(10,2)) AS 'M01',
[dbo].[IDEALfnNULLS to Zero](Sum([C01])) AS 'C01',
[dbo].[IDEALfnNULLS to Zero](Sum([R02])) AS 'R02',
CAST (CASE WHEN Sum([R02])= 0 OR Sum([R02]) IS NULL OR Sum([C02]) = 0 OR Sum([C02]) IS NULL THEN 0 ELSE
((Sum([R02]) - Sum([C02]))/(Sum([R02])))*100 END AS DECIMAL(10,2)) AS 'M02',
[dbo].[IDEALfnNULLS to Zero](Sum([C02])) AS 'C02',
[dbo].[IDEALfnNULLS to Zero](Sum([R03])) AS 'R03',
CAST (CASE WHEN Sum([R03])= 0 OR Sum([R03]) IS NULL OR Sum([C03]) = 0 OR Sum([C03]) IS NULL THEN 0 ELSE
((Sum([R03]) - Sum([C03]))/(Sum([R03])))*100 END AS DECIMAL(10,2)) AS 'M03',
[dbo].[IDEALfnNULLS to Zero](Sum([C03])) AS 'C03',
[dbo].[IDEALfnNULLS to Zero](Sum([R04])) AS 'R04',
CAST (CASE WHEN Sum([R04])= 0 OR Sum([R04]) IS NULL OR Sum([C04]) = 0 OR Sum([C04]) IS NULL THEN 0 ELSE
((Sum([R04]) - Sum([C04]))/(Sum([R04])))*100 END AS DECIMAL(10,2)) AS 'M04',
[dbo].[IDEALfnNULLS to Zero](Sum([C04])) AS 'C04',
[dbo].[IDEALfnNULLS to Zero](Sum([R05])) AS 'R05',
CAST (CASE WHEN Sum([R05])= 0 OR Sum([R05]) IS NULL OR Sum([C05]) = 0 OR Sum([C05]) IS NULL THEN 0 ELSE
((Sum([R05]) - Sum([C05]))/(Sum([R05])))*100 END AS DECIMAL(10,2)) AS 'M05',
[dbo].[IDEALfnNULLS to Zero](Sum([C05])) AS 'C05',
[dbo].[IDEALfnNULLS to Zero](Sum([R06])) AS 'R06',
CAST (CASE WHEN Sum([R06])= 0 OR Sum([R06]) IS NULL OR Sum([C06]) = 0 OR Sum([C06]) IS NULL THEN 0 ELSE
((Sum([R06]) - Sum([C06]))/(Sum([R06])))*100 END AS DECIMAL(10,2)) AS 'M06',
[dbo].[IDEALfnNULLS to Zero](Sum([C06])) AS 'C06',
[dbo].[IDEALfnNULLS to Zero](Sum([R07])) AS 'R07',
CAST (CASE WHEN Sum([R07])= 0 OR Sum([R07]) IS NULL OR Sum([C07]) = 0 OR Sum([C07]) IS NULL THEN 0 ELSE
((Sum([R07]) - Sum([C07]))/(Sum([R07])))*100 END AS DECIMAL(10,2)) AS 'M07',
[dbo].[IDEALfnNULLS to Zero](Sum([C07])) AS 'C07',
[dbo].[IDEALfnNULLS to Zero](Sum([R08])) AS 'R08',
CAST (CASE WHEN Sum([R08])= 0 OR Sum([R08]) IS NULL OR Sum([C08]) = 0 OR Sum([C08]) IS NULL THEN 0 ELSE
((Sum([R08]) - Sum([C08]))/(Sum([R08])))*100 END AS DECIMAL(10,2)) AS 'M08',
[dbo].[IDEALfnNULLS to Zero](Sum([C08])) AS 'C08',
[dbo].[IDEALfnNULLS to Zero](Sum([R09])) AS 'R09',
CAST (CASE WHEN Sum([R09])= 0 OR Sum([R09]) IS NULL OR Sum([C09]) = 0 OR Sum([C09]) IS NULL THEN 0 ELSE
((Sum([R09]) - Sum([C09]))/(Sum([R09])))*100 END AS DECIMAL(10,2)) AS 'M09',
[dbo].[IDEALfnNULLS to Zero](Sum([C09])) AS 'C09',
[dbo].[IDEALfnNULLS to Zero](Sum([R10])) AS 'R10',
CAST (CASE WHEN Sum([R10])= 0 OR Sum([R10]) IS NULL OR Sum([C10]) = 0 OR Sum([C10]) IS NULL THEN 0 ELSE
((Sum([R10]) - Sum([C10]))/(Sum([R10])))*100 END AS DECIMAL(10,2)) AS 'M10',
[dbo].[IDEALfnNULLS to Zero](Sum([C10])) AS 'C10',
[dbo].[IDEALfnNULLS to Zero](Sum([R11])) AS 'R11',
CAST (CASE WHEN Sum([R11])= 0 OR Sum([R11]) IS NULL OR Sum([C11]) = 0 OR Sum([C11]) IS NULL THEN 0 ELSE
((Sum([R11]) - Sum([C11]))/(Sum([R11])))*100 END AS DECIMAL(10,2)) AS 'M11',
[dbo].[IDEALfnNULLS to Zero](Sum([C11])) AS 'C11',
[dbo].[IDEALfnNULLS to Zero](Sum([R12])) AS 'R12',
CAST (CASE WHEN Sum([R12])= 0 OR Sum([R12]) IS NULL OR Sum([C12]) = 0 OR Sum([C12]) IS NULL THEN 0 ELSE
((Sum([R12]) - Sum([C12]))/(Sum([R12])))*100 END AS DECIMAL(10,2)) AS 'M12',
[dbo].[IDEALfnNULLS to Zero](Sum([C12])) AS 'C12',
-- TOTALS
[dbo].[IDEALfnNULLS to Zero](Sum([R01]))+[dbo].[IDEALfnNULLS to Zero](Sum([R02]))+[dbo].[IDEALfnNULLS to Zero](Sum([R03]))+[dbo].[IDEALfnNULLS to Zero](Sum([R04]))+[dbo].[IDEALfnNULLS to Zero](Sum([R05]))+[dbo].[IDEALfnNULLS to Zero](Sum([R06]))+
[dbo].[IDEALfnNULLS to Zero](Sum([R07]))+[dbo].[IDEALfnNULLS to Zero](Sum([R08]))+[dbo].[IDEALfnNULLS to Zero](Sum([R09]))+[dbo].[IDEALfnNULLS to Zero](Sum([R10]))+[dbo].[IDEALfnNULLS to Zero](Sum([R11]))+[dbo].[IDEALfnNULLS to Zero](Sum([R12])) AS 'RTotal',
[dbo].[IDEALfnNULLS to Zero](Sum([C01]))+[dbo].[IDEALfnNULLS to Zero](Sum([C02]))+[dbo].[IDEALfnNULLS to Zero](Sum([C03]))+[dbo].[IDEALfnNULLS to Zero](Sum([C04]))+[dbo].[IDEALfnNULLS to Zero](Sum([C05]))+[dbo].[IDEALfnNULLS to Zero](Sum([C06]))+
[dbo].[IDEALfnNULLS to Zero](Sum([C07]))+[dbo].[IDEALfnNULLS to Zero](Sum([C08]))+[dbo].[IDEALfnNULLS to Zero](Sum([C09]))+[dbo].[IDEALfnNULLS to Zero](Sum([C10]))+[dbo].[IDEALfnNULLS to Zero](Sum([C11]))+[dbo].[IDEALfnNULLS to Zero](Sum([C12])) AS 'CTotal'
-- ***** NEED THE TOTAL MARGIN CALC HERE *****
FROM #PS_REV_UK AS RC
LEFT OUTER JOIN #PS_COST_UK AS CC ON CC.[Product Series] = RC.[Product Series]
GROUP BY RC.[Product Series]
Data---Expected-Result.xlsx
Thanks. That will take a little while to work through.
In the meantime please note that the safest of all date literals in SQL Server is YYYYMMDD
e.g. instead of
DECLARE @FromDate DATE = '2015-01-01', @ToDate DATE = '2015-06-30';
use
DECLARE @FromDate DATE = '20150101', @ToDate DATE = '20150701';
& Best practice for date ranges is NOT to use BETWEEN and whilst you haven't literally used this, you are currently using the equivalent.
see: "Beware of Between"
so, instead of this:
WHERE [Posting Date] >= @FromDate AND [Posting Date] <= @ToDate
use:
WHERE [Posting Date] >= @FromDate AND [Posting Date] < @ToDate
The difference is subtle, but it allows the raw data to be in any datetime precision and your query will remain accurate.
In the meantime please note that the safest of all date literals in SQL Server is YYYYMMDD
e.g. instead of
DECLARE @FromDate DATE = '2015-01-01', @ToDate DATE = '2015-06-30';
use
DECLARE @FromDate DATE = '20150101', @ToDate DATE = '20150701';
& Best practice for date ranges is NOT to use BETWEEN and whilst you haven't literally used this, you are currently using the equivalent.
see: "Beware of Between"
so, instead of this:
WHERE [Posting Date] >= @FromDate AND [Posting Date] <= @ToDate
use:
WHERE [Posting Date] >= @FromDate AND [Posting Date] < @ToDate
The difference is subtle, but it allows the raw data to be in any datetime precision and your query will remain accurate.
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER
That works perfectly thank you.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Thank you for your devotion to duty on a Sunday!
:) its because I remembered I had this hanging and found some time. Cheers.
1. the existing query in full
2. sample data (not the query result, the source data)
3. expected result
It appears you are taking normalized data and pivoting this into 36 columns, and you also want a grand total set of columns. (? is that correct ?)
The most efficient way of doing this may not be trying to reuse the 36 column summary.
--
please note I edited your question to display the attached data in a code block, these are better as we don't have to look at multiple sources to read a question. :-)