SQLTeam.com | Weblogs | Forums

MS SQL - stored procedure results

sql2014

#1

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