Hi!
I've worked for days on this and cannot figure it out. I have a couple issues, one is I need for there to be a cumulative FreightBalance by PO rather than the result calculating the FreightBalance on each line which is not helpful info. Second is it would be great to get the FreightAllocated amounts in their own line regardless of whether there is a Freightbill with a matching date. Is any of this possible? Help??
At the bottom I have copied an example of the result I get.
Here's my code:
SELECT qjoinfftables.usfpo,
qjoinfftables.del_date,
Iif(qjoinfftables.Del_Date = Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date), qjoinfftables.freightallocatedtotal, 0) AS FreightAllocated,
iif(qjoinfftables.Del_Date = Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date), qjoinfftables.freightactualtotal, 0) AS FreightActualTotal,
Sum(Nz(Iif(Year(qjoinfftables.Del_Date) = Year(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date)) And Month(qjoinfftables.Del_Date) = Month(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date)), tblfreightbilldetails.amount, 0), 0)) AS FreightPDTotal,
qjoinfftables.Vietnam_Savings,
qjoinfftables.USFOtherExpenses,
Sum(Iif(Month(qjoinfftables.Del_Date) = Month(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date)), qjoinfftables.oceanfreighttotal, 0)) AS OceanFreightTotal,
Sum(Iif(Month(qjoinfftables.Del_Date) = Month(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date)), qjoinfftables.brokeragetotal, 0)) AS BrokerageTotal,
Sum(Iif(Month(qjoinfftables.Del_Date) = Month(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date)), qjoinfftables.DutyTotal, 0)) AS DutyTotal,
Sum(Iif(Month(qjoinfftables.Del_Date) = Month(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date)), qjoinfftables.TariffTotal, 0)) AS TariffTotal,
Sum(Iif(Month(qjoinfftables.Del_Date) = Month(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date)), qjoinfftables.TaxesTotal, 0)) AS TaxesTotal,
Sum(Iif(Month(qjoinfftables.Del_Date) = Month(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date)), qjoinfftables.DELIVERYTOTAL, 0)) AS DeliveryTotal,
Sum(Iif(Month(qjoinfftables.Del_Date) = Month(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date)), qjoinfftables.ChassisTotal, 0)) AS ChassisTotal,
Sum(Iif(Month(qjoinfftables.Del_Date) = Month(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date)), qjoinfftables.PrePullTotal, 0)) AS PrePullTotal,
Sum(Iif(Month(qjoinfftables.Del_Date) = Month(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date)), qjoinfftables.StorageTotal, 0)) AS StorageTotal,
Sum(Iif(Month(qjoinfftables.Del_Date) = Month(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date)), qjoinfftables.DetentionTotal, 0)) AS DetentionTotal,
Sum(Iif(Month(qjoinfftables.Del_Date) = Month(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date)), qjoinfftables.LumperTotal, 0)) AS LumperTotal,
Sum(Iif(Month(qjoinfftables.Del_Date) = Month(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date)), qjoinfftables.OtherTotal, 0)) AS OtherTotal,
Iif(qjoinfftables.Del_Date = Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date), qjoinfftables.tariffallocation_fromFOB, 0) AS TariffAllocation_FromFOB,
Sum(Iif(Month(qjoinfftables.Del_Date) = Month(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date)), qjoinfftables.freightallocatedtotal, 0)) - Sum(Iif(Month(qjoinfftables.Del_Date) = Month(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date)), qjoinfftables.freightactualtotal, 0)) - qjoinfftables.USFOtherExpenses + qjoinfftables.Vietnam_Savings - Sum(Nz(tblfreightbilldetails.amount, 0)) AS Freight_Balance,
Iif(IsNull(tblfreightbilldetails.APDate), qjoinfftables.Del_Date, tblfreightbilldetails.APDate) AS AcctgDate,
iif(isnull(tblfreightbilldetails.qbcategory), "Original FF Entry", tblfreightbilldetails.qbcategory) as Category
FROM qjoinfftables
LEFT JOIN tblfreightbilldetails
ON qjoinfftables.usfpo = tblfreightbilldetails.usfpo
AND Month(qjoinfftables.Del_Date) = Month(Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date))
GROUP BY qjoinfftables.usfpo,
qjoinfftables.del_date,
iif(isnull(tblfreightbilldetails.qbcategory), "Original FF Entry", tblfreightbilldetails.qbcategory),
Iif(IsNull(tblfreightbilldetails.APDate), qjoinfftables.Del_Date, tblfreightbilldetails.APDate),
Iif(qjoinfftables.Del_Date = Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date), qjoinfftables.freightallocatedtotal, 0),
iif(qjoinfftables.Del_Date = Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date), qjoinfftables.freightactualtotal, 0),
Iif(qjoinfftables.Del_Date = Nz(tblfreightbilldetails.APDate, qjoinfftables.Del_Date), qjoinfftables.tariffallocation_fromFOB, 0),
qjoinfftables.Vietnam_Savings,
qjoinfftables.USFOtherExpenses,
deliverytotal,
OceanFreightTotal, BrokerageTotal, DutyTotal, TariffTotal, TaxesTotal, ChassisTotal, PrepullTotal, StorageTotal, DetentionTotal, LumperTotal, OtherTotal, TariffAllocation_FromFOB,
Iif(IsNull(tblfreightbilldetails.APDate), qjoinfftables.Del_Date, tblfreightbilldetails.APDate)
UNION SELECT qjoinfftables.usfpo, qjoinfftables.del_date,
0 AS FreightAllocated,
0 AS FreightActualTotal,
SUM(Nz(tblfreightbilldetails.amount,0)) AS FreightPDTotal,
qjoinfftables.Vietnam_Savings,
qjoinfftables.USFOtherExpenses,
0 as OceanFreightTotal,
0 as BrokerageTotal,
0 as DutyTotal,
0 as TariffTotal,
0 as TaxesTotal,
0 as DeliveryTotal,
0 as ChassisTotal, 0 as PrepullTotal, 0 as StorageTotal, 0 as DetentionTotal, 0 as LumperTotal, 0 as OtherTotal, 0 as TariffAllocation_FromFOB,
0-
SUM(Nz(tblfreightbilldetails.amount,0)) AS Freight_Balance,
Iif(IsNull(tblfreightbilldetails.APDate), qjoinfftables.Del_Date, tblfreightbilldetails.APDate) AS AcctgDate,
iif(isnull(tblfreightbilldetails.qbcategory), "Original FF Entry", tblfreightbilldetails.qbcategory) as Category
FROM qjoinfftables
LEFT JOIN tblfreightbilldetails ON qjoinfftables.usfpo = tblfreightbilldetails.usfpo
AND Month(qjoinfftables.Del_Date) <> Month(Nz(tblfreightbilldetails.APDate,qjoinfftable s.Del_Date))
WHERE Month(tblfreightbilldetails.APDate) = Month(Nz(tblfreightbilldetails.APDate,qjoinfftable s.Del_Date))
GROUP BY qjoinfftables.usfpo, qjoinfftables.del_date,qjoinfftables.Vietnam_Savin gs, qjoinfftables.USFOtherExpenses, qjoinFFTables.OceanFreightTotal, qjoinFFTables.BrokerageTotal, qjoinFFTables.DutyTotal, qjoinFFTables.TariffTotal, qjoinFFTables.TaxesTotal, qjoinFFTables.ChassisTotal, qjoinFFTables.PrepullTotal, qjoinFFTables.StorageTotal, qjoinFFTables.DetentionTotal, qjoinFFTables.LumperTotal, qjoinFFTables.OtherTotal, qjoinFFTables.TariffAllocation_FromFOB,qjoinfftabl es.freightallocatedtotal, qjoinfftables.freightactualtotal, tblfreightbilldetails.qbcategory,IIf(IsNull(tblfre ightbilldetails.APDate),qjoinfftables.Del_Date,tbl freightbilldetails.APDate), DateSerial(Year(qjoinfftables.Del_Date), Month(tblfreightbilldetails.APDate), 1);
usfpo del_date FreightAllocated FreightActualTotal FreightPDTotal Vietnam_Savings USFOtherExpenses OceanFreightTotal BrokerageTotal DutyTotal TariffTotal TaxesTotal DeliveryTotal ChassisTotal PrePullTotal StorageTotal DetentionTotal LumperTotal OtherTotal TariffAllocation_FromFOB Freight_Balance AcctgDate Category PO1 11-Apr-23 $0.00 $0.00 150 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 188506.6 24-Apr-23 Brokerage PO1 11-Apr-23 $0.00 $0.00 2490 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 60395.53 24-Apr-23 Ocean PO1 11-Apr-23 $69,646.47 $6,760.94 6262.6 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $9,183.48 182394 11-Apr-23 DTT