get the total amount of several similar invoice numbers only if the total is less than ZERO

Question

question

excelsql987 asked Jan 16, '20

get the total amount of several similar invoice numbers only if the total is less than ZERO

---FOR EXAMPLE:
Invoice number   Line Item  Amount
800			1	-$2000
800			2	-$6000
800			3	+$7000
so I want to pull the SUM of invoice #800  to reflect -$1,000  ONLY 
(NOT -$8000)
This is the code below

USE [CCS]
SELECT ISNULL(be.[FY],'Total') as FY,
SUM(be.[Invoice Amount]) as 'Total Amount',
SUM(CASE WHEN DATEDIFF (DAY,be.[Due Date],ft.[EndDate])<=365 then be.[Invoice Amount] ELSE 0 END) as 'UnderOneYear',
SUM(CASE WHEN DATEDIFF (DAY,be.[Due Date],ft.[EndDate])>365 and DATEDIFF (DAY,be.[Due Date],ft.[EndDate])<=730 
then be.[Invoice Amount] ELSE 0 END) as '1_2 years',
SUM(CASE WHEN DATEDIFF (DAY,be.[Due Date],ft.[EndDate])>730 and DATEDIFF (DAY,be.[Due Date],ft.[EndDate])<=1095 
then be.[Invoice Amount] ELSE 0 END) as '2_3 years',
SUM(CASE WHEN DATEDIFF (DAY,be.[Due Date],ft.[EndDate])>1095 
then be.[Invoice Amount] ELSE 0 END) as 'Over_3_years'
FROM ([dbo].[FiscalDateTable] as ft
INNER JOIN ([dbo].[TB_DATA_All] as be
LEFT JOIN [dbo].[Master Reason Codes List] AS mrc
ON be.[Reason Code] = mrc.[Reason Code])
ON ft.[Fiscal YYYY-MM] = be.FY)
INNER JOIN [dbo].[Master Profit Center List_All Divisions_New] as mpc
ON be.[Profit Center] = mpc.[Profit Centers]
AND be.CoCd = mpc.[Company FY15]
WHERE (be.[Reconciliation Account]) LIKE'%24%' AND mpc.New_Div ='DI' AND be.FY 
BETWEEN '2018-12' AND '2019-12' 
AND be.[Invoice Amount]<0
GROUP BY ( be.[FY]) 
ORDER by be.[FY] ASC
<br>

sql-server-2012

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Jan 16, 2020 at 07:56 AM

The sum of that invoice is -$1000, so I think you'd be better showing us an example where the result you want is not as simple as summing all the line items.

0 ·

Answer 1 · 2020-01-17T09:36:27Z

chris.0120 answered Jan 17, '20

be.[Invoice Amount] < 0 means that you will only be summing rows where the invoice amount is less than 0. I think what you're saying is that instead you want to sum all rows for an invoice and then filter on if the total is less than 0.

For this you want to use the HAVING clause, which fits between the GROUP BY and ORDER BY clauses. This allows you to filter your results post-aggregation.

So I think you want to drop the

AND be.[Invoice Amount] < 0

line and add a line between the GROUP BY and ORDER BY reading:

HAVING SUM(be.[Invoice Amount]) < 0

It's worth noting that HAVING doesn't let you use any column aliases from the SELECT clause, which is why I'm not saying HAVING [Total Amount] < 0

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question