How to filter out unwanted data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to filter out unwanted data

Author

Topic

fdtoo
Starting Member

28 Posts

Posted - 2006-04-25 : 04:23:49

I have the following fields in table A:

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:

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:

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:

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:

Date | Descrip | Amt Dr
----------------------------------
01/02/2006 | 740240 |(2,400.00)
14/02/2006 | 740241 |(3,000.00)

Can i achieve this? Please help.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-25 : 04:47:26

I guess desired output and your sample data doesnt match with your requirement..

check look at the following query ..


Declare @Tbl1 Table 
(
[Date] Varchar(10),
Descrip Varchar(100),
Amt float
)

Insert @Tbl1 
Select '01/02/2006' , '740240' ,2400.00 Union All
Select '14/02/2006', '740241' ,3000.00 Union All
Select '15/02/2006', '41142' ,1800.00 Union All
Select '20/02/2006','41142' ,2700.00 Union All
Select '25/02/2006', '740245', 5200.00

Declare @Tbl2 Table 
(
[Date] Varchar(10),
Descrip Varchar(100),
Amt float
)
Insert @Tbl2
Select '02/02/2006 ' , '88258' ,1400.00 Union All
Select '17/02/2006', '740244' ,1500.00 Union All
Select '25/02/2006', '740245' ,5200.00 

SELECT
CASE WHEN Amt > 0 THEN [Date] ELSE '' END,
CASE WHEN Amt > 0 THEN Descrip ELSE 0 END,
CASE WHEN Amt > 0 THEN (Amt * 2)-Amt ELSE 0 END AS [Add Back]
FROM @Tbl1
WHERE Descrip Not in (Select Descrip  From @Tbl2)
GROUP BY [Date],Amt,Descrip

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-25 : 06:20:42

Hi fdtoo,

chiragkhabaria is right - there seems to be some inconsistencies in your example. Can you please check it and disambiguate.

Perhaps an exists statement would be useful? See below for an example...

--data
set dateformat dmy
Declare @Tbl1 Table ([Date] Varchar(10), Descrip Varchar(100), Amt float)

Insert @Tbl1 
          Select '01/02/2006' , '740240' ,-2400.00
Union All Select '14/02/2006', '740241' ,-3000.00
Union All Select '15/02/2006', '41142' ,1800.00
Union All Select '20/02/2006','41142' ,2700.00
Union All Select '25/02/2006', '740245', 5200.00

Declare @Tbl2 Table ([Date] Varchar(10), Descrip Varchar(100), Amt float)
Insert @Tbl2
          Select '02/02/2006 ' , '88258' ,1400.00
Union All Select '17/02/2006', '740244' ,-1500.00
Union All Select '25/02/2006', '740245' ,5200.00 

--calculation
select * from @Tbl1 a 
where not exists (select * from @Tbl2 where [Date] = a.[Date] and Descrip = a.Descrip)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources