SELECT count(`C2`) AS `C1`
FROM
  (SELECT `OTBL`.`C2`
   FROM
     (SELECT `C1`,
             `C1` AS `C2`
      FROM
        (SELECT `smt_id`,
                `smt_id` AS `C1`
         FROM `xxxxxxxx_db`.`yyyyyyyy_view`
         WHERE (`edi_type` = {odbc_positional_1:LowCardinality(String)}
                AND `transferred` = {odbc_positional_2:LowCardinality(String)})
           AND (`modality` IN ({odbc_positional_3:LowCardinality(String)}, {odbc_positional_4:LowCardinality(String)}))) AS `ITBL`) AS `OTBL`
   INNER JOIN
     (SELECT `C1`
      FROM
        (SELECT `C1`
         FROM
           (SELECT `Shipper_Name_Shorten`,
                   `gsis_id`,
                   `dw_db_name`,
                   `shipper_ident_id`,
                   `shipper_name`,
                   `shipper_global_id`,
                   `consignee_ident_id`,
                   `consignee_name`,
                   `consignee_global_id`,
                   `import_export`,
                   `office_network_location_id`,
                   `datapool_id`,
                   `Company`,
                   `Office`,
                   `atd`,
                   `etd`,
                   `edi_delayed`,
                   `edi_origin_agt_code`,
                   `edi_origin_agt_network_location_id`,
                   `edi_origin_agt_name`,
                   `edi_destination_agt_code`,
                   `edi_destination_agt_network_location_id`,
                   `edi_destination_agt_name`,
                   `modality`,
                   `is_edi`,
                   `edi_destination_agt_is_edi`,
                   `edi_origin_agt_is_edi`,
                   `creation_date`,
                   `month_year`,
                   `year`,
                   `is_mainhaul`,
                   `edi_relevant`,
                   `MonthNo`,
                   `CompanyOfficeDB`,
                   `EX_EDI_Timeliness_Update`,
                   `IM_EDI_Received_Is_Processed_Update`,
                   cast(`id` AS String) AS `C1`
            FROM `xxxxxxxx_db`.`yyyyyyyy_view`
            WHERE (((((((((((((((((((((((`dw_db_name` = {odbc_positional_5:LowCardinality(String)}
                                         AND cast(`shipper_ident_id` AS Float64) = {odbc_positional_6:Nullable(Float64)})
                                        AND `consignee_global_id` IS NULL)
                                       AND `import_export` = {odbc_positional_7:LowCardinality(String)})
                                      AND `shipper_name` = {odbc_positional_8:LowCardinality(String)})
                                     AND `shipper_global_id` IS NULL)
                                    AND cast(`consignee_ident_id` AS Float64) = {odbc_positional_9:Nullable(Float64)})
                                   AND `consignee_name` = {odbc_positional_10:LowCardinality(String)})
                                  AND cast(`office_network_location_id` AS Float64) = {odbc_positional_11:Nullable(Float64)})
                                 AND `datapool_id` = {odbc_positional_12:LowCardinality(String)})
                                AND `modality` = {odbc_positional_13:LowCardinality(String)})
                               AND `atd` = {odbc_positional_14:Nullable(Date)})
                              AND `EX_EDI_Timeliness_Update` = {odbc_positional_15:LowCardinality(String)})
                             AND `IM_EDI_Received_Is_Processed_Update` = {odbc_positional_16:LowCardinality(String)})
                            AND `edi_relevant` = {odbc_positional_17:LowCardinality(String)})
                           AND `Shipper_Name_Shorten` = {odbc_positional_18:LowCardinality(String)})
                          AND `edi_origin_agt_code` = {odbc_positional_19:Nullable(Float64)})
                         AND `edi_origin_agt_name` = {odbc_positional_20:LowCardinality(String)})
                        AND `edi_destination_agt_code` = {odbc_positional_21:Nullable(Float64)})
                       AND `edi_destination_agt_name` = {odbc_positional_22:LowCardinality(String)})
                      AND `etd` = {odbc_positional_23:Nullable(Date)})
                     AND `creation_date` = {odbc_positional_24:Nullable(DateTime64(9))})
                    AND `year` = {odbc_positional_25:LowCardinality(String)})
                   AND `Office` = {odbc_positional_26:LowCardinality(String)})
              AND `Company` = {odbc_positional_27:LowCardinality(String)}) AS `ITBL`
         WHERE `C1` = {odbc_positional_28:LowCardinality(String)}
           AND `gsis_id` = {odbc_positional_29:LowCardinality(String)}) AS `ITBL`
      GROUP BY `C1`) AS `ITBL` ON (`OTBL`.`C1` = `ITBL`.`C1`)) AS `ITBL`)

CREATE VIEW xxxxxxxx_db.yyyyyyyy_view
(

    `EDI_relevant_Mike` Bool,

    `Shipper_Name_Shorten` String,

    `Consignee_Name_Shorten` String,

    `id` Int64,

    `gsis_id` Nullable(String),

    `dw_db_name` String,

    `shipper_ident_id` Int64,

    `shipper_name` String,

    `shipper_global_id` Nullable(String),

    `consignee_ident_id` Int64,

    `consignee_name` String,

    `consignee_global_id` Nullable(String),

    `import_export` Nullable(String),

    `office_network_location_id` Int64,

    `datapool_id` Nullable(String),

    `Company` String,

    `Office` String,

    `atd` Nullable(Date),

    `etd` Nullable(Date),

    `edi_delayed` Bool,

    `ex_edi_timeliness` String,

    `smt_status` LowCardinality(Nullable(String)),

    `is_canceled_by_event_code` Nullable(Bool),

    `service_type` LowCardinality(Nullable(String)),

    `is_direct` Nullable(Bool),

    `edi_origin_agt_code` Nullable(Float64),

    `edi_origin_agt_network_location_id` Nullable(String),

    `edi_origin_agt_name` Nullable(String),

    `edi_origin_agt_is_dprhenus_member` Nullable(Bool),

    `edi_destination_agt_code` Nullable(Float64),

    `edi_destination_agt_network_location_id` Nullable(String),

    `edi_destination_agt_name` Nullable(String),

    `edi_destination_agt_is_dprhenus_member` Nullable(Bool),

    `modality` String,

    `is_edi` Nullable(Bool),

    `edi_destination_agt_is_edi` Nullable(Bool),

    `edi_origin_agt_is_edi` Nullable(Bool),

    `creation_date` DateTime,

    `month_year` Nullable(String),

    `year` Nullable(String),

    `is_mainhaul` Nullable(Bool),

    `edi_consignee_agt_is_dprhenus_member` Nullable(Bool),

    `smt_type` LowCardinality(Nullable(String)),

    `IM_EDI_Received_Is_Processed` Nullable(String),

    `edi_relevant` Nullable(Bool),

    `edi_relevant_group_wise` Nullable(Bool),

    `edi_relevant_row_wise` Nullable(Bool),

    `MonthNo` Nullable(String),

    `CompanyOfficeDB` Nullable(String),

    `EX_EDI_Timeliness_Update` String,

    `IM_EDI_Received_Is_Processed_Update` String
)
AS SELECT
    CAST(coalesce(edi_origin_agt_is_dprhenus_member,
 false) AND coalesce(edi_destination_agt_is_dprhenus_member,
 false) AND coalesce(is_mainhaul,
 false),
 'Bool') AS EDI_relevant_Mike,

    ifNull(arrayStringConcat(arraySlice(splitByChar(' ',
 shipper_name),
 1,
 2),
 ' '),
 '') AS Shipper_Name_Shorten,

    ifNull(arrayStringConcat(arraySlice(splitByChar(' ',
 consignee_name),
 1,
 2),
 ' '),
 '') AS Consignee_Name_Shorten,

    *,

    substring(month_year,
 1,
 2) AS MonthNo,

    concat(substring(datapool_id,
 1,
 4),
 '-',
 substring(replaceOne(dw_db_name,
 'ROCSPROD',
 ''),
 1,
 2)) AS CompanyOfficeDB,

    multiIf(edi_relevant = true,
 multiIf((import_export = 'Export') AND (lower(modality) = 'ocean') AND ((atd IS NOT NULL) OR (etd IS NOT NULL)),
 if(today() <= (coalesce(atd,
 etd) + toIntervalDay(7)),
 'On time',
 'Delayed'),
 (import_export = 'Export') AND (lower(modality) = 'air') AND ((atd IS NOT NULL) OR (etd IS NOT NULL)),
 if(today() <= coalesce(atd,
 etd),
 'On time',
 'Delayed'),
 import_export = 'Export',
 'NoInfo',
 'N/A'),
 'N/A') AS EX_EDI_Timeliness_Update,

    multiIf(edi_relevant = true,
 multiIf(import_export = 'Import',
 multiIf((gsis_id IS NULL) OR (trimBoth(gsis_id) = ''),
 'SystemError',
 (sumIf(1,
 import_export = 'Import') OVER (PARTITION BY gsis_id) > 0) AND (sumIf(1,
 import_export = 'Export') OVER (PARTITION BY gsis_id) > 0),
 'Created from EDI',
 (sumIf(1,
 import_export = 'Import') OVER (PARTITION BY gsis_id) > 1) AND (sumIf(1,
 import_export = 'Export') OVER (PARTITION BY gsis_id) = 0),
 'Invalid',
 'Not created from EDI'),
 'N/A'),
 'N/A') AS IM_EDI_Received_Is_Processed_Update
FROM xxxxxxxx_db.yyyyyyyy_view;