Comparing Sql Queries and Sums
I have this problem, I need to match the amount to the columns to see if they match the invoice grand total by invoice number (I am working in a query to do this) 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
source to share
Is this what you are looking for? I think the subquery is what you are asking for, but my guess is to get the end result similar to everything.
select t."Invoice No", t."Line no_", t."Invoice total",
calcTotals.lineNum as calcSum, case when t."Invoice total" = calcTotals.lineNum then 'matched' else 'not matched' end
from [table] t
inner join (
select "Invoice No" as invoiceNumber,
sum("Line _no") as lineNum
from [table]
group by "Invoice No"
) calcTotals on t."Invoice No" = calcTotals.invoiceNumber
source to share
You want to receive the cumulative amount. 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 tend to do this 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 the help cross apply
as M Ali suggests.
EDIT:
Now when I think about the problem, you don't need a cumulative sum. This is how I thought about 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 (from the second to the last column) without some additional manipulation, but the column is match
n't hard.
source to share
Configuring MS SQL Server 2008 schema :
CREATE TABLE TEST(InvoiceNo INT, Line_no INT, TotalLine INT, InvoiceTotal INT)
INSERT INTO TEST VALUES
(45 ,1 ,145 ,300),
(45 ,2 ,165 ,300),
(46 ,1 ,200 ,200),
(47 ,1 ,100 ,300),
(47 ,2 ,100 ,300),
(47 ,3 ,100 ,300)
Request 1 :
SELECT t.[InvoiceNo]
,t.[Line_no]
,t.[TotalLine]
,t.[InvoiceTotal]
,C.Grand_Total
,CASE WHEN C.Grand_Total = t.[InvoiceTotal]
THEN 'Match' ELSE '' END AS [Matched]
FROM TEST t
CROSS APPLY (SELECT SUM([TotalLine]) AS Grand_Total
FROM TEST
WHERE [InvoiceNo] = t.[InvoiceNo]
AND [Line_no] < = t.[Line_no]) C
Results :
| INVOICENO | LINE_NO | TOTALLINE | INVOICETOTAL | GRAND_TOTAL | MATCHED |
|-----------|---------|-----------|--------------|-------------|---------|
| 45 | 1 | 145 | 300 | 145 | |
| 45 | 2 | 165 | 300 | 310 | |
| 46 | 1 | 200 | 200 | 200 | Match |
| 47 | 1 | 100 | 300 | 100 | |
| 47 | 2 | 100 | 300 | 200 | |
| 47 | 3 | 100 | 300 | 300 | Match |
source to share