Good Morning All, I'm back from vacation and wanted to check in to see if there was any other ideas on this issue since I sent this reply on Oct 14th.
Thanks, Mike ----- Original Message ----- From: 'Softtech Support' [email protected] [firebird-support] To: [email protected] Sent: Tuesday, October 14, 2014 7:24 AM Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Greetings Set, I appreciate you joining in. Note: Because STATUS_DATE is a TimeStamp I modified your example for solution A to: SELECT COUNT(DISTINCT DCD.ACCT_ID||'-'||DCD.CASE_ID||'-'||DCD.DEBT_NO) FROM DEBTOR_CASE_DEBT DCD JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID AND AC.CASE_ID = DCD.CASE_ID WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE || ' 00:00:00' AND :V_END_DATE || ' 23:59:59' AND DCD.STATUS_CODE = 'B' AND AC.CLT_ID = :V_CLT_ID Solution A returned accurate results but was painfully slow. 09/01/2014 thru 09/30/2014 took 7.25 secs - 34 Count 01/01/2014 thru 09/30/2014 took 1 Min, 6.312 secs - 196 Count Here is the plan it used: PLAN JOIN (AC INDEX (REFCLIENT457),DCD INDEX (REFDEBT134,IX_DCD_STATUS_DATE_AND_CODE)) REFCLIENT457 is a FK to CLIENT which uses CLT_ID(Integer) for the PK REFDEBT134 is a FK to DEBT which uses ACCT_ID(Integer), DEBT_NO(SmallInt) for the PK IX_DCD_STATUS_DATE_AND_CODE is a new index I just added uses STATUS_DATE(TimeStamp) and STATUS_CODE(Char(1)) Solution B returned inaccurate results but was quick 09/01/2014 thru 09/30/2014 took 0.063 secs - 35 Count 01/01/2014 thru 09/30/2014 took 0.031 secs - 205 Count By changing solution B to the following I was able to determine it was not counting distinct records as in 167565-3-3 was listed twice for 09/01/2014 thru 09/30/2014 and similar duplicates for the YTD results. SELECT DCD.ACCT_ID, DCD.CASE_ID, DCD.DEBT_NO FROM DEBTOR_CASE_DEBT DCD WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE || ' 00:00:00' AND :V_END_DATE || ' 23:59:59' AND DCD.STATUS_CODE = 'B' AND EXISTS(SELECT * FROM ACCT_CASE AC WHERE AC.ACCT_ID = DCD.ACCT_ID AND AC.CASE_ID = DCD.CASE_ID AND AC.CLT_ID = :V_CLT_ID) ORDER BY 1,2,3 Here is the plan it used: PLAN (AC INDEX (PK_CASE)) Set, you mention "The only (minor) thing lacking is a reason for you not wanting CLT_ID included... " I'm not sure what you meant here. CLT_ID is found only in the ACCT_CASE and CLIENT tables. DEBTOR_CASE_DEBT does not include CLT_ID soas to normalize the data, thus the join from DEBTOR_CASE_DEBT to ACCT_CASE to use CLT_ID. Am I missing something? Thanks again to both Martijn and Set for your help, Mike PS: I'll be on vacation starting today thru next Monday, so may be slow to respond. ----- Original Message ----- From: Svein Erling Tysvær [email protected] [firebird-support] To: [email protected] Sent: Tuesday, October 14, 2014 2:23 AM Subject: RE: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? >ACCT_CASE: Case Management table >ACCT_ID INTEGER NOT NULL PK >CASE_ID SMALLINT NOT NULL PK >CLT_ID INTEGER NOT NULL FK to CLIENT table << Need this for the JOIN >DEBTOR_CASE_DEBT: Allows for multiple PERSON's to be associated with a DEBT >ACCT_ID INTEGER NOT NULL PK >CASE_ID SMALLINT NOT NULL PK >DEBT_NO SMALLINT NOT NULL PK >PERSON_ID INTEGER NOT NULL PK >STATUS_DATE TIMESTAMP NOT NULL >STATUS_CODE CHAR(1) NOT NULL >What am I attempting to do? I need to know how many records are in the DEBTOR_CASE_DEBT table that have a STATUS_DATE between '09/01/14' and '09/30/14' >and the STATUS_CODE = 'B" (Bankruptcy Filed) and is for a specific CLT_ID (thus the join to ACCT_CASE to use CLT_ID). I do not want to include the >PERSON_ID when fetching a COUNT() of the record, I only need to know how many debts are in this status for the client. So only concerned with ACCT_ID, >CASE_ID and DEBT_NO. > >So this SQL will return the correct number of records, now I just have to figure out how to return a count in one record. > > SELECT DISTINCT DCD.ACCT_ID, DCD.CASE_ID, DCD.DEBT_NO > FROM DEBTOR_CASE_DEBT DCD > JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID > AND AC.CASE_ID = DCD.CASE_ID > WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE > AND DCD.STATUS_CODE = 'B' > AND AC.CLT_ID = :V_CLT_ID > >Did I provide enough information this time? If not feel free to ask... This is close to a perfect problem description, Mike, well done! The only (minor) thing lacking is a reason for you not wanting CLT_ID included... I can think of two possible solutions: a) SELECT COUNT(DISTINCT DCD.ACCT_ID||'-'||DCD.CASE_ID||'-'||DCD.DEBT_NO) FROM DEBTOR_CASE_DEBT DCD JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID AND AC.CASE_ID = DCD.CASE_ID WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE AND DCD.STATUS_CODE = 'B' AND AC.CLT_ID = :V_CLT_ID b) SELECT COUNT(*) FROM DEBTOR_CASE_DEBT DCD WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE AND DCD.STATUS_CODE = 'B' AND EXISTS(SELECT * FROM ACCT_CASE AC WHERE AC.ACCT_ID = DCD.ACCT_ID AND AC.CASE_ID = DCD.CASE_ID AND AC.CLT_ID = :V_CLT_ID) Myself, I generally prefer to have single field primary keys, one benefit of this is that you can use solution a) without having to do tricks with concatenation. HTH, Set ---------------------------------------------------------------------------- This email is free from viruses and malware because avast! Antivirus protection is active. --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com
