--  File:     ISOL6_1.SQL
--            Microsoft TPC-H Benchmark Kit Ver. 2.14.0-1010
--            Copyright Microsoft, 2010, 2011
--
-- Implemented trunc(x,2) as ROUND(x,2,1)
--
-- Currently the DELTA is 0 to maximize the runtime for the query.
-- Modify the 'WHERE' clause to use a different value.
--

--	ACID CODE BEGIN
print ' '
print 'TPC-H ACID Query (S1:Q1) Start:'
select convert(char(30), getdate(),9)
print ' '
--	ACID CODE END

-- Do 4 self joins to prolong the runnning time
SELECT	l1.L_RETURNFLAG,
	l1.L_LINESTATUS,
	SUM(l1.L_QUANTITY)					AS SUM_QTY,
	SUM(l1.L_EXTENDEDPRICE)				AS SUM_BASE_PRICE,
	SUM(l1.L_EXTENDEDPRICE*(1-l1.L_DISCOUNT))		AS SUM_DISC_PRICE,
	SUM(l1.L_EXTENDEDPRICE*(1-l1.L_DISCOUNT)*(1+l1.L_TAX))	AS SUM_CHARGE,
	AVG(l1.L_QUANTITY	)				AS AVG_QTY,
	AVG(l1.L_EXTENDEDPRICE)				AS AVG_PRICE,
	AVG(l1.L_DISCOUNT)					AS AVG_DISC,
	COUNT(*)					AS COUNT_ORDER
FROM	LINEITEM l1, LINEITEM l2, LINEITEM l3,LINEITEM l4, LINEITEM l5,LINEITEM l6, LINEITEM l7,LINEITEM l8,LINEITEM l9, LINEITEM l10, LINEITEM l11
WHERE	
	l1.L_ORDERKEY = l2.L_ORDERKEY and l1.L_LINENUMBER = l2.L_LINENUMBER
        and l2.L_ORDERKEY = l3.L_ORDERKEY and l2.L_LINENUMBER < l3.L_LINENUMBER
        and l3.L_ORDERKEY = l4.L_ORDERKEY and l3.L_LINENUMBER < l4.L_LINENUMBER
	and l5.L_ORDERKEY = l4.L_ORDERKEY and l5.L_LINENUMBER < l4.L_LINENUMBER
	and l5.L_ORDERKEY = l6.L_ORDERKEY and l5.L_LINENUMBER < l6.L_LINENUMBER
	and l6.L_ORDERKEY = l7.L_ORDERKEY and l6.L_LINENUMBER < l7.L_LINENUMBER
	and l7.L_ORDERKEY = l8.L_ORDERKEY and l7.L_LINENUMBER < l8.L_LINENUMBER
	and l8.L_ORDERKEY = l9.L_ORDERKEY and l8.L_LINENUMBER < l9.L_LINENUMBER
	and l9.L_ORDERKEY = l10.L_ORDERKEY and l9.L_LINENUMBER < l10.L_LINENUMBER
	and l10.L_ORDERKEY = l11.L_ORDERKEY and l10.L_LINENUMBER < l11.L_LINENUMBER
GROUP	BY l1.L_RETURNFLAG,
	   l1.L_LINESTATUS
ORDER	BY l1.L_RETURNFLAG,
	   l1.L_LINESTATUS


--	ACID CODE BEGIN
print ' '
print 'TPC-H ACID Query (S1:Q1) End:'
select convert(char(30), getdate(),9)
print ' '
--	ACID CODE END

GO

--repeat

--	ACID CODE BEGIN
print ' '
print 'TPC-H ACID Query (S1:Q2) Start:'
select convert(char(30), getdate(),9)
print ' '
--	ACID CODE END

-- Do 4 self joins to prolong the runnning time
SELECT	l1.L_RETURNFLAG,
	l1.L_LINESTATUS,
	SUM(l1.L_QUANTITY)					AS SUM_QTY,
	SUM(l1.L_EXTENDEDPRICE)				AS SUM_BASE_PRICE,
	SUM(l1.L_EXTENDEDPRICE*(1-l1.L_DISCOUNT))		AS SUM_DISC_PRICE,
	SUM(l1.L_EXTENDEDPRICE*(1-l1.L_DISCOUNT)*(1+l1.L_TAX))	AS SUM_CHARGE,
	AVG(l1.L_QUANTITY	)				AS AVG_QTY,
	AVG(l1.L_EXTENDEDPRICE)				AS AVG_PRICE,
	AVG(l1.L_DISCOUNT)					AS AVG_DISC,
	COUNT(*)					AS COUNT_ORDER
FROM	LINEITEM l1, LINEITEM l2, LINEITEM l3,LINEITEM l4, LINEITEM l5,LINEITEM l6, LINEITEM l7,LINEITEM l8,LINEITEM l9
WHERE	
	l1.L_ORDERKEY = l2.L_ORDERKEY and l1.L_LINENUMBER = l2.L_LINENUMBER
        and l2.L_ORDERKEY = l3.L_ORDERKEY and l2.L_LINENUMBER < l3.L_LINENUMBER
        and l3.L_ORDERKEY = l4.L_ORDERKEY and l3.L_LINENUMBER < l4.L_LINENUMBER
	and l5.L_ORDERKEY = l4.L_ORDERKEY and l5.L_LINENUMBER < l4.L_LINENUMBER
	and l5.L_ORDERKEY = l6.L_ORDERKEY and l5.L_LINENUMBER < l6.L_LINENUMBER
	and l6.L_ORDERKEY = l7.L_ORDERKEY and l6.L_LINENUMBER < l7.L_LINENUMBER
	and l7.L_ORDERKEY = l8.L_ORDERKEY and l7.L_LINENUMBER < l8.L_LINENUMBER
	and l8.L_ORDERKEY = l9.L_ORDERKEY and l8.L_LINENUMBER < l9.L_LINENUMBER
GROUP	BY l1.L_RETURNFLAG,
	   l1.L_LINESTATUS
ORDER	BY l1.L_RETURNFLAG,
	   l1.L_LINESTATUS


--	ACID CODE BEGIN
print ' '
print 'TPC-H ACID Query (S1:Q2) End:'
select convert(char(30), getdate(),9)
print ' '
--	ACID CODE END

GO


--repeat

--	ACID CODE BEGIN
print ' '
print 'TPC-H ACID Query (S1:Q3) Start:'
select convert(char(30), getdate(),9)
print ' '
--	ACID CODE END

-- Do 4 self joins to prolong the runnning time
SELECT	l1.L_RETURNFLAG,
	l1.L_LINESTATUS,
	SUM(l1.L_QUANTITY)					AS SUM_QTY,
	SUM(l1.L_EXTENDEDPRICE)				AS SUM_BASE_PRICE,
	SUM(l1.L_EXTENDEDPRICE*(1-l1.L_DISCOUNT))		AS SUM_DISC_PRICE,
	SUM(l1.L_EXTENDEDPRICE*(1-l1.L_DISCOUNT)*(1+l1.L_TAX))	AS SUM_CHARGE,
	AVG(l1.L_QUANTITY	)				AS AVG_QTY,
	AVG(l1.L_EXTENDEDPRICE)				AS AVG_PRICE,
	AVG(l1.L_DISCOUNT)					AS AVG_DISC,
	COUNT(*)					AS COUNT_ORDER
FROM	LINEITEM l1, LINEITEM l2, LINEITEM l3,LINEITEM l4, LINEITEM l5,LINEITEM l6, LINEITEM l7,LINEITEM l8,LINEITEM l9
WHERE	
	l1.L_ORDERKEY = l2.L_ORDERKEY and l1.L_LINENUMBER = l2.L_LINENUMBER
        and l2.L_ORDERKEY = l3.L_ORDERKEY and l2.L_LINENUMBER < l3.L_LINENUMBER
        and l3.L_ORDERKEY = l4.L_ORDERKEY and l3.L_LINENUMBER < l4.L_LINENUMBER
	and l5.L_ORDERKEY = l4.L_ORDERKEY and l5.L_LINENUMBER < l4.L_LINENUMBER
	and l5.L_ORDERKEY = l6.L_ORDERKEY and l5.L_LINENUMBER < l6.L_LINENUMBER
	and l6.L_ORDERKEY = l7.L_ORDERKEY and l6.L_LINENUMBER < l7.L_LINENUMBER
	and l7.L_ORDERKEY = l8.L_ORDERKEY and l7.L_LINENUMBER < l8.L_LINENUMBER
	and l8.L_ORDERKEY = l9.L_ORDERKEY and l8.L_LINENUMBER < l9.L_LINENUMBER
GROUP	BY l1.L_RETURNFLAG,
	   l1.L_LINESTATUS
ORDER	BY l1.L_RETURNFLAG,
	   l1.L_LINESTATUS


--	ACID CODE BEGIN
print ' '
print 'TPC-H ACID Query (S1:Q3) End:'
select convert(char(30), getdate(),9)
print ' '
--	ACID CODE END

GO

--	ACID CODE BEGIN
print ' '
print 'TPC-H ACID Query (S1:Q4) Start:'
select convert(char(30), getdate(),9)
print ' '

-- Do 4 self joins to prolong the runnning time
SELECT	l1.L_RETURNFLAG,
	l1.L_LINESTATUS,
	SUM(l1.L_QUANTITY)					AS SUM_QTY,
	SUM(l1.L_EXTENDEDPRICE)				AS SUM_BASE_PRICE,
	SUM(l1.L_EXTENDEDPRICE*(1-l1.L_DISCOUNT))		AS SUM_DISC_PRICE,
	SUM(l1.L_EXTENDEDPRICE*(1-l1.L_DISCOUNT)*(1+l1.L_TAX))	AS SUM_CHARGE,
	AVG(l1.L_QUANTITY	)				AS AVG_QTY,
	AVG(l1.L_EXTENDEDPRICE)				AS AVG_PRICE,
	AVG(l1.L_DISCOUNT)					AS AVG_DISC,
	COUNT(*)					AS COUNT_ORDER
FROM	LINEITEM l1, LINEITEM l2, LINEITEM l3,LINEITEM l4, LINEITEM l5,LINEITEM l6, LINEITEM l7,LINEITEM l8,LINEITEM l9, LINEITEM l10
WHERE	
	l1.L_ORDERKEY = l2.L_ORDERKEY and l1.L_LINENUMBER = l2.L_LINENUMBER
        and l2.L_ORDERKEY = l3.L_ORDERKEY and l2.L_LINENUMBER < l3.L_LINENUMBER
        and l3.L_ORDERKEY = l4.L_ORDERKEY and l3.L_LINENUMBER < l4.L_LINENUMBER
	and l5.L_ORDERKEY = l4.L_ORDERKEY and l5.L_LINENUMBER < l4.L_LINENUMBER
	and l5.L_ORDERKEY = l6.L_ORDERKEY and l5.L_LINENUMBER < l6.L_LINENUMBER
	and l6.L_ORDERKEY = l7.L_ORDERKEY and l6.L_LINENUMBER < l7.L_LINENUMBER
	and l7.L_ORDERKEY = l8.L_ORDERKEY and l7.L_LINENUMBER < l8.L_LINENUMBER
	and l8.L_ORDERKEY = l9.L_ORDERKEY and l8.L_LINENUMBER < l9.L_LINENUMBER
	and l9.L_ORDERKEY = l10.L_ORDERKEY and l9.L_LINENUMBER < l10.L_LINENUMBER
GROUP	BY l1.L_RETURNFLAG,
	   l1.L_LINESTATUS
ORDER	BY l1.L_RETURNFLAG,
	   l1.L_LINESTATUS


--	ACID CODE BEGIN
print ' '
print 'TPC-H ACID Query (S1:Q4) End:'
select convert(char(30), getdate(),9)
print ' '
--	ACID CODE END

--	ACID CODE BEGIN
print ' '
print 'TPC-H ACID Query (S1:Q5) Start:'
select convert(char(30), getdate(),9)
print ' '

-- Do 4 self joins to prolong the runnning time
SELECT	l1.L_RETURNFLAG,
	l1.L_LINESTATUS,
	SUM(l1.L_QUANTITY)					AS SUM_QTY,
	SUM(l1.L_EXTENDEDPRICE)				AS SUM_BASE_PRICE,
	SUM(l1.L_EXTENDEDPRICE*(1-l1.L_DISCOUNT))		AS SUM_DISC_PRICE,
	SUM(l1.L_EXTENDEDPRICE*(1-l1.L_DISCOUNT)*(1+l1.L_TAX))	AS SUM_CHARGE,
	AVG(l1.L_QUANTITY	)				AS AVG_QTY,
	AVG(l1.L_EXTENDEDPRICE)				AS AVG_PRICE,
	AVG(l1.L_DISCOUNT)					AS AVG_DISC,
	COUNT(*)					AS COUNT_ORDER
FROM	LINEITEM l1, LINEITEM l2, LINEITEM l3,LINEITEM l4, LINEITEM l5,LINEITEM l6, LINEITEM l7,LINEITEM l8,LINEITEM l9, LINEITEM l10
WHERE	
	l1.L_ORDERKEY = l2.L_ORDERKEY and l1.L_LINENUMBER = l2.L_LINENUMBER
        and l2.L_ORDERKEY = l3.L_ORDERKEY and l2.L_LINENUMBER < l3.L_LINENUMBER
        and l3.L_ORDERKEY = l4.L_ORDERKEY and l3.L_LINENUMBER < l4.L_LINENUMBER
	and l5.L_ORDERKEY = l4.L_ORDERKEY and l5.L_LINENUMBER < l4.L_LINENUMBER
	and l5.L_ORDERKEY = l6.L_ORDERKEY and l5.L_LINENUMBER < l6.L_LINENUMBER
	and l6.L_ORDERKEY = l7.L_ORDERKEY and l6.L_LINENUMBER < l7.L_LINENUMBER
	and l7.L_ORDERKEY = l8.L_ORDERKEY and l7.L_LINENUMBER < l8.L_LINENUMBER
	and l8.L_ORDERKEY = l9.L_ORDERKEY and l8.L_LINENUMBER < l9.L_LINENUMBER
	and l9.L_ORDERKEY = l10.L_ORDERKEY and l9.L_LINENUMBER < l10.L_LINENUMBER
GROUP	BY l1.L_RETURNFLAG,
	   l1.L_LINESTATUS
ORDER	BY l1.L_RETURNFLAG,
	   l1.L_LINESTATUS


--	ACID CODE BEGIN
print ' '
print 'TPC-H ACID Query (S1:Q5) End:'
select convert(char(30), getdate(),9)
print ' '
--	ACID CODE END


GO