-- $Id: q22-tpch12.sql 2657 2007-06-12 16:08:15Z rdempsey $
-- 22nd query in 100 GB stream0 (tpch12).
SELECT 
	L_SHIPMODE,
	SUM( CASE
			WHEN O_ORDERPRIORITY = '1-URGENT' 
				OR O_ORDERPRIORITY = '2-HIGH'
			THEN 1
			ELSE 0
	END) AS HIGH_LINE_COUNT,
	SUM( CASE 
			WHEN O_ORDERPRIORITY <> '1- URGENT' AND
			O_ORDERPRIORITY <> '2-HIGH'
			THEN 1
			ELSE 0
	END) AS LOW_LINE_COUNT
FROM 
	ORDERS,
	LINEITEM
WHERE 
	O_ORDERKEY = L_ORDERKEY AND
	L_SHIPMODE IN ('FOB','SHIP') AND
	L_COMMITDATE < L_RECEIPTDATE AND
	L_SHIPDATE < L_COMMITDATE AND
	L_RECEIPTDATE >= date '1994-01-01' AND
	L_RECEIPTDATE < date '1994-01-01' + interval '1' year
GROUP BY 
	L_SHIPMODE
ORDER BY 
	L_SHIPMODE;