I need to report counts by program, level and if they have enrolled in a course for the selected year. With the below query I receive counts for both the enrolled and offered columns when a student is enrolled in the selected year and also previous years. It does not seem to select only the MAX(nmcrs.sch_yr). If this is not the proper way to achieve my results I am willing to change!
SELECT distinct award.awd_cod, name.soc_sec, award.awd_txt, MAX(nmcrs.sch_yr),
/* Registered Returning Students Count */
SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ < '3' AND nmcrs.sch_yr = transact.sch_yr then 1 ELSE 0 END)) AS Enrl_FRSO_Cnt,
SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ = '3' AND nmcrs.sch_yr = transact.sch_yr then 1 ELSE 0 END)) AS Enrl_JR_Cnt,
SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ > '3' AND nmcrs.sch_yr = transact.sch_yr then 1 ELSE 0 END)) AS Enrl_SR_Cnt,
SUM(distinct (case when nmprg.prg_cod = 'UNGRDTR' AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS Enrl_TR_Cnt,
/* Registered Returning Students Amount */
SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ < '3' AND nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS Enrl_FRSO_Amt,
SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ = '3' AND nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS Enrl_JR_Amt,
SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ > '3' AND nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS Enrl_SR_Amt,
SUM(distinct (case when nmprg.prg_cod = 'UNGRDTR' AND nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS Enrl_TR_Amt,
/* Not Registered Returning Students Count */
SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ < '3' AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS Ofr_FRSO_Cnt,
SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ = '3' AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS Ofr_JR_Cnt,
SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ > '3' AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS Ofr_SR_Cnt,
SUM(distinct (case when nmprg.prg_cod = 'UNGRDTR' AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS Ofr_TR_Cnt,
/* Not Registered Returning Students Amount */
SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ < '3' AND nmcrs.sch_yr <> transact.sch_yr then transact.offered ELSE 0 END)) AS Ofr_FRSO_Amt,
SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ = '3' AND nmcrs.sch_yr <> transact.sch_yr then transact.offered ELSE 0 END)) AS Ofr_JR_Amt,
SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ > '3' AND nmcrs.sch_yr <>transact.sch_yr then transact.offered ELSE 0 END)) AS Ofr_SR_Amt,
SUM(distinct (case when nmprg.prg_cod = 'UNGRDTR' AND nmcrs.sch_yr <> transact.sch_yr then transact.offered ELSE 0 END)) AS Ofr_TR_Amt
FROM award INNER JOIN transact ON award.awd_cod = transact.awd_id
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#'
GROUP BY name.soc_sec, award.awd_cod, award.awd_txt
ORDER BY award.awd_txt, name.soc_sec
I sum the counts and amounts in my report.
AdChoices