I'm somewhat new to SQL (using it in the past but only being exposed to it heavily in my current role). Unfortunately nobody at my current company has really given me any advice on formatting. How can I format this better / what should I avoid doing that I may have done below? Any feedback on re-factoring would be greatly appreciated also.
/* Uncomment when testing this SQL */ declare @period_id as int = 252 -- ### Potentially redundant ### declare @building_id as varchar(10) = '20500' declare @section_name as varchar(20) = 'ALL' declare @lease_expiry_period as int = 1; /* SQL for Section_Name parameter on report */ select 'ALL', 1 as order_by union all select distinct upper(isnull(section_name, '')) as section_name, 2 as order_by from property.lease_period where section_name <> '' order by 2; declare @truncateddate as date = dateadd(d,0,datediff(d,0,getdate())); /* SQL for Report (dsDetail) */ select lp.period_id , lp.building_id , lp.suite_id , lp.lease_id , lp.tenant_trading_name, lp.suite_status , lp.suite_name , lp.suite_area, lp.lease_status , lp.lease_current_start_date , lp.lease_current_stop_date , lp.lease_current_term , lp.lease_occupancy_date , lp.lease_vacated_date from property.lease_period lp where lp.lease_current_stop_date < @truncateddate and (upper(lp.lease_status) = 'ACTIVE' or upper(lp.lease_status) = 'OVERHOLDING') --and lp.period_id = @period_id -- ##Potentially redundant (query now running into the future) and lp.building_id = @building_id and not exists ( select 1 from lease_deal.lease l where lp.building_id = l.building_id and lp.lease_id = l.lease_id ) and (@section_name = 'ALL' or (@section_name <> 'ALL' and upper(section_name) = upper(@section_name))) and lp.lease_current_stop_date between @truncateddate and dateadd(MONTH, @lease_expiry_period, @truncateddate) order by period_id desc