April 4, 2005 at 9:53 am
Good morning,
I have a table Transactions_Line with the following fields:
Fund,
Trans_Type
Trans_Amount
Trans_Type can be a Debit or Credit.
In a stored procedure, I would like to fill a temp table (#temp) with the total
Debits and total Credits for each Fund in the Transactions_Line table.
The following SELECTS do what I want individually but how could I consolidate
all into #temp so that I have Fund, Total Debits, Total Credits?
SELECT Fund, ISNULL(SUM(Trans_Amount)*(-1),0) 'Total Debits'
FROM dbo.Transactions_Line
WHERE Trans_Type = 'D'
GROUP BY Fund
SELECT Fund, ISNULL(SUM(Trans_Amount),0) 'Total Credits'
FROM dbo.Transactions_Line
WHERE Trans_Type = 'C'
GROUP BY Fund
Thank you!
jmatt
April 4, 2005 at 10:41 am
Try this one:
SELECT Fund, ISNULL(SUM(CASE WHEN Trans_Type = 'D' THEN Trans_Amount * -1 ELSE 0 END), 0) AS 'Total Debits',
ISNULL(SUM(CASE WHEN Trans_Type = 'C' THEN Trans_Amount ELSE 0 END), 0) AS 'Total Credits'
FROM dbo.Transactions_Line
GROUP BY Fund
April 4, 2005 at 10:53 am
Brilliant!
Thank you very much.
jmatt
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply