Hello I need some help writing a SQL query which will group by a variable while taking the max of something where something is true. Sounds much simpler than it really is, so I'll jump right to my query... proc sql;
create table INSPN_FY as
select distinct
a.HUB_Name,
b.PROP_ID,
b.PROP_NAME,
c.INSPN_FISC_YR,
e.FISC_YR as ASMT_FISC_YR,
c.INSPECTION_ID,
c.INSPECTION_CD,
c.INSPECTION_SCORE
from HUB_TBL a INNER JOIN PROPERTY_TBL b ON a.HUB_ID = b.HUB_ID
INNER JOIN
INSPECTION_TBL c ON c.PROP_ID = b.PROP_ID
LEFT JOIN INSPECTION_ASSESSMENT d ON c.INSPECTION_ID = d.INSPECTION_ID
LEFT JOIN ASSESSMENT_TBL e on d.ASMT_ID = e.ASMT_ID and d.GRP_ID = e.GRP_ID and d.VER_ID = e.VER_ID
where e.FISC_YR <> . and
c.INSPECTION_ID >=500000 and c.INSPECTION_ID <1000000 and
c.INSPECTION_CD in('RTN','IRA', 'VUR', 'AWP', 'VUI')
and c.PROP_ID is not null and
(c.INSPECTION_SCORE is not null OR c.INSPECTION_CD in( 'VUI'))
and b.PROPERTY_UNIT_CNT > 0
ORDER BY a.HUB_Name,
b.PROP_ID,
b.PROP_NAME,
c.INSPN_FISC_YR,
e.FISC_YR ;
quit; I want to select inspections for properties by assessment FY, where the conditions in the WHERE clause are met. However, some cases have 2 inspections in the same assessment year, one with a VUI status and one released, such as: PROPERTY_ID PROPERTY_UNIT_CNT INSPN_FISC_YR ASMT_FISC_YR INSPECTION_ID INSPECTION_CODE XX999999 28 2016 2017 550505 RTN XX999999 28 2017 2017 690908 VUI And some have two released, such as: PROPERTY_ID PROPERTY_UNIT_CNT INSPN_FISC_YR ASMT_FISC_YR INSPECTION_ID INSPECTION_CD XX999999 74 2016 2017 570707 RTN XX999999 74 2017 2017 680808 RTN In the first case, I want the RTN, while in the second case I want the MAX inspection ID. However, if the only inspection for a FY is VUI, I still want it (only omit them if there is a duplicative released). How can I do this in 1 SQL step? I need it in 1 step because I plan to have this table automated in an ETL. SQL only please.
... View more
