I have made it this far
Code:
SELECT sd1.tracking_number, charges.freight as Freight, charges.Fuel as Fuel, charges.Discount as Discount, charges.Fuel_Discount as Fuel_Discount, charges.Tax as Tax, charges.Duty as Duty, charges.Miscellaneous as Miscellaneous
FROM shipment_details AS sd1
LEFT JOIN (SELECT tracking_number,
Sum(Iif(charge_classification_code = 'FRT',
net_amount + incentive_amount)) AS
Freight,
Sum(Iif(charge_classification_code = 'FSC',
net_amount + incentive_amount)) AS
Fuel,
Sum(Iif(charge_classification_code = 'FRT',
incentive_amount * -1))
AS Discount,
Sum(Iif(charge_classification_code = 'FSC',
incentive_amount * -1))
AS
Fuel_Discount,
Sum(Iif(charge_classification_code = 'TAX', net_amount)
)
AS Tax,
Sum(Iif(charge_classification_code IN ( 'GOV', 'BRK' ),
net_amount))
AS Duty,
Sum(Iif(charge_classification_code IN ( 'ACC', 'MSC' ),
net_amount))
AS
Miscellaneous
FROM shipment_details AS xxx
WHERE charge_category_code NOT IN ( 'ADJ', 'MIS' )
GROUP BY tracking_number) AS charges ON sd1.tracking_number = charges.tracking_number
but I am not sure how to add in an additional join. I tried the query below, but I get the following error: Syntax error in FROM clause
Code:
SELECT sd1.tracking_number, charges.freight as Freight, charges.Fuel as Fuel, charges.Discount as Discount, charges.Fuel_Discount as Fuel_Discount, charges.Tax as Tax, charges.Duty as Duty, charges.Miscellaneous as Miscellaneous, adj.*
FROM shipment_details AS sd1
LEFT JOIN (SELECT tracking_number,
Sum(Iif(charge_classification_code = 'FRT',
net_amount + incentive_amount)) AS
Freight,
Sum(Iif(charge_classification_code = 'FSC',
net_amount + incentive_amount)) AS
Fuel,
Sum(Iif(charge_classification_code = 'FRT',
incentive_amount * -1))
AS Discount,
Sum(Iif(charge_classification_code = 'FSC',
incentive_amount * -1))
AS
Fuel_Discount,
Sum(Iif(charge_classification_code = 'TAX', net_amount)
)
AS Tax,
Sum(Iif(charge_classification_code IN ( 'GOV', 'BRK' ),
net_amount))
AS Duty,
Sum(Iif(charge_classification_code IN ( 'ACC', 'MSC' ),
net_amount))
AS
Miscellaneous
FROM shipment_details AS xxx
WHERE charge_category_code NOT IN ( 'ADJ', 'MIS' )
GROUP BY tracking_number) AS charges ON sd1.tracking_number = charges.tracking_number,
LEFT JOIN (SELECT tracking_number,
Sum(net_amount) AS Adjustments
FROM shipment_details AS xxx
WHERE charge_category_code IN ( 'ADJ', 'MIS' )
GROUP BY tracking_number) AS adj ON sd1.tracking_number = adj.tracking_number
Thanks for any assistance