Steve,

Thanks so much. I should have thought of doing that...

SELECT COUNT(DISTINCT CAST(CE.ACCT_ID || '-' || CE.CASE_ID || '-' ||
CE.DEBT_NO as varchar(20))) FROM CAW_EXPORT CE

Works as expected and there will never be nulls in any field in this table.

Thanks Again,

Mike

----- Original Message -----
From: "Steve Wiser" <steve@...>
To: <firebird-support@yahoogroups.com>
Sent: Wednesday, June 29, 2011 8:07 AM
Subject: Re: [firebird-support] How do I return a count of (2) distinct
records when there are multiple records in a table?


> couldn't you do:
>
> select count( distinct cast(acct_id || '-' || case_id || '-' || debt_no as
> varchar(20))) from TABLE_NAME?
>
> Maybe that is an ugly solution though and does not handle nulls in those 3
> fields (you would have to use coalesce for that).
>
> -steve
>
> --
> Steve Wiser
> President
> Specialized Business Software
> 6325 Cochran Road, Unit 1
> Solon, OH 44139
>
> www.specializedbusinesssoftware.com
> www.docunym.com
> (440) 542-9145 - fax (440) 542-9143
> Toll Free: (866) 328-4936
>
>
>
>
> On Wed, Jun 29, 2011 at 8:48 AM, SoftTech <miket@...> wrote:
>
>> **
>>
>>
>> Greetings All,
>>
>> I have a temporary table in my database that stores activities on a debt
>> that will eventually be exported to another company.
>>
>> In the following scenario, there are two debts 1010-6-12 and 1010-7-13
>> that
>>
>> have activities on them.
>>
>> During our export we combine activities for each debt into one memo line.
>>
>> Is it possible to write a SQL select statement to take the following data
>> and return a count of (2) records?
>>
>> SITE_ID, ACCT_ID, CASE_ID, DEBT_NO, MEMO_DATE, MEMO
>>
>> 3008, 1010, 6, 12, 6/28/2011 6:44:57 AM, Payment Plan Created
>> 3008, 1010, 6, 12, 6/28/2011 6:46:15 AM, Payment Plan Deleted
>> 3008, 1010, 6, 12, 6/28/2011 7:07:19 AM, Inbound Call Received: Positive
>> Outcome
>> 3008, 1010, 6, 12, 6/28/2011 7:09:13 AM, Inbound Call Received: Positive
>> Outcome
>> 3008, 1010, 6, 12, 6/28/2011 7:10:26 AM, Outbound Call - No Answer
>> 3008, 1010, 6, 12, 6/28/2011 7:26:15 AM, Dialer - Left Message
>> 3008, 1010, 6, 12, 6/28/2011 8:14:54 AM, Settlement Letter
>> 3008, 1010, 7, 13, 6/28/2011 6:44:57 AM, Payment Plan Created
>> 3008, 1010, 7, 13, 6/28/2011 6:46:15 AM, Payment Plan Deleted
>> 3008, 1010, 7, 13, 6/28/2011 7:09:13 AM, Inbound Call Received: Positive
>> Outcome
>> 3008, 1010, 7, 13, 6/28/2011 7:26:15 AM, Dialer - No Answer
>>
>> Thanks to all that reply,
>>
>> Mike
>>
>>
>>
>>
>> This message and any files transmitted with it may contain information
>> that
>> is privileged, confidential, and exempt from disclosure under applicable
>> law. They are intended solely for the use of the intended recipient. If
>> you are not the intended recipient, distributing, copying, disclosing, or
>> reliance on the contents of this communication is strictly prohibited.
>> If
>> this has reached you in error, kindly destroy this message and notify the
>> sender immediately. Thank you for your assistance.
>>
>> We attempt to sweep harmful content (e.g. viruses) from e-mail and
>> attachments, however we cannot guarantee their safety and can accept no
>> liability for any resulting damage. The recipient is responsible to
>> verify
>> the safety of this message and any attachments before accepting them.
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>
> --
> MailScanner Virus/Spam/Malware: PASS (GZ)
>
>