Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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])))*100 END AS DECIMAL(10,2)) AS 'M01',

However I cannot see how is best to create the margin values using the totals.

What I need is ((RTotal-CTotal)/RTotal)*100 where some of the divisor values are 0

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 ???????
```

Example.txt
Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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]
```

Whole-Query.txtData---Expected-Result.xlsx

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

& Best practice for date ranges is

see: "Beware of Between"

so, instead of this:

WHERE [Posting Date] >= @FromDate AND [Posting Date] <= @ToDate

use:

WHERE [Posting Date] >= @FromDate AND [Posting Date]

The difference is subtle, but it allows the raw data to be in any datetime precision and your query will remain accurate.

I would suggest making it far easier by doing this, into a single temp table if you prefer, or as a "derived table" (which is probably how I would do it):

```
DECLARE @FromDate date = '20150101'
, @ToDate date = '20150701';
SELECT
[Global Dimension 2 Code] AS [Product Series]
, SUM(CASE WHEN [G_L Account No_] LIKE '511%' AND CA.Per = '01' THEN [Amount] END) AS R01
, SUM(CASE WHEN [G_L Account No_] LIKE '511%' AND CA.Per = '02' THEN [Amount] END) AS R02
, SUM(CASE WHEN [G_L Account No_] LIKE '511%' AND CA.Per = '03' THEN [Amount] END) AS R03
, SUM(CASE WHEN [G_L Account No_] LIKE '511%' AND CA.Per = '04' THEN [Amount] END) AS R04
, SUM(CASE WHEN [G_L Account No_] LIKE '511%' AND CA.Per = '05' THEN [Amount] END) AS R05
, SUM(CASE WHEN [G_L Account No_] LIKE '511%' AND CA.Per = '06' THEN [Amount] END) AS R06
, SUM(CASE WHEN [G_L Account No_] LIKE '511%' AND CA.Per = '07' THEN [Amount] END) AS R07
, SUM(CASE WHEN [G_L Account No_] LIKE '511%' AND CA.Per = '08' THEN [Amount] END) AS R08
, SUM(CASE WHEN [G_L Account No_] LIKE '511%' AND CA.Per = '09' THEN [Amount] END) AS R09
, SUM(CASE WHEN [G_L Account No_] LIKE '511%' AND CA.Per = '10' THEN [Amount] END) AS R10
, SUM(CASE WHEN [G_L Account No_] LIKE '511%' AND CA.Per = '11' THEN [Amount] END) AS R11
, SUM(CASE WHEN [G_L Account No_] LIKE '511%' AND CA.Per = '12' THEN [Amount] END) AS R12
, SUM(CASE WHEN [G_L Account No_] LIKE '511%' THEN [Amount] END) AS Rtotal
, SUM(CASE WHEN [G_L Account No_] LIKE '521%' AND CA.Per = '01' THEN [Amount] END) AS C01
, SUM(CASE WHEN [G_L Account No_] LIKE '521%' AND CA.Per = '02' THEN [Amount] END) AS C02
, SUM(CASE WHEN [G_L Account No_] LIKE '521%' AND CA.Per = '03' THEN [Amount] END) AS C03
, SUM(CASE WHEN [G_L Account No_] LIKE '521%' AND CA.Per = '04' THEN [Amount] END) AS C04
, SUM(CASE WHEN [G_L Account No_] LIKE '521%' AND CA.Per = '05' THEN [Amount] END) AS C05
, SUM(CASE WHEN [G_L Account No_] LIKE '521%' AND CA.Per = '06' THEN [Amount] END) AS C06
, SUM(CASE WHEN [G_L Account No_] LIKE '521%' AND CA.Per = '07' THEN [Amount] END) AS C07
, SUM(CASE WHEN [G_L Account No_] LIKE '521%' AND CA.Per = '08' THEN [Amount] END) AS C08
, SUM(CASE WHEN [G_L Account No_] LIKE '521%' AND CA.Per = '09' THEN [Amount] END) AS C09
, SUM(CASE WHEN [G_L Account No_] LIKE '521%' AND CA.Per = '10' THEN [Amount] END) AS C10
, SUM(CASE WHEN [G_L Account No_] LIKE '521%' AND CA.Per = '11' THEN [Amount] END) AS C11
, SUM(CASE WHEN [G_L Account No_] LIKE '521%' AND CA.Per = '12' THEN [Amount] END) AS C12
, SUM(CASE WHEN [G_L Account No_] LIKE '521%' THEN [Amount] END) AS Ctotal
FROM [IDEAL_LIVE].[dbo].[UK$G_L Entry] AS GLE_UK
CROSS APPLY (
SELECT dbo.IDEALfnPER(GLE_UK.[Posting Date])
) CA ([Per])
WHERE [Posting Date] >= @FromDate
AND [Posting Date] < @ToDate
--Exclude Inter Company.
AND [G_L Account No_] NOT IN ('52115', '51115')
GROUP BY
[Global Dimension 2 Code]
```

Note, the row totals are already included.

This approach should result in zeros if there was no values to sum, and from this you should now be able to produce your margin calculations using simple case expressions, even for the totals.

bottom line:

In the "good old days" before "pivot" existed as a TSQL feature we always used case expressions and group by to arrive at pivoted data. In many way it is still the easier method, please don't assume that the "pivot" feature is necessarily the best approach.

& I have also applied the recommended change regarding the date range filter

see: "Beware of Between"

{+ edit}

I overlooked putting " ELSE 0 " in the case expressions. I suggest you include this please.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

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. :-)