Is there a better way to write my SQL statement to eliminate duplicate records ?

Do you know how I can rewrite the joins in the following tables to eliminate duplicate records and I retrieve some records "Where FallCycle = 0" even though I stated "Where FallCycle = 1"

Main fields are as follows:
tblCustomers has a 1 to many relationship to tblProducts

1) tblStatesAll AS S:
    fields a) StateFS (2 character State Codes: for ex: AL, AR, AZ)
             b) MutualFS (contains integer representing # of years)
             c) BondsFS (contains integer representing # of years)
             d) FallCycle (bit value of 1 for Fall States, and a value of 0 for Spring States)
           
For ex: CT is a Spring state (S.FallCycle = 0.
           AK is a Fall state (S.FallCycle = 1).

2) tblCustomers AS C:
    fields a) CustomerNumber (primary key)
            b) State
            c) DateLost

3) tblProducts AS P:
    fields a) CustomerNumber (foreign key)
            b) Property Type (Mutual Fund, Bonds)

The basic process is to Select customers whose tblCustomer state (C.State) value is among the state values in tblStatesAll (S.StateFS) and is considered a Fall State (S.FallCycle = 1).

I select customer's whose product records, based on property type, have a
C.DateLost value LESS THAN 6/30/2005 Minus the number of years in the tblStatesAll table for the Customer's state.

Let's assume the following values for tblStatesAll:
StateFS  MutualFS    BondsFS
NY         2                3

For example, If John Smith lives in NY and has a Bond and he lost the Bond on 6/29/2003,
I perform the following calculation to determine if I select his record.
Is DateLost <= 6/30/2005 - 3 (# of years from tblStatesAll for NY for Bonds) which translates to:
Is 6/29/2003 <= 6/30/2002 ? Since the answer is no, I won't select his record.

DateLost of 6/29/2003
BondFS of 3 as per tblStatesAll for NY customers

I wrote the join as follows but I get some duplicate records:

SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid], C.SecondNameLast AS [Second Name Last], Null AS Relation
       INTO tblFGAllStates
       FROM tblStatesAll
AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON S.StateFS = C.State WHERE S.FallCycle = 1   AND ((C.DateLost <= CAST((2005 - S.MutualFS) AS VARCHAR) + '-06-30' AND  P.PropertyType='OTHER' AND LEN(P.IraCode) = 0 AND S.FallCycle = 1)   OR    (C.DateLost <= CAST((2005 - S.BondsFS) AS VARCHAR) + '-06-30'  AND P.PropertyType='FIXED INCOME' AND LEN(P.IraCode) = 0 AND S.FallCycle = 1)  

If @Prop = 'A'
   BEGIN
     SET @SQL = @SQL + '  AND ((C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-06-30'' AND  P.PropertyType=''OTHER'' AND LEN(P.IraCode) = 0 AND S.FallCycle = 1) '  
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.BondsFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0 AND S.FallCycle = 1) '
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-06-30'' AND P.PropertyType=''MUTUAL FUND'' AND PlanNumber = ''NETWORKED'' AND LEN(P.IraCode) = 0 AND S.FallCycle = 1) '
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-06-30'' AND P.PropertyType=''MUTUAL FUND'' AND P.PlanNumber <> ''NETWORKED'' AND LEN(P.IraCode) = 0 AND S.FallCycle = 1) '
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.CashFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0 AND S.FallCycle = 1) '  
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.StocksFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''EQUITY'' AND LEN(P.IraCode) = 0 AND S.FallCycle = 1)) '
   END
zimmer9Asked:
Who is Participating?
 
mdouganCommented:
Hi zimmer9,
Possibly reiterating what other's have said, but anyway, I copied your SQL and indented it in a way that would help me read all the blocks of parenthesis etc, and didn't quite follow the way you had it coded... I blocked it the way that I would have, and would bet that this would work for you...:

SELECT DISTINCT
       Null As Title,
       C.FirstName AS [First Name],
       C.MiddleInitial AS MI,
       C.LastName AS [Last Name],
       C.SecondNameFirst AS [Second Name First],
       C.SecondNameMid AS [Second Name Mid],
       C.SecondNameLast AS [Second Name Last],
       Null AS Relation
  INTO tblFGAllStates
  FROM tblStatesAll AS S
 INNER JOIN tblCustomers AS C
    ON S.StateFS = C.State
 INNER JOIN tblProducts AS P
    ON C.CustomerNumber = P.CustomerNumber  
 WHERE S.FallCycle = 1  
   AND LEN(P.IraCode) = 0
   AND (
          (C.DateLost <= CAST((2005 - S.MutualFS) AS VARCHAR) + '-06-30'
             AND  P.PropertyType='OTHER'
          )  
        OR    
          (C.DateLost <= CAST((2005 - S.BondsFS) AS VARCHAR) + '-06-30'  
             AND P.PropertyType='FIXED INCOME'
          )
       )

Cheers!
0
 
Jeff CertainCommented:
Using the DISTINCT keyword will eliminate duplicate records:
SELECT DISTINCT * FROM myTable

You can also filter the results of joins after the joins are complete:

SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid], C.SecondNameLast AS [Second Name Last], Null AS Relation
       INTO tblFGAllStates
       FROM tblStatesAll
AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON S.StateFS = C.State   AND ((C.DateLost <= CAST((2005 - S.MutualFS) AS VARCHAR) + '-06-30' AND  P.PropertyType='OTHER' AND LEN(P.IraCode) = 0)  OR   (C.DateLost <= CAST((2005 - S.BondsFS) AS VARCHAR) + '-06-30'  AND P.PropertyType='FIXED INCOME' AND LEN(P.IraCode) = 0)  
WHERE S.FallCycle = 1
0
 
ispalenyCommented:
zimmer9,
You post incorrect number of brackets in query. Can you post a corrected version?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.