March 8th, 2013, 12:42 PM
Does this help?
each SalesProposal has 1 or more SalesProposalStatus row
each SalesProposal has one Customer row
each SalesProposal has 0 or more Dispatch rows
each SalesProposal has 1 or more Invoice rows
each Invoice has multiple InvoiceItem rows
March 8th, 2013, 01:02 PM
yes, we're almost there
Originally Posted by kloud
it looks like you're summing invoice items across all invoices for a salesproposal
but what if there are multiple dispatches at the same time?
in particular, how to resolve this factor --
when there are both multiple dispatches and multiple invoiceitems
SUM(CASE WHEN dbo.SalesProposal.PreDispatchInvoicing = 1
THEN (dbo.SalesProposal.ForecastQuantity - dbo.InvoiceItem.Quantity)
ELSE ((dbo.Dispatch.Weight * dbo.Dispatch.ConversionFactor) -
dbo.InvoiceItem.Quantity) END) AS InvoiceableQuantity
this is where the wrong results will be computed
also, are you sure there can be more than one salesproposalstatus for a given salesproposal?
March 8th, 2013, 01:15 PM
My apologies - I was thinking of another table that's similarly names. You're correct - only one status per Salesproposal.
Originally Posted by r937
I need the following from the query:
This is the total of the quantity column in the invoiceitem table for all invoices against the salesproposal
This is the total of quantity delivered (the dispatch table records deliveries). The quantity delivered is a product of the weight column and conversion factor column in the dispatch table.
This is tricky. Salesproposals can be invoiced in 2 ways - by collating a number of dispatches and assigning an invoice to them. In that case, the dispatches are basically invoiceitems. The other way is pre dispatch where invoicing is done upfront before product ships. So a quantity is assigned to an invoice with a generic product type. These invoices will have only one invoiceitem against them. The flag PreDispatchInvoicing differentiates these invoicing methods, hence the case statement for it. If it's true, then the invoiceable quantity will come from subtracting the InvoicedQuantity (above) from a value called ForecastQuantity which is a field in the sales proposal. If it's false the the invoiceable quantity is simply calculated by subtracting the InvoicedQuantity from the DeliveredQuantity.
That's it. There are one or two other fields to add to the main select (no aggregates) but if I get this working I'll be able to finish it.
March 9th, 2013, 03:27 PM
Just posting the solution in case anybody's interested. Help from another forum. Derived tables were the answer. Very neat solution.
SELECT dbo.Customer.CustomerCode, dbo.SalesProposal.SPNo + ' (' + dbo.Forest.Forest + ')' AS SalesProposal, dbo.SalesProposal.SalesProposalID,
dbo.SalesProposal.PreDispatchInvoicing, CASE WHEN dbo.SalesProposal.PreDispatchInvoicing = 1 THEN 'Pre Dispatch' ELSE 'Post Dispatch' END AS Invoicing,
Invoice_Derived.InvoicedQuantity AS InvoicedQuantity, Dispatch_Derived.DeliveredQuantity AS DeliveredQuantity,
CASE WHEN dbo.SalesProposal.PreDispatchInvoicing = 1 THEN dbo.SalesProposal.ForecastQuantity - Invoice_Derived.InvoicedQuantity ELSE Dispatch_Derived.DeliveredQuantity
- Invoice_Derived.InvoicedQuantity END AS InvoiceableQuantity
FROM dbo.SalesProposal INNER JOIN
dbo.SalesProposalStatus ON dbo.SalesProposal.StatusID = dbo.SalesProposalStatus.SalesProposalStatusID AND
dbo.SalesProposalStatus.SalesProposalStatus = 'Sold' INNER JOIN
dbo.Forest ON dbo.SalesProposal.ForestID = dbo.Forest.ForestID INNER JOIN
dbo.Customer ON dbo.SalesProposal.CustomerID = dbo.Customer.CustomerID LEFT OUTER JOIN
(SELECT dbo.Invoice.SalesProposalID, SUM(dbo.InvoiceItem.Quantity) AS InvoicedQuantity
FROM dbo.Invoice INNER JOIN
dbo.InvoiceItem ON dbo.Invoice.InvoiceID = dbo.InvoiceItem.InvoiceID
GROUP BY dbo.Invoice.SalesProposalID) AS Invoice_Derived ON dbo.SalesProposal.SalesProposalID = Invoice_Derived.SalesProposalID LEFT OUTER JOIN
(SELECT SalesProposalID, SUM(Weight * ConversionFactor) AS DeliveredQuantity
GROUP BY SalesProposalID) AS Dispatch_Derived ON Dispatch_Derived.SalesProposalID = dbo.SalesProposal.SalesProposalID