Cacher is the code snippet organizer for pro developers

--
--Returns net revenue for the time period
--
select SUM(paymentamount) as 'Total Dollars' 
from payments 
where paymenttype <> 'Write-Off'
AND (paymentdate <= '12-31-2016') --To Date
AND (paymentdate >= '01-01-2016') --From Date

--
--Returns net sales for the timeframe for frames, lenses, lens options, and frame parts
--Ignores quotes
--
SELECT ( 
	SELECT isnull(SUM(case when insurance_supplied = 'N' or insurance_supplied is null then isnull(orderitems.retailprice * orderitems.quantity, 0) else isnull(patient_pays, 0) + isnull(copay, 0) - isnull(tax, 0) - isnull(tax2, 0) + isnull(insurancedue, 0) + isnull(discount, 0) + isnull(write_off_amount, 0) end), 0)
	FROM orderitems, orders
	WHERE (orders.locationid = isnull(15 , orders.locationid )) 
		and ( orderitems.orderid = orders.orderid ) 
		and ( orderitems.itemtype = 'S' ) 
		AND ( isNull(orders.orderstatusid, 0) not in (select domainid from domain where isQuote = 'Yes' and domaintypeid = 86)) 
		and ( orderitems.orderitemsdate >= '2016-01-01 00:00:00.000' ) 
		AND ( orderitems.orderitemsdate <= '2016-12-31 00:00:00.000' ) 
		--) as spectacles_gross ,
		--LENSES Gross
		) +
		( 
	SELECT isnull(SUM(case when insurance_supplied = 'N' or insurance_supplied is null then isnull(orderitems.retailprice * orderitems.quantity, 0) else isnull(patient_pays, 0) + isnull(copay, 0) - isnull(tax, 0) - isnull(tax2, 0) + isnull(insurancedue, 0) + isnull(discount, 0) + isnull(write_off_amount, 0) end), 0)
	FROM orderitems, orders 
	WHERE (orders.locationid = isnull(15 , orders.locationid )) 
		and ( orderitems.orderid = orders.orderid ) 
		and ( isNull(orders.orderstatusid, 0) not in (select domainid from domain where isQuote = 'Yes' and domaintypeid = 86)) 
		and ( orderitems.itemtype = 'F' ) 
		and	( orderitems.orderitemsdate >= '2016-01-01 00:00:00.000' ) 
		AND ( orderitems.orderitemsdate <= '2016-12-31 00:00:00.000' )
		-- ) as frames_gross ,
		--FRAMES Gross
		) +
		( 
	SELECT isnull(SUM(case when insurance_supplied = 'N' or insurance_supplied is null then isnull(orderitems.retailprice * orderitems.quantity, 0) else isnull(patient_pays, 0) + isnull(copay, 0) - isnull(tax, 0) - isnull(tax2, 0) + isnull(insurancedue, 0) + isnull(discount, 0) + isnull(write_off_amount, 0) end), 0)
	FROM orderitems, orders 
	WHERE (orders.locationid = isnull(15 , orders.locationid )) 
		and ( orderitems.orderid = orders.orderid ) 
		and ( isNull(orders.orderstatusid, 0) not in (select domainid from domain where isQuote = 'Yes' and domaintypeid = 86)) 
		and ( orderitems.itemtype = 'P' ) 
		and ( orderitems.orderitemsdate >= '2016-01-01 00:00:00.000' ) 
		AND ( orderitems.orderitemsdate <= '2016-12-31 00:00:00.000' ) 
		--) as frameparts_gross,
		--Frame Parts Gross
		) +
		( 
	SELECT isnull(SUM(case when insurance_supplied = 'N' or insurance_supplied is null then isnull(orderitems.retailprice * orderitems.quantity, 0) else isnull(patient_pays, 0) + isnull(copay, 0) - isnull(tax, 0) - isnull(tax2, 0) + isnull(insurancedue, 0) + isnull(discount, 0) + isnull(write_off_amount, 0) end), 0)
	FROM orderitems, orders 
	WHERE (orders.locationid = isnull(15 , orders.locationid )) 
		and ( orderitems.orderid = orders.orderid ) 
		and ( orderitems.itemtype = 'O' ) 
		and ( orderitems.lenstreatmentind = 'L' ) 
		and ( isNull(orders.orderstatusid, 0) not in (select domainid from domain where isQuote = 'Yes' and domaintypeid = 86)) 
		and ( orderitems.orderitemsdate >= '2016-01-01 00:00:00.000' ) 
		AND ( orderitems.orderitemsdate <= '2016-12-31 00:00:00.000' ) 
		--) as miscellaneous_lenstreatment_gross --LensTreatment,
		--Lens Treatment Gross
		) -
		( 
	SELECT isnull( sum(orderitems.discount) + sum(isnull(orderitems.write_off_amount,0)), 0) 
	FROM orderitems, orders
	WHERE (orders.locationid = isnull(15 , orders.locationid )) 
		and	( orderitems.orderid = orders.orderid ) 
		and ( orderitems.itemtype = 'S' ) 
		AND ( isNull(orders.orderstatusid, 0) not in (select domainid from domain where isQuote = 'Yes' and domaintypeid = 86)) 
		and ( orderitems.orderitemsdate >= '2016-01-01 00:00:00.000' ) 
		AND ( orderitems.orderitemsdate <= '2016-12-31 00:00:00.000' ) 
		--) as spectacles_discount,
		--Lens Discounts
		) -
		( 
	SELECT isnull( sum(orderitems.discount) + sum(isnull(orderitems.write_off_amount,0)), 0) 
	FROM orderitems, orders 
	WHERE (orders.locationid = isnull(15 , orders.locationid )) 
		and ( orderitems.orderid = orders.orderid ) 
		and ( isNull(orders.orderstatusid, 0) not in (select domainid from domain where isQuote = 'Yes' and domaintypeid = 86)) 
		and ( orderitems.itemtype = 'F' ) 
		and	( orderitems.orderitemsdate >= '2016-01-01 00:00:00.000' ) 
		AND ( orderitems.orderitemsdate <= '2016-12-31 00:00:00.000' ) 
		--) as frames_discount,
		--Frames Discounts
		) -
		( 
	SELECT isnull( sum(isnull(orderitems.discount, 0)) + sum(isnull(orderitems.write_off_amount,0)), 0) 
	FROM orderitems,orders 
	WHERE (orders.locationid = isnull(15 , orders.locationid )) 
		and ( orderitems.orderid = orders.orderid ) 
		and ( isNull(orders.orderstatusid, 0) not in (select domainid from domain where isQuote = 'Yes' and domaintypeid = 86)) 
		and ( orderitems.itemtype = 'P' ) 
		and ( orderitems.orderitemsdate >= '2016-01-01 00:00:00.000' ) 
		AND ( orderitems.orderitemsdate <= '2016-12-31 00:00:00.000' ) 
		--) as frameparts_discount,
		--Frame Parts Discounts
		) -
		( 
	SELECT isnull( sum(orderitems.discount) + sum(isnull(orderitems.write_off_amount,0)), 0) 
	FROM orderitems, orders 
	WHERE (orders.locationid = isnull(15 , orders.locationid )) 
	and ( orderitems.orderid = orders.orderid ) 
	and ( orderitems.itemtype = 'O' ) 
	and ( orderitems.lenstreatmentind = 'L' ) 
	and ( isNull(orders.orderstatusid, 0) not in (select domainid from domain where isQuote = 'Yes' and domaintypeid = 86)) 
	and ( orderitems.orderitemsdate >= '2016-01-01 00:00:00.000' ) 
	AND ( orderitems.orderitemsdate <= '2016-12-31 00:00:00.000' ) 
	--) as miscellaneous_lenstreatment_discount,
	--Lens Treatment Discounts
	) as 'Optical Net Sales'

--
--Gross Sales less Discounts, ignores quotes
--Similar to Sales & Payment Summary Report
--
SELECT ( 
	SELECT isnull(SUM(case when insurance_supplied = 'N' or insurance_supplied is null then isnull(orderitems.retailprice * orderitems.quantity, 0) else isnull(patient_pays, 0) + isnull(copay, 0) - isnull(tax, 0) - isnull(tax2, 0) + isnull(insurancedue, 0) + isnull(discount, 0) + isnull(write_off_amount, 0) end), 0)
	FROM orderitems, orders 
	WHERE (orders.locationid = isnull(15 , orders.locationid )) 
		and ( orderitems.orderid = orders.orderid ) 
		and ( orderitems.itemtype = 'C' ) 
		and ( isNull(orders.orderstatusid, 0) not in (select domainid from domain where isQuote = 'Yes' and domaintypeid = 86)) 
		and ( orderitems.orderitemsdate >= '2016-01-01 00:00:00.000' ) 
		AND ( orderitems.orderitemsdate <= '2016-12-31 00:00:00.000' ) 
	--) as contacts_soft_gross ,
	) -
	( 
	SELECT isnull(sum(orderitems.discount) + sum(isnull(orderitems.write_off_amount,0)), 0) 
	FROM orderitems, orders 
	WHERE (orders.locationid = isnull(15 , orders.locationid )) 
		and ( orderitems.orderid = orders.orderid ) 
		and ( orderitems.itemtype = 'C' ) 
		and ( isNull(orders.orderstatusid, 0) not in (select domainid from domain where isQuote = 'Yes' and domaintypeid = 86)) 
		and ( orderitems.orderitemsdate >= '2016-01-01 00:00:00.000' ) 
		AND ( orderitems.orderitemsdate <= '2016-12-31 00:00:00.000' ) 
		--) as contacts_soft_discount
		) as 'Contact Net Sales'