Sql Query Compare and Sum

Question

I have these problem I need to match the sum a columns to see if they match with the Final Total of the Invoice by Invoice Number ( I am working in a query to do it) Example

Invoice No      Line _no      Total Line  Invoice total   Field I will create
----------------------------------------------------------------------
45                 1            145            300              145
45                 2            165            300              300    Match

46                 1             200           200               200   Match  

47                 1             100           300               100
47                 2             100           300               200 
47                 3             100           300               300   Match

Answer 1

You want a cumulative sum. In SQL Server 2012+, just do:

select e.*,
       (case when InvoiceTotal = sum(InvoiceTotal) over (partition by invoice_no order by line_no)
             then 'Match'
        end)
from example e;

In earlier versions of SQL Server, I would be inclined to do it with a correlated subquery:

select e.*
       (case when InvoiceTotal = (select sum(InvoiceTotal) 
                                  from example e2
                                  where e2.Invoice_no = e.invoice_no and
                                        e2.line_no >= e.line_no
                                 )
             then 'Match'
        end)
from example e;

You can also do this with a cross apply as M Ali suggests.

EDIT:

Now that I think about the problem, you don't need a cumulative sum. That was just how I originally thought of the problem. So, this will work in SQL Server 2008:

select e.*,
       (case when InvoiceTotal = sum(InvoiceTotal) over (partition by invoice_no)
             then 'Match'
        end)
from example e;

You can't get the cumulative sum out (the second to last column) without more manipulation, but the match column is not hard.