Card
unknown
plain_text
2 years ago
887 B
8
Indexable
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
Editor is loading...