R
If you do want only the highest prep_attempt for each ssn, removing the group by on prep_attempt will probably solve your issue.
given this sample
CREATE TABLE #prep
(ssn INT,
prep_attempt INT
);
insert into #prep(ssn,prep_attempt)
values(1,1),(1,2),(1,3),(2,1),(3,1),(3,2)
you would have an expected output of
1,3
2,1
3,2
so you would want this for your table expression
SELECT ssn,
MAX(prep_attempt) AS mx
FROM #prep
GROUP BY ssn;
This by itself is ok but when you self join the same table to get other columns as you do with [sw_prep_roster_cao 21-003] then you end up searching through the table twice. this could have performance implications on large tables.
I would suggest looking at the row_number window function. This would allow to get the highest prer_attempt with only 1 seek/scan of the table. Taking the example above you could do:
WITH cte
AS (SELECT ssn,
prep_attempt,
ROW_NUMBER() OVER(PARTITION BY ssn
ORDER BY prep_attempt DESC) AS rn
from #prep)
SELECT *
FROM cte
WHERE rn = 1;
we can incorporate this into your original query like so:
with cte as (SELECT t1.ssn,
t1.full_name_concat AS full_name,
t2.date_of_birth,
t2.gender,
t2.marital_status,
t2.zip_code,
t2.academic_level_name,
t2.academic_level_type,
t2.academic_method,
t1.prep_class AS RosterPrepClas,
t1.afsc,
t1.prep_roster_status,
t1.prep_attempt AS PrepRoster_Attempt,
t1.prep_entry,
t1.prep_start_date,
t1.prep_end_date,
t1.last_prep_outcome_date,
t3.weight AS inbody_wieght,
t3.height_full_inches AS inbody_height,
t3.test_date_time AS inbody_test_date_time,
t3.bmi_recalc AS inbody_bmi_recalc,
t4.prep_class AS OFT_prep_Class,
t4.uic AS oft_uic,
t4.roster AS oft_roster,
t4.afsc AS oft_afsc,
t4.broadjump_initial_inches AS oft_broadjump_initial_inches,
t4.agilityright_initial_seconds AS oft_agilityright_initial_seconds,
t4.deadlift_initial_pounds AS oft_deadlift_initial_pounds,
t4.pullups_initial AS oft_pullups_initial,
t4.farmerscarry_initial_seconds AS oft_farmerscarry_initial_seconds,
t4._300yardshuttle_initial_seconds AS oft_300yardshuttle_initial_seconds,
t4._1500mfin_initial_seconds AS oft__1500mfin_initial_seconds,
t4._3mileruck_initial_seconds AS oft_3mileruck_initial_seconds,
t4.initial_complete AS oft_initial_complete,
t5.prep_class AS past_prepclass,
t5.iteration AS past_iteration,
t5.pullups AS past_pullups,
t5.situps AS past_situps,
t5.pushups AS past_pushups,
t5.swim_time_secs AS past_swimtime,
t5.run_time_secs AS past_run_time,
row_number() over (partition by ssn order by prep_attempt desc ) as rn
FROM [sw_prep_roster_cao 21-003] t1
LEFT OUTER JOIN adss_demographiccao_20210414 t2
ON t1.ssn = t2.ssn
LEFT OUTER JOIN [dbo].[inbody_initial_clean_cao_prep_21002] t3
ON t1.ssn = t3.ssn
LEFT OUTER JOIN sw_prep_oft_test t4
ON t1.ssn = t4.ssn
LEFT OUTER JOIN [dbo].[swprep_past_cao21-002] t5
ON t1.ssn = t5.ssn
WHERE iteration = 'initial')
select *
from cte
where rn = 1
you can read up more on window functions in the doc for the over clause
