--    File:    Execute_Consistency_Test_1.SQL                                                   
--             Microsoft TPC-H Benchmark Kit Ver. 2.14.1008
--             Copyright Microsoft, 2010, 2011                  
--

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

print ' '
print 'Timestamp for Consistency 1 start:'
select convert(char(30), getdate(),9)
print ' '

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

CREATE	TABLE	temp_con1_output
				(	o_orderkey      int,
					o_totalprice    money,
					l_orderkey      int,
					lineitem_sum_1  money,
					lineitem_sum_2  money
				)

INSERT  INTO temp_con1_output
SELECT  DISTINCT(o.O_ORDERKEY),
        cast(o.O_TOTALPRICE as decimal(20,3)),
        l.L_ORDERKEY,
        cast(sum(cast(cast(cast(cast(cast(cast(L_EXTENDEDPRICE as decimal(20,3))*100 as integer) as decimal(20,3)) * (1- cast(L_DISCOUNT as decimal(20,3))) as integer) * (1 + cast(L_TAX as decimal(20,3))) as integer) as decimal(20,3))/100) as decimal(20,3)),
        (CAST(sum(cast(cast(cast(cast(cast(cast(L_EXTENDEDPRICE as decimal(20,3))* 100 as integer) as decimal(20,3)) * (1-cast(L_DISCOUNT as decimal(20,3))) as integer) * (1+cast(L_TAX as decimal(20,3))) as integer) as decimal(20,3))/100) as decimal(20,3))) - CAST(o.O_TOTALPRICE 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 AS 'O_OrderKey',
        o_totalprice AS 'O_TotalPrice',
        l_orderkey AS 'L_OrderKey',
        lineitem_sum_1 AS 'LineItem Sum #1',
        lineitem_sum_2 AS 'LineItem Sum #2'
FROM	temp_con1_output

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


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

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