CREATE PROCEDURE SPSLS_NEASRpts
(
@nUIC char(5),
@nRptID char(9),
@nIsAdmin char(1),
@nAll char(1),
@nParent char(1)
)
AS
DECLARE @vUIC char(5)
DECLARE @vRptID char(9)
DECLARE @vIsAdmin char(1)
DECLARE @vAll char(1)
DECLARE @vParent char(1)
SET @vUIC = @nUIC
SET @vRptID = @nRptID
SET @vIsAdmin = @nIsAdmin
SET @vAll = @nAll
SET @vParent = @nParent
DECLARE @SQL varchar(8000)
SET @SQL = ''
SET @SQL = @SQL + ' SELECT DISTINCT '
SET @SQL = @SQL + ' R.rptid, R.rptdiary, R.rptuic, R.rptcycle, R.rptpg, R.rptdate, U.UIC, '
SET @SQL = @SQL + 'CASE WHEN EXISTS (SELECT * FROM orders.dbo.tbl_Addresses T '
SET @SQL = @SQL + 'WHERE (T.Parent_UIC = U.UIC) and (T.Parent_UIC <> T.UIC)) '
SET @SQL = @SQL + 'THEN ''P'' '
SET @SQL = @SQL + 'ELSE '''' '
SET @SQL = @SQL + 'END AS PARENT, '
SET @SQL = @SQL + 'U.PLAD AS ShortTitle, '
SET @SQL = @SQL + 'CC.RAAUTH AS CC_RAAUTH, '
SET @SQL = @SQL + ' (UPPER(LEFT(DATENAME(MONTH,(LEFT(CC.EXAMDATE,2)+''/''+SUBSTRING(CC.EXAMDATE,3,2)+''/''+RIGHT(CC.EXAMDATE,2))),3))+'' ''+RIGHT(CC.EXAMDATE,2)) as CC_FSTMOPR,'
SET @SQL = @SQL + 'C.cycle, speedie_date1, diary_ctrl, uic_ctrl, ra_sort_code AS rsc, date_of_adv AS doa, epg, drate, nam, ssn, erate1, series, serial, '
SET @SQL = @SQL + 'brclgroup, brcl_prof, prate1, tir, pc, nec_grp_cde AS necg, pro_gain_dup AS pgd, puic, dspg, std_score AS ss, perfac1, '
SET @SQL = @SQL + 'perf_mark1, tir_factor1 AS tirf1, awards, pna_factor1 AS pnaf1, cc_pna_ss1 AS cps1, cc_pna_perf1 AS cpp1, cc_pna_sum AS cps, strik_cut, '
SET @SQL = @SQL + 'final_mult1 AS fm1, fm_cut1, erlyind, pc1_cyc+pc2_cyc+pc3_cyc+pc4_cyc+pc5_cyc AS prevcyc, '
SET @SQL = @SQL + 'pc1_pts+pc2_pts+pc3_pts+pc4_pts+pc5_pts AS prevpts, total_pna_cc1 AS tpc1, '
SET @SQL = @SQL + 'rs1+rs2+rs3+rs4+rs5+rs6+rs7+rs8+rs9+rs10+rs11+rs12+rs13+rs14+rs15+rs16+rs17+rs18+rs19+rs20 AS raw_score, '
SET @SQL = @SQL + 'perc1+perc2+perc3+perc4+perc5+perc6+perc7+perc8+perc9+perc10+perc11+perc12+perc13+perc14+perc15+perc16+perc17+perc18+perc19+perc20 AS persent, '
SET @SQL = @SQL + 'arate1, spec_grp_ind AS sgi, sipg, tas, dtis, school_code AS sc, cuic, speedie_type AS sdt, speedie_datime AS sdd, '
SET @SQL = @SQL + 'ovrall_pct AS oap, status AS st, status1 AS s1, status2 AS s2, status3 AS s3, status4 AS s4, status5 AS s5, status6 AS s6, YY '
SET @SQL = @SQL + 'FROM NEASRPTSINDEX R '
SET @SQL = @SQL + 'JOIN orders.dbo.tbl_Addresses U ON ( R.rptuic = U.UIC ) '
SET @SQL = @SQL + 'or ((''' + @vAll + '''= ''y'' ) and (''' + @vUIC + ''' = U.Parent_UIC)) '
SET @SQL = @SQL + 'RIGHT OUTER '
SET @SQL = @SQL + 'JOIN orders.dbo.cyclemaster CC ON ( CC.CYCLE = R.rptcycle ) and '
SET @SQL = @SQL + '(((R.rptpg = ''7'' ) and (CC.PAYGRADE = ''E7'' )) or '
SET @SQL = @SQL + '((R.rptpg = ''_'' ) and '
SET @SQL = @SQL + '(CC.Cycle+CC.PAYGRADE IN (Select cycle+paygrade '
SET @SQL = @SQL + 'from orders.dbo.cyclemaster '
SET @SQL = @SQL + 'where (paygrade <> ''E7'') and '
SET @SQL = @SQL + '(RESULTSVIEW <> (Select case ''' + @vIsAdmin + ''' when ''Y'' '
SET @SQL = @SQL + 'then '''' '
SET @SQL = @SQL + 'else ''NV'' end)))))) '
SET @SQL = @SQL + 'LEFT OUTER '
SET @SQL = @SQL + 'JOIN NEASRPTS C ON ( C.cycle = R.rptcycle ) and '
SET @SQL = @SQL + '( speedie_date1 = R.rptdate ) and ( uic_ctrl = U.UIC ) and '
SET @SQL = @SQL + '(((epg <> ''7'' ) and (epg LIKE R.rptpg )) or ((epg = ''7'' ) and (R.RPTPG = ''7'' ))) '
SET @SQL = @SQL + 'WHERE R.rptid =' + @vRptID
SET @SQL = @SQL + 'ORDER BY U.UIC '
--select (@SQL)
exec(@SQL)
GO
