--    File:    DUR_VERIFY1.SQL                                                   
--             Microsoft TPC-H Kit Ver. 2.14.0-1010
--             Copyright Microsoft, 2010, 2011                              
--

--  Durability Verification test 1                         
--  ------------------------------                         
--                                             
--  Comment:  ORDERS.O_TOTALPRICE should equal LINEITEM sums

print ' '
print 'Timestamp for Pre-Failure Durability Verification 1 start:'
select convert(char(30), getdate(),9)
print ' '

if exists ( select name from sysobjects where name = 'temp_dur1_output' )
        drop table temp_dur1_output
GO

CREATE	TABLE	temp_dur1_output
				(	o_orderkey	bigint,
					o_totalprice	money,
					l_orderkey	bigint,
					lineitem_sum	money
				)

INSERT INTO temp_dur1_output
	SELECT	DISTINCT(o.O_ORDERKEY),
		o.O_TOTALPRICE,
		l.L_ORDERKEY,
		CAST(SUM(CAST(CAST(CAST(CAST(CAST(CAST(L_EXTENDEDPRICE as decimal(20,3))*100 as bigint) as decimal(20,3)) * (1- CAST(L_DISCOUNT as decimal(20,3))) as bigint) * (1 + CAST(L_TAX as decimal(20,3))) as bigint) as decimal(20,3))/100) as decimal(20,3))
	FROM	acid_values a INNER JOIN ORDERS o
	ON	a.acid_o_key = o.O_ORDERKEY JOIN LINEITEM l
	ON	o.O_ORDERKEY = l.L_ORDERKEY
	WHERE	a.acid_test = 'Consistency_Durability'
	GROUP	BY o.O_ORDERKEY, o.O_TOTALPRICE, l.L_ORDERKEY
	ORDER	BY o.O_ORDERKEY ASC


-- Select data from temporary table...
print ' '
print 'Select all rows from temporary table'
print ' '
SELECT	o_orderkey 'O_OrderKey',
	o_totalprice 'O_TotalPrice',
	l_orderkey 'L_OrderKey',
	lineitem_sum 'LineItem Sum'
FROM	temp_dur1_output

print ' '
print 'Select any rows where o_totalprice is not equal to lineitem_sum'
print ' '
SELECT	*
FROM	temp_dur1_output
WHERE	o_totalprice <> lineitem_sum

print ' '
print 'Count the History rows'
print ' '
SELECT	count(*)
FROM	HISTORY_DUR


print ' '
print 'Timestamp for Durability 1 end:'
select convert(char(30), getdate(),9)
print ' '

if exists ( select name from sysobjects where name = 'temp_dur1_output' )
        drop table temp_dur1_output
GO



