Code:
SELECT Count(*) AS Number_of_Duplicates, sd.charge_category_detail_code,
sd.tracking_number,
sd.invoice_number,
sd.account_number,
sd.net_amount,
sd.billed_weight,
sd.invoice_date,
sd.transaction_date,
sd.pickup_record_number,
sd.receiver_name,
sd.receiver_company_name,
sd.receiver_address_line_1,
sd.receiver_address_line_2,
sd.receiver_city,
sd.receiver_state,
sd.receiver_postal,
sd.receiver_country_territory
FROM shipment_details AS sd
INNER JOIN tracking_history
ON ( Iif(Isnull(sd.receiver_country_territory), "",
sd.receiver_country_territory)
=
Iif(Isnull(tracking_history.receiver_country_territory),
"",
tracking_history.receiver_country_territory) )
AND ( Iif(Isnull(sd.receiver_postal), "", sd.receiver_postal) =
Iif(Isnull(tracking_history.receiver_postal), "",
tracking_history.receiver_postal) )
AND ( Iif(Isnull(sd.receiver_state), "", sd.receiver_state) =
Iif(Isnull(tracking_history.receiver_state), "",
tracking_history.receiver_state) )
AND ( Iif(Isnull(sd.receiver_city), "", sd.receiver_city) =
Iif(Isnull(tracking_history.receiver_city), "",
tracking_history.receiver_city) )
AND ( Iif(Isnull(sd.receiver_address_line_2), "",
sd.receiver_address_line_2) =
Iif(
Isnull(tracking_history.receiver_address_line_2), "",
tracking_history.receiver_address_line_2) )
AND ( Iif(Isnull(sd.receiver_address_line_1), "",
sd.receiver_address_line_1) =
Iif(
Isnull(tracking_history.receiver_address_line_1), "",
tracking_history.receiver_address_line_1) )
AND ( Iif(Isnull(sd.receiver_company_name), "",
sd.receiver_company_name) =
Iif(Isnull(tracking_history.receiver_company_name), "",
tracking_history.receiver_company_name) )
AND ( Iif(Isnull(sd.receiver_name), "", sd.receiver_name) =
Iif(Isnull(tracking_history.receiver_name), "",
tracking_history.receiver_name) )
AND ( sd.tracking_number = tracking_history.tracking_number )
AND ( sd.invoice_number = tracking_history.invoice_number )
AND ( sd.account_number = tracking_history.account_number )
WHERE sd.charge_classification_code = 'FRT'
AND sd.bill_option_code NOT IN ( 'DTP', 'DFC' )
AND ( sd.charge_category_code = 'SHP'
OR sd.charge_category_detail_code = 'CLB' )
AND sd.tracking_number IS NOT NULL
AND sd.batch_id NOT IN (SELECT batch_id
FROM process_log
WHERE session_id IN (SELECT session_id
FROM process_log
WHERE process_end =
(SELECT Max(process_end)
FROM process_log)
AND process_location = (
SELECT
workspace_name
FROM
local_information))
)
GROUP BY sd.charge_category_detail_code,
sd.tracking_number,
sd.invoice_number,
sd.account_number,
sd.net_amount,
sd.billed_weight,
sd.invoice_date,
sd.transaction_date,
sd.pickup_record_number,
sd.receiver_name,
sd.receiver_company_name,
sd.receiver_address_line_1,
sd.receiver_address_line_2,
sd.receiver_city,
sd.receiver_state,
sd.receiver_postal,
sd.receiver_country_territory;