clean data---help--- - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

clean data---help---

Author

Topic

nit115
Starting Member

1 Post

Posted - 2009-04-23 : 16:52:39

Hi All - I need some help fixing some data (2 issues) - I have a couple views that i inner join to get all my data i need. The problem is that is that it duplicates some rows and needs to be cleared and 2) There is a field say "amt" that has a postive (x) value and negative value(-x) (where only x=x and I need to cancel these rows out of my view. For example below. If you see where it has acct2050 -48486.36 and acct2050 +48486.36 - I need to be able to remove these rows from my view - how do i go about doing that --- any help would greatly be appreciated....

acct posted_amt posted_date acct2050 acct2050posted_amt acct2050posted_date funded_amt
3100 45897 3/31/2009 2050 -48486.36 3/31/2009 -26853
3100 45897 3/31/2009 2050 -48486.36 3/31/2009 -1159.88
3100 45897 3/31/2009 2050 -48486.36 3/31/2009 1.63
3100 45897 3/31/2009 2050 -48486.36 3/31/2009 1159.88
3100 45897 3/31/2009 2050 -48486.36 3/31/2009 1390.6
3100 45897 3/31/2009 2050 -48486.36 3/31/2009 25460.77
3100 45897 3/31/2009 2050 48486.36 3/31/2009 -26853
3100 45897 3/31/2009 2050 48486.36 3/31/2009 -1159.88
3100 45897 3/31/2009 2050 48486.36 3/31/2009 1.63
3100 45897 3/31/2009 2050 48486.36 3/31/2009 1159.88
3100 45897 3/31/2009 2050 48486.36 3/31/2009 1390.6
3100 45897 3/31/2009 2050 48486.36 3/31/2009 25460.77

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-23 : 19:32:51

They are all distinct rows due to the last column, the only way to get rid of them is to sum the funded_amt.

something like this:


select
 a.acct, a.posted_amt, a.posted_date, a.acct2050, sum(b.acct2050posted_amt) as acct2050posted_amt, a.acct2050posted_date, a.funded_amt
from (select 
acct, posted_amt, posted_date, acct2050, acct2050posted_date, sum(funded_amt) as funded_amt
from table
group by
acct, posted_amt, posted_date, acct2050, acct2050posted_date
having sum(funded_amt) <> 0) a
inner join table b
on a.acct = b.acct
group by
a.acct, a.posted_amt, a.posted_date, a.acct2050, a.acct2050posted_date, a.funded_amt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources