select
CONVERT(VARCHAR(10), orders.orderdate, 101) as 'Date',
orders.patientid as 'Pt ID',
(SELECT CASE WHEN EXISTS (select orderid from orderitems where itemid in (250,249) and orderitems.orderid = orders.orderid) THEN 'EXAM' ELSE '' END) AS 'Exam',
(SELECT CASE WHEN EXISTS (select orderid from orderitems where itemtype = 'F' and itemid is not null and orderitems.orderid = orders.orderid) THEN 'FRAME' ELSE '' END) AS 'Frame',
(SELECT CASE WHEN EXISTS (select orderid from orderitems where itemtype = 'S' and description like '%system%' and orderitems.orderid = orders.orderid) THEN 'LENS' ELSE '' END) AS 'Lens'
from orders
where ordertypeid in (999,9,1026) ---999 = Other, 1026 = Spec Lens, 9 = Frame
and (orderstatusid not in (24,26,2944,1141,69) --Excludes bad orders
OR orderstatusid is null) --no status OK for exams
and orderdate between '2015-01-01' and '2015-12-31'
and remake_orderid is null
order by patientid