Need Cumulative Total by PO in SQL Query

[kholmsley]

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

[June7]

Could you provide db for analysis? Follow instructions at bottom of my post.

[NoellaG]

Hi, because this is posted in the SQL server topic, I presume you have a SQL server as backend. In this case I would create the query on SQL server. It has a lot more possibilities than Access SQL. For instance you can use grouping functions like partitioning/windowing/ranking functions/framing to create cumulative totals per group. If you wrap the whole in a procedure, you can also create and use temp tables ect. making your work a lot easier.