Distinct Question - 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

General SQL Server Forums

New to SQL Server Programming

Distinct Question

Author

Topic

rpc86
Posting Yak Master

200 Posts

Posted - 2006-02-14 : 22:52:51

hi guys, thanks for the recent help you advised to me.

Now my next problem is how to distict BuyerCodes with different amount.

here is my table



Table Name: PaymentSchedule

Buyercode  DueDate                     Amount               
---------- --------------------------- -------------------- 
E24-5336   2005-10-06 00:00:00.000     115849.61
E24-5336   2005-11-06 00:00:00.000     117297.73
E24-5336   2005-12-06 00:00:00.000     118763.95
E24-5336   2006-01-06 00:00:00.000     120248.50
E24-5336   2006-02-06 00:00:00.000     121751.60

E24-5337   2001-03-04 00:00:00.000     53851.99
E24-5337   2001-04-04 00:00:00.000     53851.99
E24-5337   2001-05-04 00:00:00.000     53851.99
E24-5337   2001-06-04 00:00:00.000     53851.99
E24-5337   2001-07-04 00:00:00.000     53851.99

E24-5350   2001-04-07 00:00:00.000     63162.44
E24-5350   2001-05-07 00:00:00.000     63162.44
E24-5350   2001-06-07 00:00:00.000     63162.44

N10-5023   2005-11-30 00:00:00.000     21946.53
N10-5023   2005-12-30 00:00:00.000     22129.42
N10-5023   2006-01-30 00:00:00.000     22313.83
N10-5023   2006-02-28 00:00:00.000     22499.78
N10-5023   2006-03-30 00:00:00.000     22687.28

The result I need is the following:


BuyerCode
---------
E24-5336   
N10-5023

Because these 2 BuyerCode have different amount for every duedates.

Many thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-14 : 23:00:54

[code]select Buyercode
from PaymentSchedule
group by Buyercode
having count(*) > 1[/code]

----------------------------------
'KH'
everything that has a beginning has an end

rpc86
Posting Yak Master

200 Posts

Posted - 2006-02-14 : 23:02:56

But it will include these 2 buyercodes

E24-5337
E24-5350

These 2 code has the same amount for each duedate unlike the desired result.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-14 : 23:09:49

Hi,
Try this..
select Buyercode
from (select distinct buyercode, amount from PaymentSchedule )a
group by Buyercode
having count(*) > 1

rpc86
Posting Yak Master

200 Posts

Posted - 2006-02-14 : 23:13:39

shallu1 gupta, thanks

The code is nearly close to my result but, there must have a condition for the duedates that are consecutively different by amount.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-14 : 23:23:50

quote:
different amount for every duedates.

Does this means all the amount must be different ?

select 	Buyercode
from	PaymentSchedule p
group by Buyercode
having count(distinct Amount) = (select count(*) from PaymentSchedule x where x.Buyercode = p.Buyercode)

----------------------------------
'KH'
everything that has a beginning has an end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-15 : 00:35:08

Other method is


Select buyercode from
(
	select distinct buyercode,amount  from PaymentSchedule  
) T
group by buyercode having count(buyercode)<>1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources