We are on Cold Fusion version 11 update 6.
We are running SQL2014 SP1
I need to report amounts offered by award, program, level and if they have enrolled in a course for the selected year (transact.sch_yr) or not. If this is not the proper way to achieve my results I am willing to change!
SELECT award.awd_txt, name.soc_sec, name.level_, nmprg.prg_cod, /*transact.semester,*/ 0 AS Enrl_Undgr_Cnt, 0 AS Ofr_Undgr_Cnt, 0 AS Enrl_Undgr_TR_Cnt, 0 AS Ofr_Undgr_TR_Cnt, 0 AS Enrl_Undgr_Amt, 0 AS Ofr_Undgr_Amt, 0 AS Enrl_Ungr_TR_Amt, 0 AS Ofr_Undgr_TR_Amt,
/* Registered Returning Students Amount */
CASE WHEN nmprg.prg_cod <> 'UNGRDTR' AND name.level_ < '3' AND MAX(nmcrs.sch_yr) = transact.sch_yr THEN SUM(transact.offered) ELSE 0 END AS Enrl_FRSO_Amt,
/* Not Registered Students Amount */
CASE WHEN nmprg.prg_cod <> 'UNGRDTR' AND name.level_ < '3' AND MAX(nmcrs.sch_yr) <> transact.sch_yr THEN SUM(transact.offered) ELSE 0 END AS Ofr_FRSO_Amt
FROM award INNER JOIN transact ON transact.awd_id = award.awd_cod
INNER JOIN rpt_schy ON transact.sch_yr = rpt_schy.sch_yr
INNER JOIN name ON transact.soc_sec = name.soc_sec
INNER JOIN nmcrs ON name.soc_sec = nmcrs.soc_sec
INNER JOIN nmprg ON name.soc_sec = nmprg.soc_sec
WHERE nmprg.active = '1'
AND nmcrs.transfer = '0'
AND transact.accept <> 'v'
AND transact.tcodes = 'AW'
AND award.award_type_type /* = 3 OR award.award_type_type*/ = 6
AND (name.camp_cod='Eureka' OR name.camp_cod='TBD')
AND name.last_name not like '%Teste%'
AND rpt_schy.token = '#session.token#'
AND NOT(transact.soc_sec IN
(SELECT DISTINCT finaideurekaawd.soc_sec
FROM finaideurekaawd
WHERE
rpt_schy.token = '#session.token#'
AND transact.soc_sec = finaideurekaawd.soc_sec
AND finaideurekaawd.sch_years = rpt_schy.sch_yr))
GROUP BY award.awd_txt, name.soc_sec, transact.sch_yr, name.level_, nmprg.prg_cod
With this query if a student has 10 classes (different nmcrs.sch_yrs) the amount offered would be multiplied 10 times.
1
Reply
AdChoices