Блог

С помощью следующей функции и хранимой процедуры я получаю результирующий набор из 2 столбцов. Что мне нужно дополнительно, так это общая сумма в третьем столбце для всех открытых счетов-фактур внутреннего диапазона оценок, это было бы здорово для любой идеи.

   ALTER FUNCTION dbo.OpenOrders
    (
    @MandantId int
    )
  RETURNS TABLE
  AS
  RETURN
  SELECT        SUM(Invoices.Amount - ISNULL(Payment.Amount, 0)) AS Op
  FROM            Invoices INNER JOIN
                           Orders ON Invoices.OrderId = Orders.OrderId LEFT  OUTER JOIN
                           Payment ON Invoices.InvoiceId = Payment.InvoiceId
  WHERE        (Orders.MandantId = @MandantId)
  GROUP BY Invoice.InvoiceId, Invoices.Amount
  HAVING        (SUM(Invoices.Amount - ISNULL(Payment.Amount, 0)) <> 0)


  ALTER PROCEDURE dbo.GetOpRanges
  @MandantId int
  AS
  BEGIN

    SELECT * INTO #tmp_ranges

    FROM

    //wrong in first post -> OPDebitorByMandant(@MandantId)
    OpenOrders(@MandantId)

    SELECT  op AS [score range], COUNT(*) AS [number of occurences]
    FROM
    (SELECT CASE
      WHEN op BETWEEN 0 AND 50 THEN '  0- 50'
      WHEN op BETWEEN 50 AND 100 THEN ' 50-100' 
      WHEN op BETWEEN 100 AND 500 THEN '100-500' 
      ELSE '500 and >' END AS op
    FROM [#tmp_ranges]) AS t

    GROUP BY op

    RETURN

Result:
score range number of occurences range
------------------ -------------
  0- 50            23
 50-100            4
100-500            4
500 and >          21

What i need additional is a third column total for all open invoices inner score range.

Target result:
score range number of occurences Total
----------- -------------------- ------
  0- 50            23             1.150
 50-100            4                400
100-500            4              2.000
500 and >          21            22.000

      Tables:                                                                                                                                                                                 
    Invoices 
    InvoiceId  CustomerId OrderId DateOfInvoice Amount
    ---------- ---------- ------- ------------- ------
    1          1           20      20160301      1000.00
    2          2           22      20160501      2000.00
    3          1          102      20160601      3000.00
    ...

    Orders
    OrderId MandantId CustomerId DateOfOrder Amount
    ------- --------- ---------- ----------- -----------
     20     1         1          20160101     1000.00
     22     1         2          20160101     2000.00
    102     1         1          20160101     3000.00
    ...

    Payment
    PaymentId MandantId CustomerId InvoiceId OrderId DateOfPayment Amount 
    --------- --------- ---------- --------- ------- ------------- -------------
    1         1         1          1          20     20160310      1000.00
    2         1         2          2          22     20160505      2000.00
    3         1         1          3         102     20160610      3000.00
    ...
 

надеюсь, это поможет и еще раз заранее спасибо за любое решение