[jira] [Commented] (HIVE-12779) Buffer underflow when inserting data to table

["Oleksiy Sayankin (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-12779?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15296115#comment-15296115 ] 

Oleksiy Sayankin commented on HIVE-12779:
-----------------------------------------

Steps to reproduce:

*STEP 1. Create tables temp.test with over 1 000 000 of rows*

Create test file test.csv with 1 000 000 lines of random test data of according types to table.

*STEP 2. Create folders in hdfs*

{code}
sudo -u mapr hadoop fs -mkdir /temp
sudo -u mapr hadoop fs -mkdir /temp/step5
sudo -u mapr hadoop fs -mkdir /temp/step6
{code}

*STEP 3 Create test table and upload data*

{code}
CREATE TABLE temp.test 
(id INT, a1 BIGINT, a2 BIGINT, b1 BOOLEAN, b2 BOOLEAN,
c1 DECIMAL(10,5), c2 DECIMAL(10,5), d1 DOUBLE, d2 DOUBLE, e1 FLOAT,
e2 FLOAT, f1 INT, f2 INT, g1 SMALLINT, g2 SMALLINT,
h1 STRING, h2 STRING, i1 TIMESTAMP, i2 TIMESTAMP, j1 TINYINT,
j2 TINYINT, k1 CHAR(22), k2 CHAR(22), l1 VARCHAR(22), l2 VARCHAR(22))
ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";
{code}

{code}
hadoop fs -put ~/test.csv /user/hive/warehouse/temp.db/test;
{code}

*STEP 4. Create tables temp.step5 and temp.step6 using queries below*

{code}
CREATE TABLE temp.step5
{code}

{code}
Create External Table if not exists temp.step5 (
trans_num decimal(10,5),
store_num bigint,
quantity double,
net_price double,
weight double,
operating_company string,
banner string,
product_id_hormel_rev3 int,
exposed_flag int,
tm_dim_key_week bigint,
experian_id bigint,
units int,
cents int,
baseline_units double,
baseline_cents double,
coupon int,
feature int,
display int,
totl_prc_reduc int,
feature_or_display int,
any_promo int,
coupon_occasions int,
feature_occasions int,
display_occasions int,
price_reduction_occasions int,
feature_or_display_occasions int,
any_promo_occasions int,
coupon_dollars double,
coupon_units int,
period int,
estimated_hh_income string,
state string,
county_code string,
latitude string,
longitude string,
cape_age_pop_pct_0_17 string,
cape_age_pop_pct_65_99_plus string,
cape_age_pop_pct_18_99_plus string,
cape_age_pop_median_age string,
cape_ethnic_pop_pct_white_only string,
cape_ethnic_pop_pct_black_only string,
cape_ethnic_pop_pct_asian_only string,
cape_ethnic_pop_pct_hispanic string,
cape_child_hh_pct_with_persons_lt18 string,
cape_child_hh_pct_marr_couple_famwith_persons_lt18 string,
cape_typ_hh_pct_married_couple_family string,
cape_tenancy_occhu_pct_owner_occupied string,
cape_tenancy_occhu_pct_renter_occupied string,
cape_hhsize_hh_average_household_size string,
cape_density_persons_per_hh_for_pop_in_hh string,
cape_homval_oohu_median_home_value string,
cape_hustr_hu_pct_mobile_home string,
cape_built_hu_median_housing_unit_age string,
cape_lang_hh_pct_spanish_speaking string,
cape_educ_pop25_plus_median_education_attained string,
cape_inc_hh_median_family_household_income string,
cape_educ_ispsa_decile string,
cape_inc_family_inc_state_decile string,
census_rural_urban_county_size_code string,
core_based_statistical_areas string,
person_1_birth_year_and_month string,
person_1_combined_age string,
person_1_gender string,
person_1_marital_status string,
recipient_reliability_code string,
household_composition string,
person_1_person_type string,
homeowner_combined_homeowner string,
homeowner_probability_model string,
dwelling_type string,
length_of_residence string,
dwelling_unit_size string,
number_of_children_in_living_unit string,
number_of_adults_in_living_unit string,
number_of_person_in_living_unit string,
mail_responder string,
mosaic_hh string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/temp/step5';
{code}

{code}
CREATE TABLE temp.step6
{code}

{code}
Create External Table if not exists temp.step6
(
experian_id bigint,                                  
exposed_flag int,                                   
banner STRING,                                        
Product_0_Quantity_PRE STRING,                        
Product_1_Quantity_PRE STRING,                       
Product_2_Quantity_PRE STRING,                       
Product_3_Quantity_PRE STRING,                       
Product_4_Quantity_PRE STRING,                       
Product_5_Quantity_PRE STRING,                       
Product_6_Quantity_PRE STRING,                       
Product_7_Quantity_PRE STRING,  
Product_8_Quantity_PRE STRING,              
Product_0_Quantity_POS STRING,

Product_1_Quantity_POS STRING,                       
Product_2_Quantity_POS STRING,                       
Product_3_Quantity_POS STRING,                       
Product_4_Quantity_POS STRING,                       
Product_5_Quantity_POS STRING,                       
Product_6_Quantity_POS STRING,                       
Product_7_Quantity_POS STRING,   
Product_8_Quantity_POS STRING,

Product_0_Net_Price_PRE STRING ,                      
Product_1_Net_Price_PRE STRING ,                      
Product_2_Net_Price_PRE STRING ,                      
Product_3_Net_Price_PRE STRING ,                      
Product_4_Net_Price_PRE STRING ,                      
Product_5_Net_Price_PRE STRING ,                      
Product_6_Net_Price_PRE STRING ,                      
Product_7_Net_Price_PRE STRING ,                      
Product_8_Net_Price_PRE STRING ,

Product_0_Net_Price_POS STRING ,                      
Product_1_Net_Price_POS STRING ,                      
Product_2_Net_Price_POS STRING ,                      
Product_3_Net_Price_POS STRING ,                      
Product_4_Net_Price_POS STRING ,                      
Product_5_Net_Price_POS STRING ,                      
Product_6_Net_Price_POS STRING ,                      
Product_7_Net_Price_POS STRING ,                      
Product_8_Net_Price_POS STRING ,

Coupon_Units_PRE_P0 STRING ,                          
Coupon_Units_PRE_P1 STRING ,                          
Coupon_Units_PRE_P2 STRING ,                          
Coupon_Units_PRE_P3 STRING ,                          
Coupon_Units_PRE_P4 STRING ,                          
Coupon_Units_PRE_P5 STRING ,                          
Coupon_Units_PRE_P6 STRING ,                          
Coupon_Units_PRE_P7 STRING ,  
Coupon_Units_PRE_P8 STRING ,         

Coupon_Units_POS_P0 STRING ,                          
Coupon_Units_POS_P1 STRING ,                          
Coupon_Units_POS_P2 STRING ,                          
Coupon_Units_POS_P3 STRING ,                          
Coupon_Units_POS_P4 STRING ,                          
Coupon_Units_POS_P5 STRING ,                          
Coupon_Units_POS_P6 STRING ,                          
Coupon_Units_POS_P7 STRING ,                          
Coupon_Units_POS_P8 STRING ,

Coupon_Dollars_PRE_P0   STRING ,                      
Coupon_Dollars_PRE_P1   STRING ,                      
Coupon_Dollars_PRE_P2   STRING ,                      
Coupon_Dollars_PRE_P3   STRING ,                      
Coupon_Dollars_PRE_P4   STRING ,                      
Coupon_Dollars_PRE_P5   STRING ,                      
Coupon_Dollars_PRE_P6   STRING ,                      
Coupon_Dollars_PRE_P7   STRING ,
Coupon_Dollars_PRE_P8   STRING ,                     

Coupon_Dollars_POS_P0   STRING ,                      
Coupon_Dollars_POS_P1   STRING ,                      
Coupon_Dollars_POS_P2   STRING ,                      
Coupon_Dollars_POS_P3   STRING ,                      
Coupon_Dollars_POS_P4   STRING ,                      
Coupon_Dollars_POS_P5   STRING ,                      
Coupon_Dollars_POS_P6   STRING ,                      
Coupon_Dollars_POS_P7   STRING ,
Coupon_Dollars_POS_P8   STRING ,                      

Dollars_per_Trip_PRE_P0   STRING ,                    
Dollars_per_Trip_PRE_P1   STRING ,                    
Dollars_per_Trip_PRE_P2   STRING ,                    
Dollars_per_Trip_PRE_P3   STRING ,                    
Dollars_per_Trip_PRE_P4   STRING ,                    
Dollars_per_Trip_PRE_P5   STRING ,                    
Dollars_per_Trip_PRE_P6   STRING ,                    
Dollars_per_Trip_PRE_P7   STRING ,
Dollars_per_Trip_PRE_P8   STRING ,                    

Dollars_per_Trip_POS_P0   STRING ,                    
Dollars_per_Trip_POS_P1   STRING ,                    
Dollars_per_Trip_POS_P2   STRING ,                    
Dollars_per_Trip_POS_P3   STRING ,                    
Dollars_per_Trip_POS_P4   STRING ,                    
Dollars_per_Trip_POS_P5   STRING ,                    
Dollars_per_Trip_POS_P6   STRING ,                    
Dollars_per_Trip_POS_P7   STRING ,   
Dollars_per_Trip_POS_P8   STRING ,                  

Units_per_Trip_PRE_P0    STRING ,                     
Units_per_Trip_PRE_P1    STRING ,                     
Units_per_Trip_PRE_P2    STRING ,                     
Units_per_Trip_PRE_P3    STRING ,                     
Units_per_Trip_PRE_P4    STRING ,                     
Units_per_Trip_PRE_P5    STRING ,                     
Units_per_Trip_PRE_P6    STRING ,                     
Units_per_Trip_PRE_P7    STRING ,  
Units_per_Trip_PRE_P8    STRING ,                   

Units_per_Trip_POS_P0    STRING ,                     
Units_per_Trip_POS_P1    STRING ,                     
Units_per_Trip_POS_P2    STRING ,                     
Units_per_Trip_POS_P3    STRING ,                     
Units_per_Trip_POS_P4    STRING ,                     
Units_per_Trip_POS_P5    STRING ,                     
Units_per_Trip_POS_P6    STRING ,                     
Units_per_Trip_POS_P7    STRING ,                     
Units_per_Trip_POS_P8    STRING ,

Trips_PRE_P0 STRING ,                                 
Trips_PRE_P1 STRING ,                                 
Trips_PRE_P2 STRING ,                                 
Trips_PRE_P3 STRING ,                                 
Trips_PRE_P4 STRING ,                                 
Trips_PRE_P5 STRING ,                                 
Trips_PRE_P6 STRING ,                                 
Trips_PRE_P7 STRING ,   
Trips_PRE_P8 STRING ,                              

Trips_POS_P0 STRING ,                                 
Trips_POS_P1 STRING ,                                 
Trips_POS_P2 STRING ,                                 
Trips_POS_P3 STRING ,                                 
Trips_POS_P4 STRING ,                                 
Trips_POS_P5 STRING ,                                 
Trips_POS_P6 STRING ,                                 
Trips_POS_P7 STRING ,                                 
Trips_POS_P8 STRING ,

Coupon_Dollars_per_Trip_Diff_PRE_POS_P1 STRING,      
Coupon_Dollars_per_Trip_Diff_PRE_POS_P2 STRING,      
Coupon_Dollars_per_Trip_Diff_PRE_POS_P3 STRING,      
Coupon_Dollars_per_Trip_Diff_PRE_POS_P4 STRING,      
Coupon_Dollars_per_Trip_Diff_PRE_POS_P5 STRING,      
Coupon_Dollars_per_Trip_Diff_PRE_POS_P6 STRING,      
Coupon_Dollars_per_Trip_Diff_PRE_POS_P7 STRING,  
Coupon_Dollars_per_Trip_Diff_PRE_POS_P8 STRING,  

Perc_Coupon_Dollars_P0_PRE  STRING ,                  
Perc_Coupon_Dollars_P1_PRE  STRING ,                  
Perc_Coupon_Dollars_P2_PRE  STRING ,                  
Perc_Coupon_Dollars_P3_PRE  STRING ,                  
Perc_Coupon_Dollars_P4_PRE  STRING ,                  
Perc_Coupon_Dollars_P5_PRE  STRING ,                  
Perc_Coupon_Dollars_P6_PRE  STRING ,                  
Perc_Coupon_Dollars_P7_PRE  STRING ,                  
Perc_Coupon_Dollars_P8_PRE  STRING ,

Perc_Coupon_Dollars_P0_POS  STRING ,                  
Perc_Coupon_Dollars_P1_POS  STRING ,                  
Perc_Coupon_Dollars_P2_POS  STRING ,                  
Perc_Coupon_Dollars_P3_POS  STRING ,                  
Perc_Coupon_Dollars_P4_POS  STRING ,                  
Perc_Coupon_Dollars_P5_POS  STRING ,                  
Perc_Coupon_Dollars_P6_POS  STRING ,                  
Perc_Coupon_Dollars_P7_POS  STRING ,   
Perc_Coupon_Dollars_P8_POS  STRING ,                

Coupon_Units_per_Trip_Diff_PRE_POS_P0   STRING,      
Coupon_Units_per_Trip_Diff_PRE_POS_P1   STRING,      
Coupon_Units_per_Trip_Diff_PRE_POS_P2   STRING,      
Coupon_Units_per_Trip_Diff_PRE_POS_P3   STRING,      
Coupon_Units_per_Trip_Diff_PRE_POS_P4   STRING,      
Coupon_Units_per_Trip_Diff_PRE_POS_P5   STRING,      
Coupon_Units_per_Trip_Diff_PRE_POS_P6   STRING,      
Coupon_Units_per_Trip_Diff_PRE_POS_P7   STRING,      
Coupon_Units_per_Trip_Diff_PRE_POS_P8   STRING,

Perc_Coupon_Units_P0_PRE  STRING ,                    
Perc_Coupon_Units_P1_PRE  STRING ,                    
Perc_Coupon_Units_P2_PRE  STRING ,                    
Perc_Coupon_Units_P3_PRE  STRING ,                    
Perc_Coupon_Units_P4_PRE  STRING ,                    
Perc_Coupon_Units_P5_PRE  STRING ,                    
Perc_Coupon_Units_P6_PRE  STRING ,                    
Perc_Coupon_Units_P7_PRE  STRING ,    
Perc_Coupon_Units_P8_PRE  STRING ,                

Perc_Coupon_Units_P0_POS  STRING ,                    
Perc_Coupon_Units_P1_POS  STRING ,                    
Perc_Coupon_Units_P2_POS  STRING ,                    
Perc_Coupon_Units_P3_POS  STRING ,                    
Perc_Coupon_Units_P4_POS  STRING ,                    
Perc_Coupon_Units_P5_POS  STRING ,                    
Perc_Coupon_Units_P6_POS  STRING ,                    
Perc_Coupon_Units_P7_POS  STRING ,  
Perc_Coupon_Units_P8_POS  STRING ,                  

Coupon_Trips_Share_Diff_PRE_POS_P0 STRING,           
Coupon_Trips_Share_Diff_PRE_POS_P1 STRING,           
Coupon_Trips_Share_Diff_PRE_POS_P2 STRING,           
Coupon_Trips_Share_Diff_PRE_POS_P3 STRING,           
Coupon_Trips_Share_Diff_PRE_POS_P4 STRING,           
Coupon_Trips_Share_Diff_PRE_POS_P5 STRING,           
Coupon_Trips_Share_Diff_PRE_POS_P6 STRING,           
Coupon_Trips_Share_Diff_PRE_POS_P7 STRING,  
Coupon_Trips_Share_Diff_PRE_POS_P8 STRING,          

Perc_Coupon_Trips__P0_PRE  STRING ,                 
Perc_Coupon_Trips__P1_PRE  STRING ,                 
Perc_Coupon_Trips__P2_PRE  STRING ,                 
Perc_Coupon_Trips__P3_PRE  STRING ,                 
Perc_Coupon_Trips__P4_PRE  STRING ,                 
Perc_Coupon_Trips__P5_PRE  STRING ,                 
Perc_Coupon_Trips__P6_PRE  STRING ,                 
Perc_Coupon_Trips__P7_PRE  STRING ,  
Perc_Coupon_Trips__P8_PRE  STRING ,               

Perc_Coupon_Trips__P0_POS  STRING ,                 
Perc_Coupon_Trips__P1_POS  STRING ,                 
Perc_Coupon_Trips__P2_POS  STRING ,                 
Perc_Coupon_Trips__P3_POS  STRING ,                 
Perc_Coupon_Trips__P4_POS  STRING ,                 
Perc_Coupon_Trips__P5_POS  STRING ,                 
Perc_Coupon_Trips__P6_POS  STRING ,                 
Perc_Coupon_Trips__P7_POS  STRING ,   
Perc_Coupon_Trips__P8_POS  STRING ,               

Feature_Trips_Share_Diff_PRE_POS_P0  STRING,         
Feature_Trips_Share_Diff_PRE_POS_P1  STRING,         
Feature_Trips_Share_Diff_PRE_POS_P2  STRING,         
Feature_Trips_Share_Diff_PRE_POS_P3  STRING,         
Feature_Trips_Share_Diff_PRE_POS_P4  STRING,         
Feature_Trips_Share_Diff_PRE_POS_P5  STRING,         
Feature_Trips_Share_Diff_PRE_POS_P6  STRING,         
Feature_Trips_Share_Diff_PRE_POS_P7  STRING,         
Feature_Trips_Share_Diff_PRE_POS_P8  STRING,

Perc_Feature_Trips_P0_PRE    STRING ,                 
Perc_Feature_Trips_P1_PRE    STRING ,                 
Perc_Feature_Trips_P2_PRE    STRING ,                 
Perc_Feature_Trips_P3_PRE    STRING ,                 
Perc_Feature_Trips_P4_PRE    STRING ,                 
Perc_Feature_Trips_P5_PRE    STRING ,                 
Perc_Feature_Trips_P6_PRE    STRING ,                 
Perc_Feature_Trips_P7_PRE     STRING ,   
Perc_Feature_Trips_P8_PRE     STRING ,                

Perc_Feature_Trips_P0_POS    STRING ,                 
Perc_Feature_Trips_P1_POS    STRING ,                 
Perc_Feature_Trips_P2_POS    STRING ,                 
Perc_Feature_Trips_P3_POS    STRING ,                 
Perc_Feature_Trips_P4_POS    STRING ,                 
Perc_Feature_Trips_P5_POS    STRING ,                 
Perc_Feature_Trips_P6_POS    STRING ,                 
Perc_Feature_Trips_P7_POS    STRING ,  
Perc_Feature_Trips_P8_POS    STRING ,                

Display_Trips_Share_Diff_PRE_POS_P0  STRING,         
Display_Trips_Share_Diff_PRE_POS_P1  STRING,         
Display_Trips_Share_Diff_PRE_POS_P2  STRING,         
Display_Trips_Share_Diff_PRE_POS_P3  STRING,         
Display_Trips_Share_Diff_PRE_POS_P4  STRING,         
Display_Trips_Share_Diff_PRE_POS_P5  STRING,         
Display_Trips_Share_Diff_PRE_POS_P6  STRING,         
Display_Trips_Share_Diff_PRE_POS_P7  STRING,  
Display_Trips_Share_Diff_PRE_POS_P8  STRING,        

Perc_Display_Trips_P0_PRE  STRING ,                   
Perc_Display_Trips_P1_PRE  STRING ,                   
Perc_Display_Trips_P2_PRE  STRING ,                   
Perc_Display_Trips_P3_PRE  STRING ,                   
Perc_Display_Trips_P4_PRE  STRING ,                   
Perc_Display_Trips_P5_PRE  STRING ,                   
Perc_Display_Trips_P6_PRE  STRING ,                   
Perc_Display_Trips_P7_PRE  STRING ,                   
Perc_Display_Trips_P8_PRE  STRING ,

Perc_Display_Trips_P0_POS  STRING ,                   
Perc_Display_Trips_P1_POS  STRING ,                   
Perc_Display_Trips_P2_POS  STRING ,                   
Perc_Display_Trips_P3_POS  STRING ,                   
Perc_Display_Trips_P4_POS  STRING ,                   
Perc_Display_Trips_P5_POS  STRING ,                   
Perc_Display_Trips_P6_POS  STRING ,                   
Perc_Display_Trips_P7_POS  STRING ,                   
Perc_Display_Trips_P8_POS  STRING ,

Feature_or_display_Trips_Share_Diff_PRE_POS_P0 STRING,
Feature_or_display_Trips_Share_Diff_PRE_POS_P1 STRING,
Feature_or_display_Trips_Share_Diff_PRE_POS_P2 STRING,
Feature_or_display_Trips_Share_Diff_PRE_POS_P3 STRING,
Feature_or_display_Trips_Share_Diff_PRE_POS_P4 STRING,
Feature_or_display_Trips_Share_Diff_PRE_POS_P5 STRING,
Feature_or_display_Trips_Share_Diff_PRE_POS_P6 STRING,
Feature_or_display_Trips_Share_Diff_PRE_POS_P7 STRING,
Feature_or_display_Trips_Share_Diff_PRE_POS_P8 STRING,

Perc_Feature_or_Display_Trips_P0_PRE  STRING ,        
Perc_Feature_or_Display_Trips_P1_PRE  STRING ,        
Perc_Feature_or_Display_Trips_P2_PRE  STRING ,        
Perc_Feature_or_Display_Trips_P3_PRE  STRING ,        
Perc_Feature_or_Display_Trips_P4_PRE  STRING ,        
Perc_Feature_or_Display_Trips_P5_PRE  STRING ,        
Perc_Feature_or_Display_Trips_P6_PRE  STRING ,        
Perc_Feature_or_Display_Trips_P7_PRE  STRING ,      
Perc_Feature_or_Display_Trips_P8_PRE  STRING ,   

Perc_Feature_or_Display_Trips_P0_POS  STRING ,        
Perc_Feature_or_Display_Trips_P1_POS  STRING ,        
Perc_Feature_or_Display_Trips_P2_POS  STRING ,        
Perc_Feature_or_Display_Trips_P3_POS  STRING ,        
Perc_Feature_or_Display_Trips_P4_POS  STRING ,        
Perc_Feature_or_Display_Trips_P5_POS  STRING ,        
Perc_Feature_or_Display_Trips_P6_POS  STRING ,        
Perc_Feature_or_Display_Trips_P7_POS  STRING ,        
Perc_Feature_or_Display_Trips_P8_POS  STRING ,     

Any_promo_Trips_Share_Diff_PRE_POS_P0  STRING,       
Any_promo_Trips_Share_Diff_PRE_POS_P1  STRING,       
Any_promo_Trips_Share_Diff_PRE_POS_P2  STRING,       
Any_promo_Trips_Share_Diff_PRE_POS_P3  STRING,       
Any_promo_Trips_Share_Diff_PRE_POS_P4  STRING,       
Any_promo_Trips_Share_Diff_PRE_POS_P5  STRING,       
Any_promo_Trips_Share_Diff_PRE_POS_P6  STRING,       
Any_promo_Trips_Share_Diff_PRE_POS_P7  STRING,
Any_promo_Trips_Share_Diff_PRE_POS_P8  STRING,       

Perc_Any_Promo_Trips_P0_PRE   STRING ,                
Perc_Any_Promo_Trips_P1_PRE   STRING ,                
Perc_Any_Promo_Trips_P2_PRE   STRING ,                
Perc_Any_Promo_Trips_P3_PRE   STRING ,                
Perc_Any_Promo_Trips_P4_PRE   STRING ,                
Perc_Any_Promo_Trips_P5_PRE   STRING ,                
Perc_Any_Promo_Trips_P6_PRE   STRING ,                
Perc_Any_Promo_Trips_P7_PRE   STRING ,    

Perc_Any_Promo_Trips_P0_POS   STRING ,                
Perc_Any_Promo_Trips_P1_POS   STRING ,                
Perc_Any_Promo_Trips_P2_POS   STRING ,                
Perc_Any_Promo_Trips_P3_POS   STRING ,                
Perc_Any_Promo_Trips_P4_POS   STRING ,                
Perc_Any_Promo_Trips_P5_POS   STRING ,                
Perc_Any_Promo_Trips_P6_POS   STRING ,                
Perc_Any_Promo_Trips_P7_POS   STRING ,
Perc_Any_Promo_Trips_P8_POS   STRING ,               

Price_reduction_Trips_Share_Diff_PRE_POS_P0 STRING,  
Price_reduction_Trips_Share_Diff_PRE_POS_P1 STRING,  
Price_reduction_Trips_Share_Diff_PRE_POS_P2 STRING,  
Price_reduction_Trips_Share_Diff_PRE_POS_P3 STRING,  
Price_reduction_Trips_Share_Diff_PRE_POS_P4 STRING,  
Price_reduction_Trips_Share_Diff_PRE_POS_P5 STRING,  
Price_reduction_Trips_Share_Diff_PRE_POS_P6 STRING,  
Price_reduction_Trips_Share_Diff_PRE_POS_P7 STRING,  
Price_reduction_Trips_Share_Diff_PRE_POS_P8 STRING,

Perc_Price_Reduction_Trips_P0_PRE    STRING ,         
Perc_Price_Reduction_Trips_P1_PRE    STRING ,         
Perc_Price_Reduction_Trips_P2_PRE    STRING ,         
Perc_Price_Reduction_Trips_P3_PRE    STRING ,         
Perc_Price_Reduction_Trips_P4_PRE    STRING ,         
Perc_Price_Reduction_Trips_P5_PRE    STRING ,         
Perc_Price_Reduction_Trips_P6_PRE    STRING ,         
Perc_Price_Reduction_Trips_P7_PRE    STRING ,  
Perc_Price_Reduction_Trips_P8_PRE    STRING ,       

Perc_Price_Reduction_Trips_P0_POS    STRING ,         
Perc_Price_Reduction_Trips_P1_POS    STRING ,         
Perc_Price_Reduction_Trips_P2_POS    STRING ,         
Perc_Price_Reduction_Trips_P3_POS    STRING ,         
Perc_Price_Reduction_Trips_P4_POS    STRING ,         
Perc_Price_Reduction_Trips_P5_POS    STRING ,         
Perc_Price_Reduction_Trips_P6_POS    STRING ,         
Perc_Price_Reduction_Trips_P7_POS    STRING ,      
Perc_Price_Reduction_Trips_P8_POS    STRING ,     

Price_per_Unit_Diff_PRE_POS_P0 STRING,               
Price_per_unit_Diff_PRE_POS_P1 STRING,               
Price_per_unit_Diff_PRE_POS_P2 STRING,               
Price_per_unit_Diff_PRE_POS_P3 STRING,               
Price_per_unit_Diff_PRE_POS_P4 STRING,               
Price_per_unit_Diff_PRE_POS_P5 STRING,               
Price_per_unit_Diff_PRE_POS_P6 STRING,               
Price_per_unit_Diff_PRE_POS_P7 STRING,               
Price_per_unit_Diff_PRE_POS_P8 STRING,

Price_per_Unit_P0_PRE   STRING ,                      
Price_per_Unit_P1_PRE   STRING ,                      
Price_per_Unit_P2_PRE   STRING ,                      
Price_per_Unit_P3_PRE   STRING ,                      
Price_per_Unit_P4_PRE   STRING ,                      
Price_per_Unit_P5_PRE   STRING ,                      
Price_per_Unit_P6_PRE   STRING ,                      
Price_per_Unit_P7_PRE   STRING ,                      
Price_per_Unit_P8_PRE   STRING ,

Price_per_Unit_P0_POS   STRING ,                      
Price_per_Unit_P1_POS   STRING ,                      
Price_per_Unit_P2_POS   STRING ,                      
Price_per_Unit_P3_POS   STRING ,                      
Price_per_Unit_P4_POS   STRING ,                      
Price_per_Unit_P5_POS   STRING ,                      
Price_per_Unit_P6_POS   STRING ,                      
Price_per_Unit_P7_POS   STRING ,      
Price_per_Unit_P8_POS   STRING ,

Buyer_Pre_P0 STRING,                
Buyer_Pre_P1 STRING,
Buyer_Pre_P2 STRING,
Buyer_Pre_P3 STRING,
Buyer_Pre_P4 STRING,
Buyer_Pre_P5 STRING,
Buyer_Pre_P6 STRING,
Buyer_Pre_P7 STRING,
Buyer_Pre_P8 STRING,

Nonbuyer_Pre_P0 STRING,
Nonbuyer_Pre_P1 STRING,
Nonbuyer_Pre_P2 STRING,
Nonbuyer_Pre_P3 STRING,
Nonbuyer_Pre_P4 STRING,
Nonbuyer_Pre_P5 STRING,
Nonbuyer_Pre_P6 STRING,
Nonbuyer_Pre_P7 STRING,
Nonbuyer_Pre_P8 STRING,

Buyer_Pos_P0 STRING,
Buyer_Pos_P1 STRING,
Buyer_Pos_P2 STRING,
Buyer_Pos_P3 STRING,
Buyer_Pos_P4 STRING,
Buyer_Pos_P5 STRING,
Buyer_Pos_P6 STRING,
Buyer_Pos_P7 STRING,
Buyer_Pos_P8 STRING,

Nonbuyer_Pos_P0 STRING,
Nonbuyer_Pos_P1 STRING,
Nonbuyer_Pos_P2 STRING,
Nonbuyer_Pos_P3 STRING,
Nonbuyer_Pos_P4 STRING,
Nonbuyer_Pos_P5 STRING,
Nonbuyer_Pos_P6 STRING,
Nonbuyer_Pos_P7 STRING,
Nonbuyer_Pos_P8 STRING,

estimated_hh_income string,
state string,
county_code string,
latitude string,
longitude string,
cape_age_pop_pct_0_17 string,
cape_age_pop_pct_65_99_plus string,
cape_age_pop_pct_18_99_plus string,
cape_age_pop_median_age string,
cape_ethnic_pop_pct_white_only string,
cape_ethnic_pop_pct_black_only string,
cape_ethnic_pop_pct_asian_only string,
cape_ethnic_pop_pct_hispanic string,
cape_child_hh_pct_with_persons_lt18 string,
cape_child_hh_pct_marr_couple_famwith_persons_lt18 string,
cape_typ_hh_pct_married_couple_family string,
cape_tenancy_occhu_pct_owner_occupied string,
cape_tenancy_occhu_pct_renter_occupied string,
cape_hhsize_hh_average_household_size string,
cape_density_persons_per_hh_for_pop_in_hh string,
cape_homval_oohu_median_home_value string,
cape_hustr_hu_pct_mobile_home string,
cape_built_hu_median_housing_unit_age string,
cape_lang_hh_pct_spanish_speaking string,
cape_educ_pop25_plus_median_education_attained string,
cape_inc_hh_median_family_household_income string,
cape_educ_ispsa_decile string,
cape_inc_family_inc_state_decile string,
census_rural_urban_county_size_code string,
core_based_statistical_areas string,
person_1_birth_year_and_month string,
person_1_combined_age string,
person_1_gender string,
person_1_marital_status string,
recipient_reliability_code string,
household_composition string,
person_1_person_type string,
homeowner_combined_homeowner string,
homeowner_probability_model string,
dwelling_type string,
length_of_residence string,
dwelling_unit_size string,
number_of_children_in_living_unit string,
number_of_adults_in_living_unit string,
number_of_person_in_living_unit string,
mail_responder string,
mosaic_hh string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LOCATION '/temp/step6';
{code}

*STEP 5. Insert data to table temp.step6*

{code}
INSERT OVERWRITE TABLE temp.step5 SELECT 
c2,a2,d2,d2,d2,h2,h2,f2,f2,a2,id,f2,f2,d2,d2,f2,f2,f2,f2,f2,f2,f2,f2,f2,f2,f2,
f2,d2,f2,f2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,
h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2,h2
FROM temp.test;
{code}

*STEP 6. Run query in hive shell*

{code}
Insert Overwrite Table temp.step6
SELECT
experian_id,max(exposed_flag) as exposed_flag,banner,

sum(CASE WHEN product_id_Hormel_REV3=0 and PERIOD=1 THEN Quantity END) as Product_0_Quantity_PRE,
sum(CASE WHEN product_id_Hormel_REV3=1 and PERIOD=1 THEN Quantity END) as Product_1_Quantity_PRE,
sum(CASE WHEN product_id_Hormel_REV3=2 and PERIOD=1 THEN Quantity END) as Product_2_Quantity_PRE,
sum(CASE WHEN product_id_Hormel_REV3=3 and PERIOD=1 THEN Quantity END) as Product_3_Quantity_PRE,
sum(CASE WHEN product_id_Hormel_REV3=4 and PERIOD=1 THEN Quantity END) as Product_4_Quantity_PRE,
sum(CASE WHEN product_id_Hormel_REV3=5 and PERIOD=1 THEN Quantity END) as Product_5_Quantity_PRE,
sum(CASE WHEN product_id_Hormel_REV3=6 and PERIOD=1 THEN Quantity END) as Product_6_Quantity_PRE,
sum(CASE WHEN product_id_Hormel_REV3=7 and PERIOD=1 THEN Quantity END) as Product_7_Quantity_PRE,
sum(CASE WHEN product_id_Hormel_REV3=8 and PERIOD=1 THEN Quantity END) as Product_8_Quantity_PRE,

sum(CASE WHEN product_id_Hormel_REV3=0 and PERIOD=2 THEN Quantity END) as Product_0_Quantity_POS,
sum(CASE WHEN product_id_Hormel_REV3=1 and PERIOD=2 THEN Quantity END) as Product_1_Quantity_POS,
sum(CASE WHEN product_id_Hormel_REV3=2 and PERIOD=2 THEN Quantity END) as Product_2_Quantity_POS,
sum(CASE WHEN product_id_Hormel_REV3=3 and PERIOD=2 THEN Quantity END) as Product_3_Quantity_POS,
sum(CASE WHEN product_id_Hormel_REV3=4 and PERIOD=2 THEN Quantity END) as Product_4_Quantity_POS,
sum(CASE WHEN product_id_Hormel_REV3=5 and PERIOD=2 THEN Quantity END) as Product_5_Quantity_POS,
sum(CASE WHEN product_id_Hormel_REV3=6 and PERIOD=2 THEN Quantity END) as Product_6_Quantity_POS,
sum(CASE WHEN product_id_Hormel_REV3=7 and PERIOD=2 THEN Quantity END) as Product_7_Quantity_POS,
sum(CASE WHEN product_id_Hormel_REV3=8 and PERIOD=2 THEN Quantity END) as Product_8_Quantity_POS,

sum(CASE WHEN product_id_Hormel_REV3=0 and PERIOD=1 THEN Net_Price END) as Product_0_Net_Price_PRE,
sum(CASE WHEN product_id_Hormel_REV3=1 and PERIOD=1 THEN Net_Price END) as Product_1_Net_Price_PRE,
sum(CASE WHEN product_id_Hormel_REV3=2 and PERIOD=1 THEN Net_Price END) as Product_2_Net_Price_PRE,
sum(CASE WHEN product_id_Hormel_REV3=3 and PERIOD=1 THEN Net_Price END) as Product_3_Net_Price_PRE,
sum(CASE WHEN product_id_Hormel_REV3=4 and PERIOD=1 THEN Net_Price END) as Product_4_Net_Price_PRE,
sum(CASE WHEN product_id_Hormel_REV3=5 and PERIOD=1 THEN Net_Price END) as Product_5_Net_Price_PRE,
sum(CASE WHEN product_id_Hormel_REV3=6 and PERIOD=1 THEN Net_Price END) as Product_6_Net_Price_PRE,
sum(CASE WHEN product_id_Hormel_REV3=7 and PERIOD=1 THEN Net_Price END) as Product_7_Net_Price_PRE,
sum(CASE WHEN product_id_Hormel_REV3=8 and PERIOD=1 THEN Net_Price END) as Product_8_Net_Price_PRE,

sum(CASE WHEN product_id_Hormel_REV3=0 and PERIOD=2 THEN Net_Price END) as Product_0_Net_Price_POS,
sum(CASE WHEN product_id_Hormel_REV3=1 and PERIOD=2 THEN Net_Price END) as Product_1_Net_Price_POS,
sum(CASE WHEN product_id_Hormel_REV3=2 and PERIOD=2 THEN Net_Price END) as Product_2_Net_Price_POS,
sum(CASE WHEN product_id_Hormel_REV3=3 and PERIOD=2 THEN Net_Price END) as Product_3_Net_Price_POS,
sum(CASE WHEN product_id_Hormel_REV3=4 and PERIOD=2 THEN Net_Price END) as Product_4_Net_Price_POS,
sum(CASE WHEN product_id_Hormel_REV3=5 and PERIOD=2 THEN Net_Price END) as Product_5_Net_Price_POS,
sum(CASE WHEN product_id_Hormel_REV3=6 and PERIOD=2 THEN Net_Price END) as Product_6_Net_Price_POS,
sum(CASE WHEN product_id_Hormel_REV3=7 and PERIOD=2 THEN Net_Price END) as Product_7_Net_Price_POS,
sum(CASE WHEN product_id_Hormel_REV3=8 and PERIOD=2 THEN Net_Price END) as Product_8_Net_Price_POS,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Coupon_Units END) as Coupon_Units_PRE_P0,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Coupon_Units END) as Coupon_Units_PRE_P1,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Coupon_Units END) as Coupon_Units_PRE_P2,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Coupon_Units END) as Coupon_Units_PRE_P3,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Coupon_Units END) as Coupon_Units_PRE_P4,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Coupon_Units END) as Coupon_Units_PRE_P5,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Coupon_Units END) as Coupon_Units_PRE_P6,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Coupon_Units END) as Coupon_Units_PRE_P7,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Coupon_Units END) as Coupon_Units_PRE_P8,

sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Coupon_Units END) as Coupon_Units_POS_P0,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Coupon_Units END) as Coupon_Units_POS_P1,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Coupon_Units END) as Coupon_Units_POS_P2,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Coupon_Units END) as Coupon_Units_POS_P3,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Coupon_Units END) as Coupon_Units_POS_P4,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Coupon_Units END) as Coupon_Units_POS_P5,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Coupon_Units END) as Coupon_Units_POS_P6,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Coupon_Units END) as Coupon_Units_POS_P7,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Coupon_Units END) as Coupon_Units_POS_P8,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Coupon_Dollars END) as Coupon_Dollars_PRE_P0,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Coupon_Dollars END) as Coupon_Dollars_PRE_P1,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Coupon_Dollars END) as Coupon_Dollars_PRE_P2,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Coupon_Dollars END) as Coupon_Dollars_PRE_P3,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Coupon_Dollars END) as Coupon_Dollars_PRE_P4,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Coupon_Dollars END) as Coupon_Dollars_PRE_P5,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Coupon_Dollars END) as Coupon_Dollars_PRE_P6,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Coupon_Dollars END) as Coupon_Dollars_PRE_P7,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Coupon_Dollars END) as Coupon_Dollars_PRE_P8,

sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Coupon_Dollars END) as Coupon_Dollars_POS_P0,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Coupon_Dollars END) as Coupon_Dollars_POS_P1,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Coupon_Dollars END) as Coupon_Dollars_POS_P2,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Coupon_Dollars END) as Coupon_Dollars_POS_P3,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Coupon_Dollars END) as Coupon_Dollars_POS_P4,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Coupon_Dollars END) as Coupon_Dollars_POS_P5,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Coupon_Dollars END) as Coupon_Dollars_POS_P6,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Coupon_Dollars END) as Coupon_Dollars_POS_P7,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Coupon_Dollars END) as Coupon_Dollars_POS_P8,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Dollars_per_Trip_PRE_P0,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Dollars_per_Trip_PRE_P1,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Dollars_per_Trip_PRE_P2,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Dollars_per_Trip_PRE_P3,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Dollars_per_Trip_PRE_P4,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Dollars_per_Trip_PRE_P5,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Dollars_per_Trip_PRE_P6,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Dollars_per_Trip_PRE_P7,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Dollars_per_Trip_PRE_P8,

sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Dollars_per_Trip_POS_P0,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Dollars_per_Trip_POS_P1,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Dollars_per_Trip_POS_P2,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Dollars_per_Trip_POS_P3,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Dollars_per_Trip_POS_P4,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Dollars_per_Trip_POS_P5,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Dollars_per_Trip_POS_P6,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Dollars_per_Trip_POS_P7,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Dollars_per_Trip_POS_P8,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Quantity END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Units_per_Trip_PRE_P0,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Quantity END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Units_per_Trip_PRE_P1,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Quantity END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Units_per_Trip_PRE_P2,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Quantity END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Units_per_Trip_PRE_P3,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Quantity END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Units_per_Trip_PRE_P4,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Quantity END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Units_per_Trip_PRE_P5,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Quantity END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Units_per_Trip_PRE_P6,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Quantity END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Units_per_Trip_PRE_P7,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Quantity END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Units_per_Trip_PRE_P8,

sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Quantity END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Units_per_Trip_POS_P0,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Quantity END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Units_per_Trip_POS_P1,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Quantity END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Units_per_Trip_POS_P2,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Quantity END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Units_per_Trip_POS_P3,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Quantity END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Units_per_Trip_POS_P4,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Quantity END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Units_per_Trip_POS_P5,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Quantity END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Units_per_Trip_POS_P6,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Quantity END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Units_per_Trip_POS_P7,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Quantity END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Units_per_Trip_POS_P8,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Trips_PRE_P0,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Trips_PRE_P1,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Trips_PRE_P2,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Trips_PRE_P3,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Trips_PRE_P4,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Trips_PRE_P5,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Trips_PRE_P6,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Trips_PRE_P7,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Trips_PRE_P8,

sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Trips_POS_P0,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Trips_POS_P1,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Trips_POS_P2,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Trips_POS_P3,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Trips_POS_P4,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Trips_POS_P5,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Trips_POS_P6,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Trips_POS_P7,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Trips_POS_P8,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Coupon_Dollars_per_Trip_Diff_PRE_POS_P1,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Coupon_Dollars_per_Trip_Diff_PRE_POS_P2,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Coupon_Dollars_per_Trip_Diff_PRE_POS_P3,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Coupon_Dollars_per_Trip_Diff_PRE_POS_P4,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Coupon_Dollars_per_Trip_Diff_PRE_POS_P5,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Coupon_Dollars_per_Trip_Diff_PRE_POS_P6,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Coupon_Dollars_per_Trip_Diff_PRE_POS_P7,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Coupon_Dollars_per_Trip_Diff_PRE_POS_P8,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Perc_Coupon_Dollars_P0_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Perc_Coupon_Dollars_P1_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Perc_Coupon_Dollars_P2_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Perc_Coupon_Dollars_P3_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Perc_Coupon_Dollars_P4_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Perc_Coupon_Dollars_P5_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Perc_Coupon_Dollars_P6_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Perc_Coupon_Dollars_P7_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Perc_Coupon_Dollars_P8_PRE,

sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Perc_Coupon_Dollars_P0_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Perc_Coupon_Dollars_P1_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Perc_Coupon_Dollars_P2_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Perc_Coupon_Dollars_P3_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Perc_Coupon_Dollars_P4_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Perc_Coupon_Dollars_P5_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Perc_Coupon_Dollars_P6_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Perc_Coupon_Dollars_P7_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Coupon_Dollars END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Perc_Coupon_Dollars_P8_POS,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Coupon_Units_per_Trip_Diff_PRE_POS_P0,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Coupon_Units_per_Trip_Diff_PRE_POS_P1,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Coupon_Units_per_Trip_Diff_PRE_POS_P2,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Coupon_Units_per_Trip_Diff_PRE_POS_P3,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Coupon_Units_per_Trip_Diff_PRE_POS_P4,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Coupon_Units_per_Trip_Diff_PRE_POS_P5,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Coupon_Units_per_Trip_Diff_PRE_POS_P6,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Coupon_Units_per_Trip_Diff_PRE_POS_P7,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Coupon_Units_per_Trip_Diff_PRE_POS_P8,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Perc_Coupon_Units_P0_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Perc_Coupon_Units_P1_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Perc_Coupon_Units_P2_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Perc_Coupon_Units_P3_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Perc_Coupon_Units_P4_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Perc_Coupon_Units_P5_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Perc_Coupon_Units_P6_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Perc_Coupon_Units_P7_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Perc_Coupon_Units_P8_PRE,

sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Perc_Coupon_Units_P0_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Perc_Coupon_Units_P1_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Perc_Coupon_Units_P2_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Perc_Coupon_Units_P3_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Perc_Coupon_Units_P4_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Perc_Coupon_Units_P5_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Perc_Coupon_Units_P6_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Perc_Coupon_Units_P7_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Coupon_Units END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Perc_Coupon_Units_P8_POS,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Coupon_Trips_Share_Diff_PRE_POS_P0,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Coupon_Trips_Share_Diff_PRE_POS_P1,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Coupon_Trips_Share_Diff_PRE_POS_P2,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Coupon_Trips_Share_Diff_PRE_POS_P3,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Coupon_Trips_Share_Diff_PRE_POS_P4,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Coupon_Trips_Share_Diff_PRE_POS_P5,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Coupon_Trips_Share_Diff_PRE_POS_P6,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Coupon_Trips_Share_Diff_PRE_POS_P7,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Coupon_Trips_Share_Diff_PRE_POS_P8,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Perc_Coupon_Trips__P0_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Perc_Coupon_Trips__P1_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Perc_Coupon_Trips__P2_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Perc_Coupon_Trips__P3_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Perc_Coupon_Trips__P4_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Perc_Coupon_Trips__P5_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Perc_Coupon_Trips__P6_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Perc_Coupon_Trips__P7_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Perc_Coupon_Trips__P8_PRE,

sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Perc_Coupon_Trips__P0_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Perc_Coupon_Trips__P1_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Perc_Coupon_Trips__P2_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Perc_Coupon_Trips__P3_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Perc_Coupon_Trips__P4_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Perc_Coupon_Trips__P5_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Perc_Coupon_Trips__P6_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Perc_Coupon_Trips__P7_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN coupon_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Perc_Coupon_Trips__P8_POS,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Feature_Trips_Share_Diff_PRE_POS_P0,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Feature_Trips_Share_Diff_PRE_POS_P1,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Feature_Trips_Share_Diff_PRE_POS_P2,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Feature_Trips_Share_Diff_PRE_POS_P3,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Feature_Trips_Share_Diff_PRE_POS_P4,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Feature_Trips_Share_Diff_PRE_POS_P5,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Feature_Trips_Share_Diff_PRE_POS_P6,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Feature_Trips_Share_Diff_PRE_POS_P7,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Feature_Trips_Share_Diff_PRE_POS_P8,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Perc_Feature_Trips_P0_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Perc_Feature_Trips_P1_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Perc_Feature_Trips_P2_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Perc_Feature_Trips_P3_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Perc_Feature_Trips_P4_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Perc_Feature_Trips_P5_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Perc_Feature_Trips_P6_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Perc_Feature_Trips_P7_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN feature_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Perc_Feature_Trips_P8_PRE,

sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Perc_Feature_Trips_P0_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Perc_Feature_Trips_P1_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Perc_Feature_Trips_P2_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Perc_Feature_Trips_P3_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Perc_Feature_Trips_P4_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Perc_Feature_Trips_P5_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Perc_Feature_Trips_P6_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Perc_Feature_Trips_P7_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN feature_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Perc_Feature_Trips_P8_POS,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Display_Trips_Share_Diff_PRE_POS_P0,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Display_Trips_Share_Diff_PRE_POS_P1,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Display_Trips_Share_Diff_PRE_POS_P2,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Display_Trips_Share_Diff_PRE_POS_P3,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Display_Trips_Share_Diff_PRE_POS_P4,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Display_Trips_Share_Diff_PRE_POS_P5,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Display_Trips_Share_Diff_PRE_POS_P6,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Display_Trips_Share_Diff_PRE_POS_P7,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Display_Trips_Share_Diff_PRE_POS_P8,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Perc_Display_Trips_P0_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Perc_Display_Trips_P1_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Perc_Display_Trips_P2_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Perc_Display_Trips_P3_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Perc_Display_Trips_P4_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Perc_Display_Trips_P5_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Perc_Display_Trips_P6_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Perc_Display_Trips_P7_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Perc_Display_Trips_P8_PRE,

sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Perc_Display_Trips_P0_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Perc_Display_Trips_P1_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Perc_Display_Trips_P2_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Perc_Display_Trips_P3_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Perc_Display_Trips_P4_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Perc_Display_Trips_P5_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Perc_Display_Trips_P6_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Perc_Display_Trips_P7_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Perc_Display_Trips_P8_POS,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Feature_or_display_Trips_Share_Diff_PRE_POS_P0,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Feature_or_display_Trips_Share_Diff_PRE_POS_P1,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Feature_or_display_Trips_Share_Diff_PRE_POS_P2,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Feature_or_display_Trips_Share_Diff_PRE_POS_P3,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Feature_or_display_Trips_Share_Diff_PRE_POS_P4,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Feature_or_display_Trips_Share_Diff_PRE_POS_P5,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Feature_or_display_Trips_Share_Diff_PRE_POS_P6,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Feature_or_display_Trips_Share_Diff_PRE_POS_P7,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Feature_or_display_Trips_Share_Diff_PRE_POS_P8,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) as  Perc_Feature_or_Display_Trips_P0_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) as  Perc_Feature_or_Display_Trips_P1_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) as  Perc_Feature_or_Display_Trips_P2_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) as  Perc_Feature_or_Display_Trips_P3_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) as  Perc_Feature_or_Display_Trips_P4_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) as  Perc_Feature_or_Display_Trips_P5_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) as  Perc_Feature_or_Display_Trips_P6_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) as  Perc_Feature_or_Display_Trips_P7_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) as  Perc_Feature_or_Display_Trips_P8_PRE,

sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Perc_Feature_or_Display_Trips_P0_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Perc_Feature_or_Display_Trips_P1_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Perc_Feature_or_Display_Trips_P2_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Perc_Feature_or_Display_Trips_P3_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Perc_Feature_or_Display_Trips_P4_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Perc_Feature_or_Display_Trips_P5_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Perc_Feature_or_Display_Trips_P6_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Perc_Feature_or_Display_Trips_P7_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN feature_or_display_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Perc_Feature_or_Display_Trips_P8_POS,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Any_promo_Trips_Share_Diff_PRE_POS_P0,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Any_promo_Trips_Share_Diff_PRE_POS_P1,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Any_promo_Trips_Share_Diff_PRE_POS_P2,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Any_promo_Trips_Share_Diff_PRE_POS_P3,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Any_promo_Trips_Share_Diff_PRE_POS_P4,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Any_promo_Trips_Share_Diff_PRE_POS_P5,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Any_promo_Trips_Share_Diff_PRE_POS_P6,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Any_promo_Trips_Share_Diff_PRE_POS_P7,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Any_promo_Trips_Share_Diff_PRE_POS_P8,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Perc_Any_Promo_Trips_P0_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Perc_Any_Promo_Trips_P1_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Perc_Any_Promo_Trips_P2_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Perc_Any_Promo_Trips_P3_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Perc_Any_Promo_Trips_P4_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Perc_Any_Promo_Trips_P5_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Perc_Any_Promo_Trips_P6_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Perc_Any_Promo_Trips_P7_PRE,

sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Perc_Any_Promo_Trips_P0_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Perc_Any_Promo_Trips_P1_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Perc_Any_Promo_Trips_P2_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Perc_Any_Promo_Trips_P3_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Perc_Any_Promo_Trips_P4_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Perc_Any_Promo_Trips_P5_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Perc_Any_Promo_Trips_P6_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Perc_Any_Promo_Trips_P7_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN any_promo_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Perc_Any_Promo_Trips_P8_POS,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Price_reduction_Trips_Share_Diff_PRE_POS_P0,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Price_reduction_Trips_Share_Diff_PRE_POS_P1,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Price_reduction_Trips_Share_Diff_PRE_POS_P2,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Price_reduction_Trips_Share_Diff_PRE_POS_P3,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Price_reduction_Trips_Share_Diff_PRE_POS_P4,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Price_reduction_Trips_Share_Diff_PRE_POS_P5,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Price_reduction_Trips_Share_Diff_PRE_POS_P6,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Price_reduction_Trips_Share_Diff_PRE_POS_P7,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Price_reduction_Trips_Share_Diff_PRE_POS_P8,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P0_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P1_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P2_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P3_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P4_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P5_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P6_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P7_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P8_PRE,

sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P0_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P1_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P2_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P3_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P4_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P5_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P6_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P7_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN price_reduction_occasions END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END) as Perc_Price_Reduction_Trips_P8_POS,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Quantity  END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Quantity  END) as Price_per_Unit_Diff_PRE_POS_P0,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Quantity  END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Quantity  END) as Price_per_unit_Diff_PRE_POS_P1,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Quantity  END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Quantity  END) as Price_per_unit_Diff_PRE_POS_P2,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Quantity  END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Quantity  END) as Price_per_unit_Diff_PRE_POS_P3,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Quantity  END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Quantity  END) as Price_per_unit_Diff_PRE_POS_P4,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Quantity  END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Quantity  END) as Price_per_unit_Diff_PRE_POS_P5,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Quantity  END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Quantity  END) as Price_per_unit_Diff_PRE_POS_P6,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Quantity  END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Quantity  END) as Price_per_unit_Diff_PRE_POS_P7,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Quantity  END) -  sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Quantity  END) as Price_per_unit_Diff_PRE_POS_P8,

sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Quantity  END) as Price_per_Unit_P0_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Quantity  END) as Price_per_Unit_P1_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Quantity  END) as Price_per_Unit_P2_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Quantity  END) as Price_per_Unit_P3_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Quantity  END) as Price_per_Unit_P4_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Quantity  END) as Price_per_Unit_P5_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Quantity  END) as Price_per_Unit_P6_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Quantity  END) as Price_per_Unit_P7_PRE,
sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Net_Price END)/sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Quantity  END) as Price_per_Unit_P8_PRE,

sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Quantity  END) as Price_per_Unit_P0_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Quantity  END) as Price_per_Unit_P1_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Quantity  END) as Price_per_Unit_P2_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Quantity  END) as Price_per_Unit_P3_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Quantity  END) as Price_per_Unit_P4_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Quantity  END) as Price_per_Unit_P5_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Quantity  END) as Price_per_Unit_P6_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Quantity  END) as Price_per_Unit_P7_POS,
sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Net_Price END)/sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Quantity  END) as Price_per_Unit_P8_POS,

(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pre_P0,
(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pre_P1,
(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pre_P2,
(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pre_P3,
(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pre_P4,
(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pre_P5,
(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pre_P6,
(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pre_P7,
(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pre_P8,

(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=0 THEN Trans_Num END)) IS NULL then 1 else 0 end) as  Nonbuyer_Pre_P0,
(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=1 THEN Trans_Num END)) IS NULL then 1 else 0 end) as  Nonbuyer_Pre_P1,
(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=2 THEN Trans_Num END)) IS NULL then 1 else 0 end) as  Nonbuyer_Pre_P2,
(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=3 THEN Trans_Num END)) IS NULL then 1 else 0 end) as  Nonbuyer_Pre_P3,
(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=4 THEN Trans_Num END)) IS NULL then 1 else 0 end) as  Nonbuyer_Pre_P4,
(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=5 THEN Trans_Num END)) IS NULL then 1 else 0 end) as  Nonbuyer_Pre_P5,
(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=6 THEN Trans_Num END)) IS NULL then 1 else 0 end) as  Nonbuyer_Pre_P6,
(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=7 THEN Trans_Num END)) IS NULL then 1 else 0 end) as  Nonbuyer_Pre_P7,                                                                                                                                                                                                                                                                                              
(case when (sum(CASE WHEN PERIOD=1 and product_id_Hormel_REV3=8 THEN Trans_Num END)) IS NULL then 1 else 0 end) as  Nonbuyer_Pre_P8,                                                                                                                                                                                                                                                                                              

(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pos_P0,
(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pos_P1,
(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pos_P2,
(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pos_P3,
(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pos_P4,
(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pos_P5,
(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pos_P6,
(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pos_P7,
(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END)) >0 then 1 else 0 end) as Buyer_Pos_P8,

(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=0 THEN Trans_Num END)) IS NULL then 1 else 0 end) as Nonbuyer_Pos_P0,
(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=1 THEN Trans_Num END)) IS NULL then 1 else 0 end) as Nonbuyer_Pos_P1,
(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=2 THEN Trans_Num END)) IS NULL then 1 else 0 end) as Nonbuyer_Pos_P2,
(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=3 THEN Trans_Num END)) IS NULL then 1 else 0 end) as Nonbuyer_Pos_P3,
(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=4 THEN Trans_Num END)) IS NULL then 1 else 0 end) as Nonbuyer_Pos_P4,
(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=5 THEN Trans_Num END)) IS NULL then 1 else 0 end) as Nonbuyer_Pos_P5,
(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=6 THEN Trans_Num END)) IS NULL then 1 else 0 end) as Nonbuyer_Pos_P6,
(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=7 THEN Trans_Num END)) IS NULL then 1 else 0 end) as Nonbuyer_Pos_P7,
(case when (sum(CASE WHEN PERIOD=2 and product_id_Hormel_REV3=8 THEN Trans_Num END)) IS NULL then 1 else 0 end) as Nonbuyer_Pos_P8,

max(estimated_hh_income) as  estimated_hh_income,                                                                                                                                                                                                                                             
max(state) as  state,
max(county_code) as  county_code,
max(latitude) as  latitude,
max(longitude) as  longitude,
max(cape_age_pop_pct_0_17) as  cape_age_pop_pct_0_17,
max(cape_age_pop_pct_65_99_plus) as  cape_age_pop_pct_65_99_plus,
max(cape_age_pop_pct_18_99_plus) as  cape_age_pop_pct_18_99_plus,
max(cape_age_pop_median_age) as  cape_age_pop_median_age,
max(cape_ethnic_pop_pct_white_only) as  cape_ethnic_pop_pct_white_only,
max(cape_ethnic_pop_pct_black_only) as  cape_ethnic_pop_pct_black_only,
max(cape_ethnic_pop_pct_asian_only) as  cape_ethnic_pop_pct_asian_only,
max(cape_ethnic_pop_pct_hispanic) as  cape_ethnic_pop_pct_hispanic,
max(cape_child_hh_pct_with_persons_lt18) as  cape_child_hh_pct_with_persons_lt18,
max(cape_child_hh_pct_marr_couple_famwith_persons_lt18) as  cape_child_hh_pct_marr_couple_famwith_persons_lt18,
max(cape_typ_hh_pct_married_couple_family) as  cape_typ_hh_pct_married_couple_family,
max(cape_tenancy_occhu_pct_owner_occupied) as  cape_tenancy_occhu_pct_owner_occupied,
max(cape_tenancy_occhu_pct_renter_occupied) as  cape_tenancy_occhu_pct_renter_occupied,
max(cape_hhsize_hh_average_household_size) as  cape_hhsize_hh_average_household_size,
max(cape_density_persons_per_hh_for_pop_in_hh) as  cape_density_persons_per_hh_for_pop_in_hh,
max(cape_homval_oohu_median_home_value) as  cape_homval_oohu_median_home_value,
max(cape_hustr_hu_pct_mobile_home) as  cape_hustr_hu_pct_mobile_home,
max(cape_built_hu_median_housing_unit_age) as  cape_built_hu_median_housing_unit_age,
max(cape_lang_hh_pct_spanish_speaking) as  cape_lang_hh_pct_spanish_speaking,
max(cape_educ_pop25_plus_median_education_attained) as  cape_educ_pop25_plus_median_education_attained,
max(cape_inc_hh_median_family_household_income) as  cape_inc_hh_median_family_household_income,
max(cape_educ_ispsa_decile) as  cape_educ_ispsa_decile,
max(cape_inc_family_inc_state_decile) as  cape_inc_family_inc_state_decile,
max(census_rural_urban_county_size_code) as  census_rural_urban_county_size_code,
max(core_based_statistical_areas) as  core_based_statistical_areas,
max(person_1_birth_year_and_month) as  person_1_birth_year_and_month,
max(person_1_combined_age) as  person_1_combined_age,
max(person_1_gender) as  person_1_gender,
max(person_1_marital_status) as  person_1_marital_status,
max(recipient_reliability_code) as  recipient_reliability_code,
max(household_composition) as  household_composition,
max(person_1_person_type) as  person_1_person_type,
max(homeowner_combined_homeowner) as  homeowner_combined_homeowner,
max(homeowner_probability_model) as  homeowner_probability_model,
max(dwelling_type) as  dwelling_type,
max(length_of_residence) as  length_of_residence,
max(dwelling_unit_size) as  dwelling_unit_size,
max(number_of_children_in_living_unit) as  number_of_children_in_living_unit,
max(number_of_adults_in_living_unit) as  number_of_adults_in_living_unit,
max(number_of_person_in_living_unit) as  number_of_person_in_living_unit,
max(mail_responder) as  mail_responder,
max(mosaic_hh) as  mosaic_hh

FROM temp.step5
group by experian_id,Banner;
{code}

> Buffer underflow when inserting data to table
> ---------------------------------------------
>
>                 Key: HIVE-12779
>                 URL: https://issues.apache.org/jira/browse/HIVE-12779
>             Project: Hive
>          Issue Type: Bug
>          Components: Database/Schema, SQL
>         Environment: CDH 5.4.9
>            Reporter: Ming Hsuan Tu
>            Assignee: Alan Gates
>
> I face a buffer underflow problem when inserting data to table from hive 1.1.0.
> the block size is 128 MB and the data size is only 10MB, but it gives me 891 mappers.
> Task with the most failures(4):
> -----
> Task ID:
>   task_1451989578563_0001_m_000008
> URL:
>   http://0.0.0.0:8088/taskdetails.jsp?jobid=job_1451989578563_0001&tipid=task_1451989578563_0001_m_000008
> -----
> Diagnostic Messages for this Task:
> Error: java.lang.RuntimeException: Failed to load plan: hdfs://tpe-nn-3-1:8020/tmp/hive/alec.tu/af798488-dbf5-45da-8adb-e4f2ddde1242/hive_2016-01-05_18-34-26_864_3947114301988950007-1/-mr-10004/bb86c923-0dca-43cd-aa5d-ef575d764e06/map.xml: org.apache.hive.com.esotericsoftware.kryo.KryoException: Buffer underflow.
>         at org.apache.hadoop.hive.ql.exec.Utilities.getBaseWork(Utilities.java:450)
>         at org.apache.hadoop.hive.ql.exec.Utilities.getMapWork(Utilities.java:296)
>         at org.apache.hadoop.hive.ql.io.HiveInputFormat.init(HiveInputFormat.java:268)
>         at org.apache.hadoop.hive.ql.io.HiveInputFormat.getRecordReader(HiveInputFormat.java:234)
>         at org.apache.hadoop.hive.ql.io.CombineHiveInputFormat.getRecordReader(CombineHiveInputFormat.java:701)
>         at org.apache.hadoop.mapred.MapTask$TrackedRecordReader.<init>(MapTask.java:169)
>         at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:432)
>         at org.apache.hadoop.mapred.MapTask.run(MapTask.java:343)
>         at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:163)
>         at java.security.AccessController.doPrivileged(Native Method)
>         at javax.security.auth.Subject.doAs(Subject.java:415)
>         at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1671)
>         at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
> Caused by: org.apache.hive.com.esotericsoftware.kryo.KryoException: Buffer underflow.
>         at org.apache.hive.com.esotericsoftware.kryo.io.Input.require(Input.java:181)
>         at org.apache.hive.com.esotericsoftware.kryo.io.Input.readBoolean(Input.java:783)
>         at org.apache.hive.com.esotericsoftware.kryo.serializers.UnsafeCacheFields$UnsafeBooleanField.read(UnsafeCacheFields.java:120)
>         at org.apache.hive.com.esotericsoftware.kryo.serializers.FieldSerializer.read(FieldSerializer.java:507)
>         at org.apache.hive.com.esotericsoftware.kryo.Kryo.readObject(Kryo.java:672)
>         at org.apache.hadoop.hive.ql.exec.Utilities.deserializeObjectByKryo(Utilities.java:1069)
>         at org.apache.hadoop.hive.ql.exec.Utilities.deserializePlan(Utilities.java:960)
>         at org.apache.hadoop.hive.ql.exec.Utilities.deserializePlan(Utilities.java:974)
>         at org.apache.hadoop.hive.ql.exec.Utilities.getBaseWork(Utilities.java:416)
>         ... 12 more
> Container killed by the ApplicationMaster.
> Container killed on request. Exit code is 143
> Container exited with a non-zero exit code 143
> Thank you.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)