July 22, 2013 at 12:17 pm
Hello,
I am writing a report in SSRS and at first thought I would implement these biz rules at the report level but finding it inferior to the task. So now I can either implement as custom code (vb.net) in SSRS or at TSQL level which would be my preference.
So the majority of the business rule I have already satisfied. It is this last step that has me stumped. To make it simple I have provided a sample table that represents the data I need to work with.
DECLARE @TeamTable TABLE
(
TeamID VARCHAR(3),
AssignedTask INT
)
INSERT INTO @TeamTable VALUES ('AAA', 12)
INSERT INTO @TeamTable VALUES ('BBB', 45)
INSERT INTO @TeamTable VALUES ('CCC', 67)
INSERT INTO @TeamTable VALUES ('DDD', 11)
INSERT INTO @TeamTable VALUES ('EEE', 12)
INSERT INTO @TeamTable VALUES ('FFF', 10)
INSERT INTO @TeamTable VALUES ('GGG', 11)
INSERT INTO @TeamTable VALUES ('HHH', 6)
INSERT INTO @TeamTable VALUES ('III', 3)
INSERT INTO @TeamTable VALUES ('JJJ', 11)
INSERT INTO @TeamTable VALUES ('KKK', 0)
INSERT INTO @TeamTable VALUES ('LLL', 4)
INSERT INTO @TeamTable VALUES ('MMM', 12)
INSERT INTO @TeamTable VALUES ('NNN', 1)
INSERT INTO @TeamTable VALUES ('OOO', 0)
INSERT INTO @TeamTable VALUES ('PPP', 12)
INSERT INTO @TeamTable VALUES ('QQQ', 12)
INSERT INTO @TeamTable VALUES ('RRR', 0)
This query get's the data in a manner that accurately represents my dataset:
SELECT TT.TeamID, TT.AssignedTask, RANK() OVER
(ORDER BY TT.AssignedTask DESC) AS 'Rank'
FROM @TeamTable TT
WHERE AssignedTask > 0
Now here is the trick. I have to assign a weighted value to each team based upon the number of teams that have 1 or more task assigned and I don't know what to do next. So in my sample data you'll see that teams KKK, OOO, RRR have zero so 15 is our top point value and I get that using:
DECLARE @RankMax TINYINT
SET @RankMax = @@ROWCOUNT
SELECT @RankMax
So team CCC gets 15 points because they are top dog and it scales down.....with a twist. BBB gets 14.
AAA, EEE, MMM, PPP, QQQ all tied. So the next set of points are added then averaged and everyone gets the average score.
So 5 teams tied thus 13,12,11,10,9 are added together to equal 55 divided by 5 means each team gets 11 points.
Then we move down to JJJ, GGG, DDD another tie. 8 + 7 + 6=21 and 21/3 = 7
The rest go down to zero. So the final recordset would look like this:
TeamID | AssignTask | Rank |
CCC 67 15
BBB 45 14
AAA 12 11
EEE 12 11
MMM 12 11
PPP 12 11
QQQ 12 11
JJJ 11 7
GGG 11 7
DDD 11 7
FFF 10 5
HHH 6 4
LLL 4 3
III 3 2
NNN 1 1
Not looking for anyone to do my homework just a pointer or two in how to approach the problem would be much appreciated.
Thank You
July 23, 2013 at 6:58 am
You can try something like this...
DECLARE @TeamTable TABLE
(
TeamID VARCHAR(3),
AssignedTask INT
)
INSERT INTO @TeamTable VALUES ('AAA', 12)
INSERT INTO @TeamTable VALUES ('BBB', 45)
INSERT INTO @TeamTable VALUES ('CCC', 67)
INSERT INTO @TeamTable VALUES ('DDD', 11)
INSERT INTO @TeamTable VALUES ('EEE', 12)
INSERT INTO @TeamTable VALUES ('FFF', 10)
INSERT INTO @TeamTable VALUES ('GGG', 11)
INSERT INTO @TeamTable VALUES ('HHH', 6)
INSERT INTO @TeamTable VALUES ('III', 3)
INSERT INTO @TeamTable VALUES ('JJJ', 11)
INSERT INTO @TeamTable VALUES ('KKK', 0)
INSERT INTO @TeamTable VALUES ('LLL', 4)
INSERT INTO @TeamTable VALUES ('MMM', 12)
INSERT INTO @TeamTable VALUES ('NNN', 1)
INSERT INTO @TeamTable VALUES ('OOO', 0)
INSERT INTO @TeamTable VALUES ('PPP', 12)
INSERT INTO @TeamTable VALUES ('QQQ', 12)
INSERT INTO @TeamTable VALUES ('RRR', 0)
select TeamID, AssignedTask, AVG(Rank1) OVER(PARTITION BY AssignedTask) AS 'Avg'
from
(
SELECTTT.TeamID, TT.AssignedTask
, RANK() OVER (ORDER BY TT.AssignedTask, TEAMID DESC) AS 'Rank1'
FROM@TeamTable TT
WHEREAssignedTask > 0
)t
July 23, 2013 at 6:45 pm
Here's another way that eliminates the need for the outer query:
SELECT TeamID, AssignedTask
,r=RANK() OVER (ORDER BY AssignedTask) +
COUNT(AssignedTask) OVER (PARTITION BY AssignedTask)/2
FROM @TeamTable
WHERE AssignedTask > 0
ORDER BY r DESC, TeamID DESC
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply