Page 2 - COUNTing issue

Dev Shed Forums Sponsor:

#16

March 8th, 2013, 12:42 PM

kloud

Registered User

Join Date: Jun 2012

Posts: 15

Time spent in forums: 2 h 56 m 48 sec
Reputation Power: 0

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

#17

March 8th, 2013, 01:02 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,374

Time spent in forums: 3 Months 1 Week 2 Days 7 h 1 m 52 sec
Reputation Power: 4140

Quote:

Originally Posted by kloud

Does this help?

yes, we're almost there

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 --

Code:

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

when there are both multiple dispatches and multiple invoiceitems

this is where the wrong results will be computed

also, are you sure there can be more than one salesproposalstatus for a given salesproposal?

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

#18

March 8th, 2013, 01:15 PM

kloud

Registered User

Join Date: Jun 2012

Posts: 15

Time spent in forums: 2 h 56 m 48 sec
Reputation Power: 0

Quote:

Originally Posted by r937

also, are you sure there can be more than one salesproposalstatus for a given salesproposal?

My apologies - I was thinking of another table that's similarly names. You're correct - only one status per Salesproposal.

I need the following from the query:
InvoiceQuantity
This is the total of the quantity column in the invoiceitem table for all invoices against the salesproposal
SalesProposal->Invoice(s)->InvoiceItem(s)

DeliveredQuantity
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.

InvoiceableQuantity
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.

Thanks.

#19

March 9th, 2013, 03:27 PM

kloud

Registered User

Join Date: Jun 2012

Posts: 15

Time spent in forums: 2 h 56 m 48 sec
Reputation Power: 0

Just posting the solution in case anybody's interested. Help from another forum. Derived tables were the answer. Very neat solution.

Code:

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
                               FROM            dbo.Dispatch
                               GROUP BY SalesProposalID) AS Dispatch_Derived ON Dispatch_Derived.SalesProposalID = dbo.SalesProposal.SalesProposalID

Page 2 - COUNTing issue

Developer Shed Advertisers and Affiliates