I'm building a Top 10 for a report for some management information which ends with the following script:
IF @ReportType = 'SectorEQ'
BEGIN
/* Final Select with roll up for total per portfolio */
SELECT
CONVERT(VARCHAR, ReportingDate, 103) AS ReportingDate
, PortfolioID AS FundCode
, PortfolioNme AS FundName
, CASE
WHEN GROUPING_ID(ReportingDate, PortfolioID, PortfolioNme, Sector, [Rank], [Weight]) = 7 THEN 'Total'
ELSE Sector
END AS Sector
, CASE
WHEN GROUPING_ID(ReportingDate, PortfolioID, PortfolioNme, Sector, [Rank], [Weight]) = 7 THEN 102
ELSE [Rank]
END AS [Rank]
, CAST(SUM([Weight]) AS DECIMAL(22,1)) AS Percentage
FROM @FinalOutputEQ_CS
GROUP BY ReportingDate
, PortfolioID
, PortfolioNme
, Sector
, [Rank]
, [Weight] WITH ROLLUP
HAVING GROUPING_ID(ReportingDate, PortfolioID, PortfolioNme, Sector, [Rank], [Weight]) IN (1,7)
ORDER BY ReportingDate
, PortfolioID
, [Rank]
As you will see, the percentage is rounded to 1 decimal place which can potentially cause rounding issues because the sum of the percentage to 1 decimal place could be less than or greater than 100, ie 100.1 or 99.9. What i need to do is to ensure that the rounded to 1 decimal place percentages all equal to 100.0.
What i would like to do is have a check to see that the sum of the rounded percentages is equal to 100.0, if it isn't then to take the Top 1 and minus the difference from its percentage.
I've had a few ideas as to how to do this, but i'm wondering what would be the best method to ensure this run quickly and efficiently without slowing the proc down?
EDIT: Example Table
ReportingDate FundCode FundName Sector Rank Percentage
28/02/2013 XXXXXXXX XXXXXXXXXXXXXXX Consumer Discretionary 1 16.1
28/02/2013 XXXXXXXX XXXXXXXXXXXXXXX Health Care 2 13.8
28/02/2013 XXXXXXXX XXXXXXXXXXXXXXX Information Technology 3 11.8
28/02/2013 XXXXXXXX XXXXXXXXXXXXXXX Telecommunication Services 4 10.3
28/02/2013 XXXXXXXX XXXXXXXXXXXXXXX Consumer Staples 5 10.1
28/02/2013 XXXXXXXX XXXXXXXXXXXXXXX Industrials 6 8.7
28/02/2013 XXXXXXXX XXXXXXXXXXXXXXX Financials 7 6.8
28/02/2013 XXXXXXXX XXXXXXXXXXXXXXX Materials 8 6.5
28/02/2013 XXXXXXXX XXXXXXXXXXXXXXX Energy 9 5.2
28/02/2013 XXXXXXXX XXXXXXXXXXXXXXX Utilities 10 0.7
28/02/2013 XXXXXXXX XXXXXXXXXXXXXXX Cash and Bonds 101 10.1
28/02/2013 XXXXXXXX XXXXXXXXXXXXXXX Total 102 100.1
The only solution i could work out for this is to create the script below which basically summed up the detail to give a total. Then after my CTE i subtracted this from the top 1 and the total:
IF @ReportType = 'SectorEQ'
BEGIN
INSERT INTO @RoundingDifference
SELECT
PortfolioID
, CAST(SUM([Weight])-100 AS DECIMAL(22,1)) AS [Weight]
FROM @FinalOutputEQ_CS
WHERE Sector <> 'Total'
GROUP BY
ReportingDate
, PortfolioID
, PortfolioNme
END
IF @ReportType = 'SectorEQ'
BEGIN
;;WITH CTE AS
(
/* Final Select with roll up for total per portfolio */
SELECT
CONVERT(VARCHAR, ReportingDate, 103) AS ReportingDate
, PortfolioID AS FundCode
, PortfolioNme AS FundName
, CASE
WHEN GROUPING_ID(ReportingDate, PortfolioID, PortfolioNme, Sector, [Rank], [Weight]) = 7 THEN 'Total'
ELSE Sector
END AS Sector
, CASE
WHEN GROUPING_ID(ReportingDate, PortfolioID, PortfolioNme, Sector, [Rank], [Weight]) = 7 THEN 102
ELSE [Rank]
END AS [Rank]
, CAST(SUM([Weight]) AS DECIMAL(22,1)) AS Percentage
FROM @FinalOutputEQ_CS
GROUP BY ReportingDate
, PortfolioID
, PortfolioNme
, Sector
, [Rank]
, [Weight] WITH ROLLUP
HAVING GROUPING_ID(ReportingDate, PortfolioID, PortfolioNme, Sector, [Rank], [Weight]) IN (1,7)
)
SELECT
ReportingDate
, FundCode
, FundName
, Sector
, [Rank]
, CASE
WHEN [Rank]IN (1,102) THEN Percentage - RD.[Weight]
ELSE Percentage
END AS Percentage
FROM CTE AS CTE
INNER JOIN @RoundingDifference AS RD
ON RD.PortfolioID = CTE.FundCode
ORDER BY ReportingDate
, FundCode
, [Rank]
END