Thread: COUNTing issue

Page 2 of 2 First 12
  • Jump to page:
    #16
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    15
    Rep 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
  2. #17
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #18
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    15
    Rep Power
    0
    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.
  6. #19
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    15
    Rep 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 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo