Stored Procedure Issue

• DeepaSubramanian

  Valued Member

  Points: 64

  More actions

  November 10, 2006 at 12:12 am

  #117607

  hi,

   I need to generate a stored procedure that involve the following tables.Please help in getting the same.

  I've the Following:

  Product - ProductCode,ProductDesc,FRB,SourceID,MarketSegmtID

  ProductPrice - ProductCode,CustNo

  Customer - CustNo,CustName

  Invoice - InvoiceNo,CustNo,DiscountPercent

  InvoiceDetailProduct - InvoiceNo, ProductCode,Quantity,UnitPrice

  InvoiceDetailProductNonInventory - InvoiceNo,ProductCode,Quantity,UnitPrice

         I need to generate a stored procedure to retrieve the following fields  CustomerName, ProductDesc,Productcode,SourceId,FRB,MktSegmentID,Quantity,UnitPrice,Value - that is the sum of Quantity and UnitPrice - DiscountPercent of the particular product - total value of invoices of that particular product in a specified time,

   Grouped by particular Customer

                I'll welcome any help from the Community people in getting me the result.

                   Thank you

                  sincerely,

                   Deepa

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  November 10, 2006 at 7:22 am

  #671081

  at a guess (would be helpful for test data and expected results )

  SELECT c.CustNo,

  c.CustomerName,

  p.ProductDesc,

  p.Productcode,

  p.SourceID,

  p.FRB,

  p.MktSegmentID,

  SUM(idp.Quantity) AS [Quantity],

  SUM(idp.Quantity * (idp.UnitPrice - (idp.UnitPrice * (100.0 - i.DiscountPercent)))) AS [Value]

  FROM Customer c

  INNER JOIN Invoice i ON i.CustNo = c.CustNo

  INNER JOIN InvoiceDetailProduct idp ON idp.InvoiceNo = i.InvoiceNo

  INNER JOIN Product p ON p.ProductCode = idp.ProductCode

  INNER JOIN ProductPrice pp ON pp.ProductCode = p.ProductCode

  GROUP BY c.CustNo,

  c.CustomerName,

  p.ProductDesc,

  p.Productcode,

  p.SourceID,

  p.FRB,

  p.MktSegmentID

  ORDER BY GROUPING(c.CustNo),

  c.CustNo,

  GROUPING(p.Productcode),

  p.Productcode

  HAVING GROUPING(c.CustomerName) = 0

  AND GROUPING(p.ProductDesc) = 0

  AND GROUPING(p.SourceID) = 0

  AND GROUPING(p.FRB) = 0

  AND GROUPING(p.MktSegmentID) = 0

  what is the purpose of InvoiceDetailProductNonInventory and how do you know which to use?

  Far away is close at hand in the images of elsewhere.
  Anon.

• DeepaSubramanian

  Valued Member

  Points: 64

  More actions

  November 12, 2006 at 11:05 pm

  #671263

  Hi David,

   Thats really great to note from you.Bye

            Thank you Once again.

                        Sincerely,

                         Deepa.