I have a stored procedure that I inherited.
The goal is for the data in the temp table: #multi_nominees_uf to be joined with the data in #temp_reviewers_UF and assign #temp_reviewers_UF.uf_rev_id to #multi_nominees_uf.application_number where the corresponding uf_rev_id's short_plan does not match the major.
The following has to be in place:
short_plan can not match the major(associated with the uf_rev_id)
count of each uf_rev_id can only be in the table a certain number of times (@Temp).
Also, the uf_rev_id will be in the #temp_reviewers_uf table more than once with a different short_plan, it should only be looking at DISTINCT uf_rev_id when calculating the @RevPerRevieweruf.
The way it is written now, the counts are not consistent. One uf_rev_ID may have 122 records and another may have 50 - each distinct uf_rev_id should have the same count (or very close).
Any ideas of the best way to accomplish this?? Any input is appreciated.
Sample Data
CREATE TABLE #mult_nominees_uf(
appnum VARCHAR(8)
,major VARCHAR(8)
,compid INT
);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('00012345','ACT',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('10002343','BBC',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('10002777','BBC',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('10000023','DED',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('23457829','AAR',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('78954321','RRE',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('90002342','ACT',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('11156726','AAR',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('88855593','RRE',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('10000001','DED',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('20000393','ACT',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('11119999','DED',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('78927626','AAR',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('67589393','RRE',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('12453647','AAR',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('00012345','ACT',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('10002343','BBC',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('10002777','BBC',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('10000023','DED`',2);
INSERT INTO #mult_nominees_uf(appnum,major,compid) VALUES ('23457829','AAR',2);
--with this sample data the @RevsPerReviewerUF count would be 4 since A5 is listed twice and we only want distinct values used
CREATE TABLE #Temp_Reviewers_uf(
uf_rev_id VARCHAR(8)
,short_plan VARCHAR(8)
,fac_emplid INTEGER
);
INSERT INTO #Temp_Reviewers_uf(uf_rev_id,short_plan,fac_emplid) VALUES ('A1','ACT',00000012);
INSERT INTO #Temp_Reviewers_uf(uf_rev_id,short_plan,fac_emplid) VALUES ('A2','BBC',00000145);
INSERT INTO #Temp_Reviewers_uf(uf_rev_id,short_plan,fac_emplid) VALUES ('A3','DED',10002934);
INSERT INTO #Temp_Reviewers_uf(uf_rev_id,short_plan,fac_emplid) VALUES ('A5','RRE',90001223);
INSERT INTO #Temp_Reviewers_uf(uf_rev_id,short_plan,fac_emplid) VALUES ('A5','ACT',90001223);
Stored Procedure
DECLARE @Index INT
DECLARE @Num_nomineesUF INT
DECLARE @Num_reviewersUF INT
DECLARE @Num_reviewersUFDISTINCT INT
DECLARE @Num_reviews INT
DECLARE @Rev_ID nvarchar(25), @Nom_ID varchar(8), @Short_Plan varchar(8), @Major varchar(8)
DECLARE @RevsPerReviewerUF INT
SET @Num_reviews = 4
DECLARE @UFCount
DECLARE @actualCompID int
DECLARE @UF_Flag INT
DECLARE @InsertNum int
SET @InsertNum = 1
create table #mult_nominees_UF (appNumber varchar(8), Major varchar(8), comp_id INT)
create table #TempNomineeTable (uf_rev_id varchar(8), fac_emplid varchar(9), appNumber varchar(8), Major varchar(8), short_plan varchar(8), comp_id int)
create table #Temp_Reviewers_UF (uf_rev_id varchar(8), short_plan varchar(8), fac_emplid varchar(9)) -- temp table used to hold Nom_IDs already assigned to Rev_IDs
set @actualCompID = 21
* * SELECT APPLICATION NUMBER & MAJOR FROM FS_RESULTS TABLE * * * * * --
select appNumber, LEFT(Major, CHARINDEX('-', Major)-1) as Major, comp_id into #Delete_nomineesUF
from FS_Results
where UF='Y'
and comp_id = @actualCompID
and nominated=1;
SET @Num_nomineesUF = @@rowcount; --GET RECORD COUNT
IF (@Num_nomineesUF > 0)
BEGIN
SET @UF_Flag = 1;
END
SET @Index = 1 ; -- reinit variable
WHILE @Index <= 4 BEGIN
if (@UF_Flag > 0)
BEGIN
INSERT into #mult_nominees_uf
select * from #Delete_nomineesUF
END
select * from #mult_nominees_uf order by [Application Number]
Set @MN_UFCnt = @@ROWCOUNT
-- Create temp table for UF Reviewers
select uf_rev_id, short_plan, fac_emplid into #temp_reviewers_UF
from ReviewersID_ShortPlan
where uf_rev_id like 'UF%'
and competition_id = @actualCompID
SET @Num_reviewersUF = @@rowcount
SELECT DISTINCT UF_REV_ID FROM ReviewersID_ShortPlan WHERE UF_REV_ID like 'UF%' AND competition_id = @actualCompID
SET @Num_reviewersUFDistinct = @@rowcount
SET @RevsPerReviewerUF = (@Num_nomineesUF * @Num_reviews) / nullif(@Num_reviewersUFDistinct,0)
SET @Temp = (@MN_UFCnt / @Num_reviewersUFDistinct);
WITH Match_NomineesWithReviewers AS(
SELECT DISTINCT
appNumber,
RTRIM(Major) AS Major,
COUNT(1) as rowcnt,
comp_id
FROM #mult_nominees_uf
GROUP BY appNumber,
RTRIM(Major),
comp_id
)
, rownum_matches AS (
SELECT m.appNumber,
m.Major,
t.short_plan,
t.uf_rev_id,
t.fac_emplid,
m.rowcnt,
m.comp_id,
ROW_NUMBER() OVER (PARTITION BY m.appNumber order by newid()) AS rownum
FROM Match_NomineesWithReviewers m
JOIN #temp_reviewers_UF t ON t.short_plan != m.major
GROUP BY m.appNumber, m.Major, t.short_plan,
t.uf_rev_id, t.fac_emplid, m.rowcnt, m.comp_id
HAVING COUNT(t.uf_rev_id) <= @Temp
)
INSERT INTO #TempNomineeTable
SELECT uf_rev_id, fac_emplid, appNumber, Major, short_plan, null, 0, null, comp_id FROM rownum_matches rm
WHERE rownum <= rowcnt
group by uf_rev_id, fac_emplid, appNumber, Major, short_plan, comp_id
HAVING COUNT(uf_rev_id) <= @Temp