SQL Server - deducting from available credit

Question

I have invoicing solution that uses Azure SQL to store and calculate invoice data. I have been requested to provide 'credit' functionality so rather than recovering customers charges, the totals are deducted from an amount of available credit and reflected in the invoice (solution xyz may have 1500 worth of charges, but deducted from available credit of 10,000 means its effectively zero'd and leaves 8,500 credit remaining ). Unfortunately after several days I haven't been able to work out how to do this.

I am able to get a list of items and their costs from sql easily:

invoice_id	contact_id	solution_id	total	date
202104-015	52	10000	30317.27	2021-05-22
202104-015	52	10001	2399.90	2021-05-22
202104-015	52	10005	8302.27	2021-05-22
202104-015	52	10060	3625.22	2021-05-22
202104-015	52	10111	22.87	2021-05-22
202104-015	52	10115	435.99	2021-05-22

I have another table that shows the credit available for the given contact:

id	credit_id	owner_id	total_applied	date_applied
1	C00001	52	500000.00	2021-05-14

I have tried using the following SQL statement, based on another stackoverflow question to subtract from the previous row, thinking each row would then reflect the remaining credit:

Select 
    invoice_id,
    solution_id
    sum(total) as 'total',
    cr.total_remaining - coalesce(lag(total)) over (order by s.solution_id), 0) as credit_available,
    date
from
    invoices
left join credits cr on
    cr.credit_id = 'C00001'

Whilst this does subtract, it only subtracts from the row above it, not all of the rows above it:

invoice_id	solution_id	total	credit_available	date
202104-015	10000	30317.27	500000.00	2021-05-22
202104-015	10001	2399.90	469682.73	2021-05-22
202104-015	10005	8302.27	497600.10	2021-05-22
202104-015	10060	3625.22	491697.73	2021-05-22
202104-015	10111	22.87	496374.78	2021-05-22
202104-015	10115	435.99	499977.13	2021-05-22

I've also tried various queries with a mess of case statements.

Im at the point where I am contemplating using powershell or similar to do the task instead (loop through each solution, check if there is enough available credit, update a deduction table, goto next etc) but I'd rather keep it all in SQL if I can.

Anyone have some pointers for this beginner?

Answer 1

This is a pretty complex scenario. I sadly cannot spend the time to offer a complete solution here. I do can provide you with tips and points of attention here:

• Be sure to determine the actual remaining credit based on the complete invoice history. If you introduce filtering (in a WHERE-clause, for example, or by including joins with other tables), the results should not be affected by it. You should probably pre-calculate the available credit per invoice detail record in a temporary table or in a CTE and use that data in your main query.
• Make sure that you regard the date_applied value of the credit. Before a credit is applied to a customer, that customer should probably have less credit or no credit at all. That should be reflected correctly on historical invoices, I guess.
• Make sure you determine the correct amount of total credit. It is unclear from the information provided in your question how that should be determined/calculated. Is only the latest total_applied value from the credits table active? Or should all the historical total_applied values be summarized to get the total available credit?)
• Include a correct join between your invoices table and your credits table. Currently, this join is hard coded in your query.
• Also regard actual payments by customers. Payments have effect on the available credit, I assume. Also note that, unless you are OK with a history that changes, you need to regard the payment dates as well (just like the credit change dates).

I'm not sure how you would solve your scenario using PowerShell... I do know for sure, that this can be tackled with SQL.

I cannot say anything about the resulting performance, however. These kinds of calculations surely come with a price tag attached in that regard. If you need high performance, I guess it might be more practical to include columns in your invoices table to physically store the available credit with each invoice detail record.

Edit

I have experimented a little with your scenario and your additional comments.

My solution implementation uses two CTEs:

• The first CTE (cte_invoice_credit_dates) retrieves the date of the active credit record for specific invoice IDs.
• The second CTE (cte_contact_invoice_summarized_totals) calculates the invoice totals of all the invoices of a specific contact. Since you want to summarize on solution detail per invoice as well, I also included the solution ID per invoice in the querying logic.

The main query selects all columns from the invoices table and uses the data from the two CTEs to calculate three additional columns in the result set:

• Column credit_assigned represents the total assigned credit at the invoice's date.
• Column summarized_total shows the contact's cumulative invoice total.
• Column credit_available shows the remaining credit.

WITH
  [cte_invoice_credit_dates] AS (
    SELECT DISTINCT
      I.[invoice_id],
      C.[date_applied]
    FROM
      [invoices] AS I
      OUTER APPLY (SELECT TOP (1) [date_applied]
                   FROM [credits]
                   WHERE
                     [owner_id] = I.[contact_id] AND
                     [date_applied] <= I.[date]
                   ORDER BY [date_applied] DESC) AS C
  ),
  [cte_contact_invoice_summarized_totals] AS (
    SELECT
      I.[contact_id],
      I.[invoice_id],
      I.[solution_id],
      SUM(H.[total]) AS [total]
    FROM
      [invoices] AS I
      INNER JOIN [invoices] AS H ON
        H.[contact_id] = I.[contact_id] AND
        H.[invoice_id] = I.[invoice_id] AND
        H.[solution_id] <= I.[solution_id] AND
        H.[date] <= I.[date]
    GROUP BY
      I.[contact_id],
      I.[invoice_id],
      I.[solution_id]
  )
SELECT
  I.[invoice_id],
  I.[contact_id],
  I.[solution_id],
  I.[total],
  I.[date],
  COALESCE(C.[total_applied], 0) AS [credit_assigned],
  H.[total] AS [summarized_total],
  COALESCE(C.[total_applied] - H.[total], 0) AS [credit_available]
FROM
  [invoices] AS I
  INNER JOIN [cte_contact_invoice_summarized_totals] AS H ON
    H.[contact_id] = I.[contact_id] AND
    H.[invoice_id] = I.[invoice_id] AND
    H.[solution_id] = I.[solution_id]
  LEFT JOIN [cte_invoice_credit_dates] AS CD ON
    CD.[invoice_id] = I.[invoice_id]
  LEFT JOIN [credits] AS C ON
    C.[owner_id] = I.[contact_id] AND
    C.[date_applied] = CD.[date_applied]
ORDER BY
  I.[invoice_id],
  I.[solution_id];

Answer 2

You don't need to use window functions, use a sub-query that sums the total of previous invoices. But be sure to use index the table correctly so that performance is not a problem.

There are two sub-queries, one for the previous total sum and another to get the date of the next credit for contact_id.

SELECT  [inv].[invoice_id],
        [inv].[solution_id],
        [inv].[total],
        -- subquery that sums the previous totals
        [cr].[total_applied] - COALESCE((
                                SELECT  SUM([inv_inner].[total])
                                FROM    [dbo].[invoices] AS [inv_inner]
                                WHERE [inv_inner].[solution_id] < [inv].[solution_id]
                            ), 0) AS [credit_available],
        [inv].[date]
FROM    [dbo].[invoices]        [inv]
    LEFT JOIN [dbo].[credits]   [cr]
        ON [cr].[owner_id] = [inv].[contact_id]
        -- here, we make sure that the credit is available for the correct period
        -- invoice date >= credit date_applied
        AND [inv].[date] >= [cr].[date_applied]
        -- and invoice date < next date_applied or tomorrow, in case there are no next date_applied
        AND [inv].[date] < COALESCE((
                            SELECT  MIN([cr2].[date_applied])
                            FROM    [dbo].[credits] [cr2]
                            WHERE [cr2].[owner_id] = [cr].[owner_id]
                                AND [cr2].[date_applied] > [cr].[date_applied]
                        ), GETDATE()+1)
        AND [cr].[credit_id] = 'C00001';

This query works, but it is for this question only. Please study it and adapt to your real world problem.