Rank Order Exceptions

[owp^3]

I have an append query that loads a temporary table with the rank order of
projects within status groups based upon a score.

I am loading a temporary table because of how slow these kinds of queries
are. The query works fine but management wants certain projects to rank
higher than others based on a second criteria. Since it is a specific value
they are looking for I can't use it to group on.

Here is the query that currently works:
INSERT INTO
staging_StagingRank ( ProjectID, ProjectStatus, ProjectScore,
MRE_CurrentRank )
SELECT
select_MREStaging.MRE_ProjectID,
select_MREStaging.MRE_CurrentStatus,
select_MREStaging.MRE_Score,
(SELECT Count(*) FROM select_MREStaging AS Stag
WHERE Stag.MRE_Score > select_MREStaging.MRE_Score AND
Stag.MRE_CurrentStatus =
select_MREStaging.MRE_CurrentStatus)+1
AS MRE_CurrentRank
FROM select_MREStaging

So this creates a 1 to N rank within each Status Group.

The additional rule is any Project that has a MRE_PlanYear value of "2008P"
should have a rank of zero. All of the other projects should be ranked
against each other according to their score.

I tried adding AND Stag.MRE_PlanYear <> "2008P" to the subquery but that
didnt work. I also tried an IIF with the subquery as the ELSE parameter and
that didn't work either.

I'd appreciate any advice folks can send my way.

Thanks,
owp^3

 

[KARL DEWEY]

Try this ---
INSERT INTO
staging_StagingRank ( ProjectID, ProjectStatus, ProjectScore,
MRE_CurrentRank )
IIF( Stag.MRE_PlanYear = "2008P", 0, SELECT
select_MREStaging.MRE_ProjectID,
select_MREStaging.MRE_CurrentStatus,
select_MREStaging.MRE_Score,
(SELECT Count(*) FROM select_MREStaging AS Stag
WHERE Stag.MRE_Score > select_MREStaging.MRE_Score AND
Stag.MRE_CurrentStatus =
select_MREStaging.MRE_CurrentStatus)+1)
AS MRE_CurrentRank
FROM select_MREStaging;

 

[owp^3]

Thanks for the advice. Unfortunately, all I get with the query are syntax
errors.
It doesn't like the IIF in the INSERT INTO. I tried moving it into the
SELECT clause in a couple of different ways but get similar complaints. I
will try some more permutations using the IIF...

Here are two solutions I have that sort of work:
INSERT INTO staging_StagingRank ( ProjectID, ProjectStatus, ProjectScore,
MRE_CurrentRank )
SELECT select_MREStaging.MRE_ProjectID, select_MREStaging.MRE_CurrentStatus,
select_MREStaging.MRE_Score, (SELECT Count(*)
FROM select_MREStaging AS Stag
WHERE Stag.MRE_Score > select_MREStaging.MRE_Score AND
Stag.MRE_CurrentStatus =
select_MREStaging.MRE_CurrentStatus)+1 AS MRE_CurrentRank
FROM select_MREStaging
WHERE select_MREStaging.MRE_PlanYear<>"2008P"

OR

INSERT INTO staging_StagingRank ( ProjectID, ProjectStatus, ProjectScore,
MRE_CurrentRank )
SELECT select_MREStaging.MRE_ProjectID, select_MREStaging.MRE_CurrentStatus,
select_MREStaging.MRE_Score, IIF( select_MREStaging.MRE_PlanYear = "2008P",
0,
(SELECT Count(*) FROM select_MREStaging AS Stag
WHERE
Stag.MRE_Score > select_MREStaging.MRE_Score AND
Stag.MRE_CurrentStatus =
select_MREStaging.MRE_CurrentStatus)+1) AS MRE_CurrentRank
FROM select_MREStaging;

Both produce nearly identical results. The MRE_Current Rank Values for the
first are NULL, NULL, NULL, ... , NULL, 2, 4, 11, 13, ... , n. In the second
the NULLS are zeroes.

My desired result set is 0, 0, 0, ..., 0, 1, 2, 3, ... , n.

So, I am still wrestling with the problem.

 

[owp^3]

I ended up creating 6 queries select_2008CFno, select_2008CFno_Rank,
select_2008CFyes, select_2008CFyes_Rank, append_StagingRank1, and
append_StagingRank2. There is probably a cleaner way to do this but at least
I got it to work. Sometimes you just have to completely deconstruct the
problem.

Anyway here are the SQL statements...

SELECT select_MREStaging.*
FROM select_MREStaging
WHERE (((select_MREStaging.MRE_PlanYear)<>"2008P"));

SELECT select_2008CFno.MRE_ProjectID, select_2008CFno.MRE_CurrentStatus,
select_2008CFno.MRE_Score, select_2008CFno.MRE_PlanYear,
select_2008CFno.MRE_PMOConsultant,
(SELECT Count(*)
FROM select_2008CFno AS SRC
WHERE SRC.MRE_Score > select_2008CFno.MRE_Score AND
SRC.MRE_CurrentStatus =
select_2008CFno.MRE_CurrentStatus)+1
AS MRE_CurrentRank
FROM select_2008CFno

SELECT select_MREStaging.*
FROM select_MREStaging
WHERE (((select_MREStaging.MRE_PlanYear)="2008P"));

SELECT select_2008CFyes.MRE_ProjectID, select_2008CFyes.MRE_CurrentStatus,
select_2008CFyes.MRE_Score, select_2008CFyes.MRE_PlanYear,
select_2008CFyes.MRE_PMOConsultant, 0 AS MRE_CurrentRank
FROM select_2008CFyes;

INSERT INTO staging_StagingRank ( ProjectID, ProjectStatus, ProjectScore,
PlanYear,
PMOConsultant, CurrentRank )
SELECT select_2008CFyes_Rank.MRE_ProjectID,
select_2008CFyes_Rank.MRE_CurrentStatus,
select_2008CFyes_Rank.MRE_Score,
select_2008CFyes_Rank.MRE_PlanYear,
select_2008CFyes_Rank.MRE_PMOConsultant,
select_2008CFyes_Rank.MRE_CurrentRank
FROM select_2008CFyes_Rank
ORDER BY select_2008CFyes_Rank.MRE_CurrentStatus,
select_2008CFyes_Rank.MRE_Score DESC ,
select_2008CFyes_Rank.MRE_PMOConsultant;

INSERT INTO staging_StagingRank ( ProjectID, ProjectStatus, ProjectScore,
PlanYear,
PMOConsultant, CurrentRank )
SELECT select_2008CFno_Rank.MRE_ProjectID,
select_2008CFno_Rank.MRE_CurrentStatus,
select_2008CFno_Rank.MRE_Score,
select_2008CFno_Rank.MRE_PlanYear,
select_2008CFno_Rank.MRE_PMOConsultant,
select_2008CFno_Rank.MRE_CurrentRank
FROM select_2008CFno_Rank
ORDER BY select_2008CFno_Rank.MRE_CurrentStatus,
select_2008CFno_Rank.MRE_Score DESC ,
select_2008CFno_Rank.MRE_PMOConsultant;

I'd appreciate any feedback on this solution.

Thanks,
owp^3