Hello all,
Thanks in advance for reviewing my post and the query below.
I cannot figure out where my issue is with this query:
SELECT inv_acct.invoice_number AS Invoice_Number,
inv_acct.account_number AS Account_Number,
Round(gsr.net_charges, 2) AS GSRs,
Round(cadj.net_charges) AS [Credit Adjustments],
Round(void.net_charges) AS Voids
FROM (SELECT sd1.invoice_number,
sd1.account_number
FROM shipment_details AS sd1
WHERE sd1.invoice_date >= '2019-12-18'
AND sd1.invoice_date <= '2019-12-18'
AND sd1.account_number IN (SELECT cca2.account_number
FROM carrier_client_accounts AS cca2
WHERE cca2.client_code = 'CRES'
AND cca2.carrier_code = 'UPSN')
GROUP BY sd1.invoice_number,
sd1.account_number) inv_acct
LEFT JOIN (SELECT sd2.invoice_number,
sd2.account_number,
Sum(sd2.net_amount) AS net_charges
FROM shipment_details sd2
WHERE sd2.charge_category_detail_code = 'GSR'
AND sd2.invoice_date >= '2019-12-18'
AND sd2.invoice_date <= '2019-12-18'
AND sd2.account_number IN (SELECT cca1.account_number
FROM
carrier_client_accounts AS cca1
WHERE
cca1.client_code = 'CRES'
AND cca1.carrier_code =
'UPSN')
GROUP BY sd2.invoice_number,
sd2.account_number) AS gsr
ON gsr.invoice_number = inv_acct.invoice_number
AND gsr.account_number = inv_acct.account_number
LEFT JOIN (SELECT sd2.invoice_number,
sd2.account_number,
Sum(sd2.net_amount) AS net_charges
FROM shipment_details sd2
WHERE sd2.charge_category_detail_code = 'VOID'
AND sd2.invoice_date >= '2019-12-18'
AND sd2.invoice_date <= '2019-12-18'
AND sd2.account_number IN (SELECT cca1.account_number
FROM
carrier_client_accounts AS cca1
WHERE
cca1.client_code = 'CRES'
AND cca1.carrier_code =
'UPSN')
GROUP BY sd2.invoice_number,
sd2.account_number) AS void
ON void.invoice_number = inv_acct.invoice_number
AND void.account_number = inv_acct.account_number
LEFT JOIN (SELECT sd2.invoice_number,
sd2.account_number,
Sum(sd2.net_amount) AS net_charges
FROM shipment_details sd2
WHERE sd2.charge_category_detail_code = 'CADJ'
AND sd2.invoice_date >= '2019-12-18'
AND sd2.invoice_date <= '2019-12-18'
AND sd2.account_number IN (SELECT cca1.account_number
FROM
carrier_client_accounts AS cca1
WHERE
cca1.client_code = 'CRES'
AND cca1.carrier_code =
'UPSN')
GROUP BY sd2.invoice_number,
sd2.account_number) AS cadj
ON cadj.invoice_number = inv_acct.invoice_number
AND cadj.account_number = inv_acct.account_number