January 29, 2008 at 5:42 am
I have a table containing invoiceLines. An invoice has many lines, some debit and some credit.
lineID invoiceNumber amount type
1 100001 5000 DBT
2 100001 350 CRT
I need to filter out only invoices with both debit and credit lines. How do i do this using just this invoice table?
January 29, 2008 at 6:01 am
Assuming the only two values allowed in the type column are CRT and DBT, something like this:
SELECT InvoiceNumber, MAX(type), MIN(type)
FROM MyTable
GROUP BY InvoiceNumber
HAVING MAX(type) > MIN(type)
John
January 29, 2008 at 1:51 pm
You could also do a sum calculation in the having clause.
SELECT
InvoiceNumber
FROM MyTable
GROUP BY InvoiceNumber
HAVINGSUM(CASE WHEN [type] = 'DBT' THEN 1 ELSE 0 END) > 0 AND
SUM(CASE WHEN [type] = 'CRT' THEN 1 ELSE 0 END) > 0
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply