WITH tpa AS (--###################################--AUDIT DETAILS AND ITEMS--################################# SELECT ad.audit_id, ad.loc_code, ad.status, ad.created_at, ad.start_date, ad.end_date, ad.audit_type, ad.audited_by, ai.item, ai.item_desc, ai.stock_on_hand, ai.physical_count, l.loc_name, l.loc_type, l.format, l.dc_city, --COALESCE(NULLIF(REPLACE(variance::text,'NaN','0'), ''), '0')::float AS variance, COALESCE(NULLIF(variance, ''),'0')::float AS variance, ai.approval_status, ils.av_cost, ad.audited_by, NULLIF(ad.audited_by,'') IS NOT NULL auditor_flag, count(date(start_date)) over (PARTITION by loc_code)/count(ai.item) over (PARTITION by loc_code) as audit_days FROM fnvportal_prod.stockaudit_auditdetail ad INNER JOIN fnvportal_prod.stockaudit_auditem ai ON ad.id = ai.audit_ref_id_id LEFT JOIN tran.soh ils ON ils.loc=ad.loc_code AND ils.item=ai.item AND ils.date=ad.start_date join master.item i on ai.item = i.item join master.loc l on ai.loc = l.loc WHERE ad.status='Completed' AND LOWER(ai.approval_status)='accept' AND date_trunc('month',ad.start_date)>=dateadd('month',-{{trailing_months}},date_trunc('month', current_date-1)) and (close_date is null and inactive_date is null) ORDER BY ad.start_date DESC) SELECT loc_type, auditor_flag, date_trunc('month',start_date) as month, SUM(variance*av_cost) as variance_val, sum(stock_on_hand*av_cost) as inv_val, round(variance_val*1e2/inv_val,1) as var_perc FROM tpa GROUP BY 1,2,3

with inv_adj as ( select date(created_at) as dt,location as loc ,ia.item, sum(case when reason = '87' then -1 * quantity else quantity end) as adj_qty_t from fnvportal_prod.fnvportalapp_inventoryadjustment ia join master.item i on ia.item = i.item where date(created_at)>=dateadd('month',-{{trailing_months}},date_trunc('month', current_date-1)) and approval_status = 'AP' and rtv_file_name = '' group by 1,2,3), soh as (select date, item,loc, sum(stock_on_hand*av_cost) as inv_t,avg(av_cost) as avg_cost_t from tran.soh where date >= dateadd('month',-{{trailing_months}},date_trunc('month', current_date-1)) group by 1,2,3) select * from soh left join inv_adj on soh.date = inv_adj.dt and soh.loc = inv_adj.loc and soh.item = inv_adj.item join master.loc l on soh.loc = l.loc

ddes