Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2005-05-16
3
Medium Priority
?
233 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:zimmer9
3 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 14012116
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
 
LVL 13

Expert Comment

by:ispaleny
ID: 14012352
zimmer9,
You post incorrect number of brackets in query. Can you post a corrected version?
0
 
LVL 18

Accepted Solution

by:
mdougan earned 2000 total points
ID: 14013442
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question