1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to filter out unwanted data

Discussion in 'SQL Server' started by fdtoo, Apr 25, 2006.

  1. fdtoo

    fdtoo New Member

    Joined:
    Apr 12, 2006
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    0
    I have the following fields in table A:
    Code:
    Date	    |  Descrip | Amt Dr  
    ---------------------------------- 
    01/02/2006  |  740240  |(2,400.00)
    14/02/2006  |  740241  |(3,000.00)
    15/02/2006  |  41142   | 1,800.00 	
    20/02/2006  |  41142   | 2,700.00 	
    25/02/2006  |  740245  | 5,200.00
    
    I have the following fields in table B:
    Code:
    Date	    | Descrip |  Amt Dr  
    ---------------------------------- 
    02/02/2006  |88258    |  1,400.00 
    17/02/2006  |740244   | (1,500.00)
    25/02/2006  |740245   |  5,200.00	
    
    There are no referencial key between TableA & TableB,
    What i want is to extract the date,descrip & Amt data from
    Table A where it's Descrip data is not the same as the data
    in Table B's Descrip column.

    My sql syntax is as follows:
    Code:
    SELECT 
    CASE WHEN TableA.Amt < 0 THEN TableA.[Date] ELSE 0 END, 
    CASE WHEN TableA.Amt < 0 THEN TableA.Descrip ELSE 0 END, 
    CASE WHEN TableA.Amt < 0 THEN TableA.Amt - (TableA.Amt * 2)ELSE 0 END AS [Add Back]
    FROM TableA,TableB
    WHERE TableA.Descrip <> TableB.Descrip
    GROUP BY TableA.Date,TableA.Amt,TableA.Descrip
    
    The output of the above is as follows:
    Code:
    Date	    | Descrip |  Amt Dr  
    ---------------------------------- 
    01/02/2006  |  740240  |(2,400.00)
    14/02/2006  |  740241  |(3,000.00)
    15/02/2006  |    0         |     0
    20/02/2006  |    0         |     0
    25/02/2006  |  740245  |  5,200.00
    
    Notice that, descrip with 740245 appearing in both tables
    is what the sql should filter out, but failed to do so,
    what i want is as below:
    Code:
    Date	    | Descrip |  Amt Dr  
    ---------------------------------- 
    01/02/2006  |  740240  |(2,400.00)
    14/02/2006  |  740241  |(3,000.00)
    
    Can i achieve this? Please help.
     
    Last edited by a moderator: Apr 25, 2006
  2. coderzone

    coderzone Super Moderator

    Joined:
    Jul 25, 2004
    Messages:
    734
    Likes Received:
    37
    Trophy Points:
    0
    Why do you need the group by clause the row that is coming unwanted check if there is any traling spaces in any of the tables because those record that are equal in both table should not be coming according to the Where clause.
     

Share This Page