Cacher is the code snippet organizer for pro developers

SELECT 
order_detail.frame_source,
--order_detail.orderid,
CASE order_detail.collectionname
WHEN '7759' THEN 'Silhouette'
WHEN 'Minimal X Kids' THEN 'Silhouette'
	WHEN 'Urban Fusion' THEN 'Silhouette'
	WHEN 'Titan Next Generation III' THEN 'Silhouette'
	WHEN 'Titan Dynamics Nylor' THEN 'Silhouette'
	WHEN 'Polaroid Premiu Collection' THEN 'Polaroid Premium Collection'
	WHEN 'Urban Lite' THEN 'Silhouette'
	WHEN 'Titan Accent' THEN 'Silhouette'
	WHEN 'Base-X' THEN 'Silhouette'
	WHEN 'Titan Minimal Art Pulse' THEN 'Silhouette'
	WHEN 'Titan Accent Flora' THEN 'Silhouette'
	WHEN '7799' THEN 'Silhouette'
	WHEN 'Alpha 34' THEN 'OGA Collection'
	WHEN 'Alpha 27' THEN 'OGA Collection'
	WHEN 'Alpha 22' THEN 'OGA Collection'
	WHEN 'Alpha 30' THEN 'OGA Collection'
	WHEN 'Baseline Collection' THEN 'Safety Frames'
	WHEN 'Boulevard Boutique New Dawn Collection' THEN 'Medicaid - NYE'
	WHEN 'TitanProfile' THEN 'Silhouette'
	WHEN 'CarbonT1' THEN 'Silhouette'
	WHEN 'Compose' THEN 'Silhouette - Adidas'
	WHEN 'Copenhagen 3' THEN 'OGA Collection'
	WHEN 'Day Lite' THEN 'Silhouette'
	WHEN 'Enhance' THEN 'Medicaid - NYE'
	WHEN 'Falk III' THEN 'OGA Collection'
	WHEN 'Inspired 2D' THEN 'Silhouette - Adidas'
	WHEN 'Lazair' THEN 'Silhouette - Adidas'
	WHEN 'Lite Fit' THEN 'Silhouette - Adidas'
	WHEN 'Lite Fit Youth' THEN 'Silhouette - Adidas'
	WHEN 'Dahlia' THEN 'Koali Collection'
	WHEN 'Nepenthe' THEN 'Koali Collecton'
	WHEN 'Nepenthe 3' THEN 'Koali Collecton'
	WHEN 'Oakley Polarized' THEN 'Oakley Sunglasses'
	WHEN 'Op-Ocean Pacific Kids Collection' THEN 'Op-Ocean Pacific Collection'
	WHEN 'Performance Sport' THEN 'Silhouette - Adidas'
	WHEN 'Performance Sport Golf' THEN 'Silhouette - Adidas'
	WHEN 'Silhouette Collection' THEN 'Silhouette'
	WHEN 'SPX Connection' THEN 'Silhouette'
	WHEN 'SPX Illusion' THEN 'Silhouette'
	WHEN 'streamlite' THEN 'Silhouette - Adidas'
	WHEN 'FYSH UK Collection' THEN 'FYSH UK - Westgroupe/Eyewear'
	WHEN 'Titan' THEN 'Silhouette'
	WHEN 'Titan Dynamics Half Rim' THEN 'Silhouette'
	WHEN 'Titan Elements' THEN 'Silhouette'
	WHEN 'Titan Minimal Art Collection' THEN 'Silhouette'
	WHEN 'Titan Profile' THEN 'Silhouette'
	WHEN 'TNG III' THEN 'Silhouette'
	WHEN 'TNG Nylor' THEN 'Silhouette'
	WHEN 'Tulipe' THEN 'Koali Collecton'
	WHEN 'Yucca' THEN 'Koali Collecton'
	WHEN 'Zenlight' THEN 'Silhouette'
	WHEN 'Alpha 23' THEN 'OGA Collection'
	WHEN 'Crystal Diva' THEN 'Silhouette'
	WHEN 'F8 Collection' THEN 'Liberty Sport'
	WHEN 'Focus Collection' THEN 'Medicaid - Focus'
	WHEN 'Illusion Nylor' THEN 'Silhouette'
	WHEN 'Jelly Bean Collection' THEN 'Medicaid - Jelly Bean'
	WHEN 'Jubilee Collection' THEN 'Medicaid - NYE'
	WHEN 'Liberty Sport Sun Performance Collection' THEN 'Liberty Sport'
	WHEN 'Lite Twist' THEN 'Silhouette'
	WHEN 'SPX Signia Carbon' THEN 'Silhouette'
	WHEN 'Delta 1A US' THEN 'Lightec Collection'
	WHEN 'LT Collection' THEN 'Lightec Collection'
	WHEN 'Oakley' THEN 'Oakley Ophthalmic'
	WHEN 'Oakley Frame' THEN 'Oakley Ophthalmic'
	WHEN 'Performance Youth' THEN 'Silhouette - Adidas'
	WHEN 'Rec Specs Collection' THEN 'Liberty Sport'
	WHEN 'Shapelite' THEN 'Silhouette - Adidas'
	WHEN 'Silhouette Radiance' THEN 'Silhouette'
	WHEN 'SPX Art Kids' THEN 'Silhouette'
	WHEN 'SPX Art+' THEN 'Silhouette'
	WHEN 'SPX Match' THEN 'Silhouette'
	WHEN 'SPX SIGNIA' THEN 'Silhouette'
	WHEN 'SPX Signia Nylor' THEN 'Silhouette'
	WHEN 'SuperFlex Collection' THEN 'SuperFlex'
	WHEN 'SuperFlex Kids Collection' THEN 'SuperFlex'
	WHEN 'Titan Contour' THEN 'Silhouette'
	WHEN 'Titan Dynamics' THEN 'Silhouette'
	WHEN 'Titan Harmony' THEN 'Silhouette'
	WHEN 'Titan Next Generation' THEN 'Silhouette'
	WHEN 'Titan next generaton nylor' THEN 'Silhouette'
	WHEN 'TMA Must' THEN 'Silhouette'
	WHEN 'Via Roma Collection' THEN 'Medicaid - NYE'
ELSE order_detail.collectionname
END as collectionname,
CASE order_detail.manufacturername 
	WHEN 'I-dealoptics' THEN 'Medicaid'
	WHEN 'Hart-New York Eye' THEN 'Medicaid'
	WHEN 'Oakley' THEN 'Oakley, Inc.'
	WHEN 'Superflex - Westgroupe' THEN 'Westgroupe'
	WHEN 'FYSH UK - Westgroupe' THEN 'Westgroupe'
	WHEN 'KLiiK:denmark - Westgroupe' THEN 'Westgroupe'
	ELSE order_detail.manufacturername
END as 'manufacturer',
order_detail.framename, order_detail.productgroupname frametype,
--order_detail.shape, 
order_detail.colordescription as color, order_detail.colornumber, 
--order_detail.eyesize, order_detail.bridgesize, order_detail.a, order_detail.b, order_detail.ed, order_detail.templelength, 
orderitems.retailprice, orderitems.quantity, 
--orderitems.tax, orderitems.tax2, 
orderitems.itemcost, orderitems.discount, 
remake_orderid,
order_detail.upc_frame,
-- HIDES REMAKES
(select domain.domain from domain where domain.domainid = orderitems.discount_reasonid and domain.domain not in ('Remake')) as discount_reason,
--order_detail.material,
--(select top 1 (quantitypurchased - quantitysold) from inventorylocation where producttypeind = 'F' and inventorylocation.productid = orderitems.itemid and inventorylocation.locationid = orders.locationid) onhand,
(select max(inventorytransactions.daterecieved) from inventorytransactions where producttypeind = 'F' and inventorytransactions.productid = orderitems.itemid and inventorytransactions.locationid = orders.locationid and inventorytransactions.transactiontypeind = 'Received Product') daterecieved,
(select companyname from insurance where id = orders.insuranceid) insurance_name,
--(select suppliername from suppliers where suppliers.supplierid = orders.labid) labname,
orderitems.itemid,
orderitems.orderitemsdate,
--isnull(mycompanyinformation.company_code + ': ', '') + mycompanyinformation.companyname companyname,
--contacts.contactid, contacts.title, contacts.firstname, contacts.middlename, contacts.lastname, contacts.addressline1, contacts.addressline2, contacts.city, contacts.stateorprovince, contacts.zipcode, contacts.workphone, contacts.homephone, contacts.faxnumber, contacts.emailname,
--order_detail.gendertype,
--frameinventory.frame_usage,
--order_detail.productgroupname,
--(select firstname + ' ' + lastname from employee where employee.employeeid = orders.employeeid) as employee_name,
--dbo.uf_getdate() as print_date,
orders. orderedbyemployeeid
FROM {oj contacts RIGHT OUTER JOIN orders ON contacts.contactid = orders.patientid}, {oj orderitems LEFT OUTER JOIN frameinventory ON orderitems.itemid = frameinventory.frameid},
order_detail,
mycompanyinformation
WHERE orders.orderid = orderitems.orderid AND orders.orderid = order_detail.orderid
AND isNull(orders.orderstatusid, 0) NOT IN (SELECT domain.domainid FROM domain WHERE domain.isQuote = 'Yes' AND domain.domaintypeid = 86)
AND orderitems.itemtype = 'F'
AND orders.locationid = mycompanyinformation.setupid
AND orderitems.orderitemsid NOT IN ( SELECT orderitems_returned.orderitemsid
FROM orders orders_returned, orderitems orderitems_returned, orders_transaction
WHERE orderitems_returned.orderid = orders_returned.orderid
AND (orders_transaction.orderitemsid_original = orderitems_returned.orderitemsid OR orders_transaction.orderitemsid_corresponding = orderitems_returned.orderitemsid)
AND orderitems_returned.orderitemsid = orderitems.orderitemsid
AND orders_transaction.action = 'Return')
-- No redos
AND remake_orderid IS NULL
-- No patient frames or Supplied
AND order_detail.frame_source not in ('Supply','Patient','None')
AND orderitems.orderitemsid NOT IN ( SELECT orderitems_warranty.orderitemsid
	FROM orders orders_warranty, order_detail order_detail_warranty, orderitems orderitems_warranty, orders_transaction
	WHERE order_detail_warranty.orderid = orders_warranty.orderid
	AND orderitems_warranty.orderid = orders_warranty.orderid
	AND orderitems_warranty.orderitemsid = orders_transaction.orderitemsid_original
	AND orders_transaction.action = 'Warranty') AND orders.locationid = 15 AND orderitems.orderitemsdate >=  @DateFrom  AND orderitems.orderitemsdate <=  @DateTo