Card

SELECT p.wkyear::int yr, split_part(p.yrweek,'-',2)::int wk,					
  round(100*sum(CASE WHEN s.status ilike '%out of stock%' OR s.notes LIKE '%OOS -%' THEN 1 END)/					
            sum(CASE WHEN s.status NOT IN ('canceled','declined','preorder') THEN 1 END)::float, 2) AS oos_pct,					
  sum(CASE WHEN s.status ilike '%out of stock%' OR s.notes LIKE '%OOS -%' THEN 1 END)*100 AS oositems,					
  sum(CASE WHEN s.status NOT IN ('canceled','declined','preorder') THEN 1 END) AS allnofinds,					
  CASE WHEN f.report_siteflag IN ('F') THEN 'F' ELSE 'R' END AS siteflag					
FROM mars__revolveclothing_com___db.shipment s 					
INNER JOIN mars__id.id_yrweek p ON p.day = trunc(s.orderdate) 					
INNER JOIN bi_report.siteflag f ON f.ordertype = s.ordertype					
WHERE date_part(YEAR, s.orderdate)>=date_part(YEAR,CURRENT_DATE)-2					
GROUP BY 1,2,6 ORDER BY 1 DESC, 2 DESC, 6