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 remove duplicate balance

Author  Topic 

nt4vn
Yak Posting Veteran

98 Posts
Posted - 2008-06-18 : 10:27:13
Please help me how to write a code to eliminate duplicated balance on report. Here the data:

Account1: 111-222-3333
Balance: 1,7,426.98 (it repeated in the database for 3 different day: For example:
- On 05/05/2008, balance listed = 1,742,698
- One 06/18/2008, balance listed = 1,742,698
- On 06/15/2008, balance listed = 1,742,698
I used sum(balance), then it total all 3, but it should be one balance = 1,742,698

Anyone could help..thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-18 : 10:28:50
may be sum(distinct balance)
Go to Top of Page

nt4vn
Yak Posting Veteran

98 Posts
Posted - 2008-06-18 : 10:32:03
quote:
Originally posted by visakh16

may be sum(distinct balance)


I tried, but it does not work.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-18 : 10:33:04
Try setting "Ignore duplicate row" option in reporting engine.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-18 : 10:46:02
Can you provide sample dataset?
Go to Top of Page

nt4vn
Yak Posting Veteran

98 Posts
Posted - 2008-06-18 : 10:58:50
quote:
Originally posted by visakh16

Can you provide sample dataset?


ID Balance DAF_DATE
111222333 150000.00 2008-05-12
111222333 162000.00 2008-06-02
111222333 162000.00 2008-06-02
111222333 0.00 2008-06-02
111222333 0.00 2008-06-02
111222333 162000.00 2008-06-02

Thanks, I am thinking of using max function ex: max(balance)....to get the maximum balance
Go to Top of Page

jdaman
Constraint Violating Yak Guru

354 Posts
Posted - 2008-06-18 : 11:01:19
Are you after the maximum balance or the most recent balance?
Go to Top of Page

nt4vn
Yak Posting Veteran

98 Posts
Posted - 2008-06-18 : 11:05:26
quote:
Originally posted by jdaman

Are you after the maximum balance or the most recent balance?


That was what I thought...but not sure if that is right...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-18 : 11:07:03
quote:
Originally posted by nt4vn

quote:
Originally posted by visakh16

Can you provide sample dataset?


ID Balance DAF_DATE
111222333 150000.00 2008-05-12
111222333 162000.00 2008-06-02
111222333 162000.00 2008-06-02
111222333 0.00 2008-06-02
111222333 0.00 2008-06-02
111222333 162000.00 2008-06-02

Thanks, I am thinking of using max function ex: max(balance)....to get the maximum balance

How did duplicate records come for same day with different balance values? What's your business rule for this case? You need to just take any one of non zero balance? Is there a chance where there can be multiple non zero balances for same day?
Go to Top of Page

jdaman
Constraint Violating Yak Guru

354 Posts
Posted - 2008-06-18 : 11:08:03
This will give you the most recent maximum balance:
select  a.ID, max(a.Balance) as Balance
from    YourTable a
join    ( select    ID, max(DAF_DATE) as DAF_DATE
          from      YourTable
          group by  ID
        ) b on a.ID = b.ID
               and a.DAF_DATE = b.DAF_DATE
group by a.ID
Go to Top of Page

nt4vn
Yak Posting Veteran

98 Posts
Posted - 2008-06-18 : 11:35:14
quote:
Originally posted by visakh16

quote:
Originally posted by nt4vn

quote:
Originally posted by visakh16

Can you provide sample dataset?


ID Balance DAF_DATE TYPE
111222333 150000.00 2008-05-12 O
111222333 162000.00 2008-06-02 S
111222333 162000.00 2008-06-02 S
111222333 0.00 2008-06-02 0
111222333 0.00 2008-06-02 0
111222333 162000.00 2008-06-02 S

Thanks, I am thinking of using max function ex: max(balance)....to get the maximum balance

How did duplicate records come for same day with different balance values? What's your business rule for this case? You need to just take any one of non zero balance? Is there a chance where there can be multiple non zero balances for same day?


Actually there is a type for these transaction that I just found out. Two types: o=original, s=supllemental. So I was confirm to go with max(balance)with type=s. Thanks, (SEE I ADD ANOTHER COLUMN 'TYPE' FOR DATA EXAMPLE.
Go to Top of Page

jdaman
Constraint Violating Yak Guru

354 Posts
Posted - 2008-06-18 : 11:39:22
quote:
Originally posted by nt4vn
Actually there is a type for these transaction that I just found out. Two types: o=original, s=supllemental. So I was confirm to go with max(balance)with type=s. Thanks, (SEE I ADD ANOTHER COLUMN 'TYPE' FOR DATA EXAMPLE.


Would the correct amount then be the original transaction amount plus any supplement transactions for a given date or are you only after the original amount?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-18 : 11:51:56
quote:
Originally posted by nt4vn

quote:
Originally posted by visakh16

quote:
Originally posted by nt4vn

quote:
Originally posted by visakh16

Can you provide sample dataset?


ID Balance DAF_DATE TYPE
111222333 150000.00 2008-05-12 O
111222333 162000.00 2008-06-02 S
111222333 162000.00 2008-06-02 S
111222333 0.00 2008-06-02 0
111222333 0.00 2008-06-02 0
111222333 162000.00 2008-06-02 S

Thanks, I am thinking of using max function ex: max(balance)....to get the maximum balance

How did duplicate records come for same day with different balance values? What's your business rule for this case? You need to just take any one of non zero balance? Is there a chance where there can be multiple non zero balances for same day?


Actually there is a type for these transaction that I just found out. Two types: o=original, s=supllemental. So I was confirm to go with max(balance)with type=s. Thanks, (SEE I ADD ANOTHER COLUMN 'TYPE' FOR DATA EXAMPLE.

So whats balance you're looking at/ Original ones or supplemented ones or sum of all?
Go to Top of Page

nt4vn
Yak Posting Veteran

98 Posts
Posted - 2008-06-18 : 22:34:09
quote:
Originally posted by visakh16

quote:
Originally posted by nt4vn

quote:
Originally posted by visakh16

quote:
Originally posted by nt4vn

quote:
Originally posted by visakh16

Can you provide sample dataset?


ID Balance DAF_DATE TYPE
111222333 150000.00 2008-05-12 O
111222333 162000.00 2008-06-02 S
111222333 162000.00 2008-06-02 S
111222333 0.00 2008-06-02 0
111222333 0.00 2008-06-02 0
111222333 162000.00 2008-06-02 S

Thanks, I am thinking of using max function ex: max(balance)....to get the maximum balance

How did duplicate records come for same day with different balance values? What's your business rule for this case? You need to just take any one of non zero balance? Is there a chance where there can be multiple non zero balances for same day?


Actually there is a type for these transaction that I just found out. Two types: o=original, s=supllemental. So I was confirm to go with max(balance)with type=s. Thanks, (SEE I ADD ANOTHER COLUMN 'TYPE' FOR DATA EXAMPLE.

So whats balance you're looking at/ Original ones or supplemented ones or sum of all?


I will be using the supplemented ones.
Go to Top of Page

nt4vn
Yak Posting Veteran

98 Posts
Posted - 2008-06-18 : 22:36:13
quote:
Originally posted by jdaman

quote:
Originally posted by nt4vn
Actually there is a type for these transaction that I just found out. Two types: o=original, s=supllemental. So I was confirm to go with max(balance)with type=s. Thanks, (SEE I ADD ANOTHER COLUMN 'TYPE' FOR DATA EXAMPLE.


Would the correct amount then be the original transaction amount plus any supplement transactions for a given date or are you only after the original amount?


With the above data example, $162000 is the right amount I suppose to take. Thanks,
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-19 : 02:46:23
quote:
Originally posted by nt4vn

quote:
Originally posted by jdaman

quote:
Originally posted by nt4vn
Actually there is a type for these transaction that I just found out. Two types: o=original, s=supllemental. So I was confirm to go with max(balance)with type=s. Thanks, (SEE I ADD ANOTHER COLUMN 'TYPE' FOR DATA EXAMPLE.


Would the correct amount then be the original transaction amount plus any supplement transactions for a given date or are you only after the original amount?


With the above data example, $162000 is the right amount I suppose to take. Thanks,

may be this
SUM(DISTINCT CASE WHEN TYPE='S' THEN Balance ELSE 0 END)
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site