My original report returns 3 records for account ABC, 1 with an approved status and 2 with a suspended status. The only difference in these lines is the status and billing number. All other data is identical.
I'm trying to create a variation of this report that would return 1 line for Account ABC with a column that displays the count for approved accounts and another column with the count for suspended accounts.
In the new report, there would be an Approved Accounts column with a value of 1 and a Suspended Accounts column with a value of 2.
I'm using a MAX function to return only 1 line. The issue I'm having is that 2 records with the suspended status are identical except for the Billing Number.
If I remove the billing number from the SQL then the results only return 1 suspended and 1 approved account. I need the SQL to return 1 line with 1 in the approved column and 2 in the suspended column
Here is some sample data:
Acct# Bill# Name Location Status
ABC Bill1 ABC Co 123456 Approved
ABC Bill2 ABC Co 123456 Suspended
ABC Bill3 ABC Co 123456 Suspended
Any suggestions would be greatly appreciated. Thanks for your help.....
Solved! Go to Solution.