create database isg_cushmanw;
use isg_cushmanw;

CREATE TABLE `spend_visibility_vendors2_v48` (
  `id` int(11) DEFAULT NULL,
  `lower_name` varchar(255) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `start_date_id` int(11) DEFAULT NULL,
  `end_date_id` int(11) DEFAULT NULL,
  `subcategory_id` int(11) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=latin1;

CREATE TABLE `spend_visibility_preferred2_v48_temp_1526318087` (
  `id` int(11) NOT NULL,
  `company_id` int(11) DEFAULT NULL,
  `spend_visibility_vendor_id` int(11) DEFAULT NULL,
  `amount` decimal(18,2) DEFAULT NULL,
  `amount_ang` decimal(18,2) DEFAULT NULL,
  `amount_aud` decimal(18,2) DEFAULT NULL,
  `amount_brl` decimal(18,2) DEFAULT NULL,
  `amount_cad` decimal(18,2) DEFAULT NULL,
  `amount_chf` decimal(18,2) DEFAULT NULL,
  `amount_cny` decimal(18,2) DEFAULT NULL,
  `amount_cop` decimal(18,2) DEFAULT NULL,
  `amount_eur` decimal(18,2) DEFAULT NULL,
  `amount_gbp` decimal(18,2) DEFAULT NULL,
  `amount_hkd` decimal(18,2) DEFAULT NULL,
  `amount_inr` decimal(18,2) DEFAULT NULL,
  `amount_jpy` decimal(18,2) DEFAULT NULL,
  `amount_krw` decimal(18,2) DEFAULT NULL,
  `amount_mxn` decimal(18,2) DEFAULT NULL,
  `amount_pab` decimal(18,2) DEFAULT NULL,
  `amount_sgd` decimal(18,2) DEFAULT NULL,
  `amount_thb` decimal(18,2) DEFAULT NULL,
  `original_amount` decimal(18,2) DEFAULT NULL,
  `original_currency` varchar(255) DEFAULT NULL,
  `spend_visibility_subcategory_id` int(11) DEFAULT NULL,
  `spend_visibility_location_id` int(11) DEFAULT NULL,
  `spend_visibility_subdivision_id` int(11) DEFAULT NULL,
  `spend_visibility_gl_id` int(11) DEFAULT NULL,
  `invoice_number` varchar(255) DEFAULT NULL,
  `po_number` varchar(255) DEFAULT NULL,
  `voucher` varchar(255) DEFAULT NULL,
  `reference1` varchar(255) DEFAULT NULL,
  `reference2` varchar(255) DEFAULT NULL,
  `reference3` varchar(255) DEFAULT NULL,
  `reference4` varchar(255) DEFAULT NULL,
  `reference5` varchar(255) DEFAULT NULL,
  `spend_visibility_custom_id` int(11) DEFAULT NULL,
  `date_id` int(10) DEFAULT NULL,
  `source_table_id` int(11) DEFAULT NULL,
  `source_table` varchar(255) DEFAULT NULL,
  `is_preferred` tinyint(1) NOT NULL
) ENGINE=Columnstore DEFAULT CHARSET=latin1;

CREATE TABLE `spend_visibility2_v48` (
  `id` int(11) DEFAULT NULL,
  `spend_visibility_vendor_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `date_id` int(10) DEFAULT NULL,
  `spend_visibility_subcategory_id` int(11) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=latin1;

CREATE TABLE `preferred_vendors` (
  `id` int(11) DEFAULT NULL,
  `active` tinyint(4) DEFAULT NULL,
  `vendor_lower_name` varchar(255) DEFAULT NULL,
  `lower_name` varchar(255) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `start_date_id` int(11) DEFAULT NULL,
  `end_date_id` int(11) DEFAULT NULL,
  `subcategory_id` int(11) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=latin1;

select `SpendVisibility`.`id` 
FROM `isg_cushmanw`.`spend_visibility2_v48` AS `SpendVisibility`
INNER JOIN `isg_cushmanw`.`spend_visibility_vendors2_v48` AS `Vendor` ON (`Vendor`.`id` = `SpendVisibility`.`spend_visibility_vendor_id`)
INNER JOIN `isg_cushmanw`.`preferred_vendors` AS `PreferredVendors` ON (`PreferredVendors`.`active` = 1
                                                                        AND `PreferredVendors`.`vendor_lower_name` = `Vendor`.`lower_name`
                                                                        AND `PreferredVendors`.`company_id` = `SpendVisibility`.`company_id`
                                                                        AND `SpendVisibility`.`date_id` BETWEEN `PreferredVendors`.`start_date_id` AND `PreferredVendors`.`end_date_id`
                                                                        AND `SpendVisibility`.`spend_visibility_subcategory_id` = `PreferredVendors`.`subcategory_id`);
