May 6, 2010 at 9:50 pm
Greetings all. Its been a while since I have had to make any reports, but lately the TSQL is back in my life full force (finally!). Anyhow, the gist of my problem is that we have many projects, and a small number of managers, and a small number of metrics. Every month our state must meet corporate objectives with respect to the metrics of the projects. The bigwigs don't care what proportion of the metrics are met by each manager in the state, as long as the total goal for Virginia is met. However, the managers want to see their goal only. So, let's say we have 20 projects, two metrics, two managers, and each manager is responsible for 10 projects. Each manager is therefore responsible for 50% of the total projects, so each must contribute 50% toward meeting the metrics for any given month. If the Virginia goal for metric1 is 6, each manager must contribute 3. No problem, as long as the mix of managers, projects, and metrics always works out to nice even numbers, which, as you can imagine, never happens. My problem is that even though the sum of the managers proportions of total projects is always = 1, when I multiply these proportions to the total state goal, my results often add up to 1 or 2 more than the total. I have set up three temp tables that represent the two tables and one pivot view I am hitting, and you will be able to see the problem, so lets begin.
First the tables and sample data.
IF OBJECT_ID('TempDB..#managers','u') IS NOT NULL
DROP TABLE #managers
CREATE TABLE #managers
(
ManagerID INT IDENTITY(1,1),
Manager CHAR(4) NOT NULL
)
IF OBJECT_ID('TempDB..#projects','u') IS NOT NULL
DROP TABLE #projects
GO
CREATE TABLE #Projects
(
ProjectID INT IDENTITY(1,1),
ManagerID INT NOT NULL
)
IF OBJECT_ID('TempDB..#metrics','u')IS NOT NULL
DROP TABLE #metrics
--This is really a pivot view, but this is easier for
--demonstration purposes
CREATE TABLE #metrics
(
Metric CHAR(7) NOT NULL,
Jan INT NOT NULL,
Feb INT NOT NULL
)
GO
--More than 3 managers in reality, but 3 will do here
INSERT INTO #managers
SELECT 'Mgr1' UNION ALL
SELECT 'Mgr2' UNION ALL
SELECT 'Mgr3'
--970 projects in reality, and soon to grow into several thousand
INSERT INTO #projects(ManagerID)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3
--Several more metrics, but I was able to re-create the
--problem using only 2
INSERT INTO #metrics
SELECT 'Metric1',2,5 UNION ALL
SELECT 'Metric2',3,8
Now for the end result. This code is kind of like the real thing, the only difference being that in reality, the derived table is a view.
SELECT
man.Manager,
met.Metric,
Jan = met.Jan*t1.Pct,
Feb = met.Feb*t1.Pct,
RoundJan = ROUND(met.Jan*t1.Pct,0),
RoundFeb = ROUND(met.Feb*t1.Pct,0)
FROM #managers man CROSS JOIN #metrics met INNER JOIN
(--This is really a view set up in the same fashion as
--I have done here
SELECT
m.Manager,
Total = COUNT(p.ProjectID),
Pct = COUNT(p.ProjectID)/((SELECT COUNT(ProjectID) FROM #projects)*1.0)
FROM #managers m INNER JOIN #projects p
ON m.ManagerID = p.ManagerID
GROUP BY m.Manager
) t1
ON man.Manager = t1.Manager
ORDER BY met.Metric,man.Manager
Now, we know that the corporate goal for metric1 is only 2 for the month of January, and the RoundJan columns for metric1 sum to 2, since .57 for manager1 should round to 1, for manager2 1=1, and for manager3, .42 should round down to 0. Now look at the month of February. We know that the corporate goal is 5, and, again, no problems since the rounding happens in the right direction. The problem is for metric 2 in January. We know the corporate goal is only 3, yet the RoundJan columns sum to 4, since for manager1 .85 should round to 1, for manager 2 1.5 rounds to 1, and for manager3 .64 rounds to 1. The problem, I think, is that if a number rounds to .5, I need it to round down not up. I'm not sure if there is a way to do that, or if it might cause problems in the other direction, but it seems to me that if a sum of proportions always =1, then no matter what number you multiply them by, the rounded sum should always = the desired number. Where is the extra 1 coming from, and is there a way to safely prevent it? If I take the output and paste it into Excel, and format it for 0 decimal places, the unrounded values are displayed exactly like the rounded values. However, if I drag my mouse down January values for metric2, the value shown above the systems tray is 3. Somehow Excel knows what the real answer should be. Any ideas? Thank you for any time you may take to consider my problem, and I apologize for the long explanation.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 6, 2010 at 11:27 pm
Hi Greg... long time no see.
To round down as you seem to think you need, lookup ROUND in Books Online and study the 3rd operand of the function that not too many people know about.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2010 at 9:13 am
Thanks Jeff, I knew I should have looked there first. It looks like that will do, but only when the value to the right of the decimal is exactly .5. For example, I have some cases where one value might be 1.6, and the other value might be 1.5. I need the 1.5 to round to 1, and the 1.6 to round to 2. I'm thinking of some CASE statements that might work, but as a side note, I discovered(for me anyway) something interesting about the ROUND function. I was about to snap the neck of my laptop because I could not get the darned thing to work like the BOL example. I was using a decimal variable, then setting the value = 1.5, and it always rounded to 2. Just for grins I tried it with the actual value and also with a float variable, and both worked as I would expect. Any ideas why this would be so?
DECLARE @dec DECIMAL,
@float FLOAT
SELECT @dec = 1.5,
@float = 1.5
SELECT ROUND(1.5,0,1)
SELECT ROUND(@dec,0,1)
SELECT ROUND(@float,0,1)
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply