group by - 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 2000 Forums

Transact-SQL (2000)

group by

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-07-18 : 22:04:26

Hi friends
i need ur advise on this query.I've 2 fields in a table (patient,invoice).
field invoice contains invoice no for a patient.this no can be duplicated for the same patient but not for the different patient.
i mean we can have data like this

patient invoice
John I001
John I001
Paul I002

but there should not be like following
patient invoice
John I001
John I001
Paul I001 --its wrong
Paul I002

my question is how can i find out such duplicates ?
Thanks

Cheers

Ravenn
Starting Member

7 Posts

Posted - 2005-07-18 : 23:40:09

Basically you need tro write a trigger if invoice numbers can be stored twice for the same patient.

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-07-19 : 00:13:22

Ravenn thanks for the post.
at the moment i cant write triggers only need sql stmt fix existing data.
any ideas will be appreciated.

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-19 : 01:01:40

You have to tell us how you say that Paul I001 is wrong
Do you want to keep only the patient with highest invoice?

Madhivanan

Failing to plan is Planning to fail

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-19 : 01:03:49

I feel like I may have over-complicated this query, but here's what I came up with:

SELECT DISTINCT YourTable.patient, YourTable.invoice
FROM YourTable
JOIN
(SELECT A.invoice, COUNT(*) AS cnt
FROM (SELECT DISTINCT patient, invoice FROM YourTable) AS A
GROUP BY A.invoice
HAVING COUNT(*) > 1) AS B
ON YourTable.invoice = B.invoice

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-07-19 : 01:09:31

quote:
Originally posted by madhivanan

You have to tell us how you say that Paul I001 is wrong
Do you want to keep only the patient with highest invoice?

Madhivanan,bcoz the invoice no is already taken by another patient and this guy should get a new unique invoice no

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-07-19 : 01:24:03

quote:
Originally posted by nosepicker

I feel like I may have over-complicated this query, but here's what I came up with:

Hi dude
am sure this works definetly in sql server but am using visual foxpro.
dont think foxpro supports this query where it using using results from sub query in join condition.it is giving me syntax error!!

looks like i need to do it in 2 separate queries :(

Cheers

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-19 : 01:32:17

Sorry, this is a SQL Server site, so I don't know how to translate it to V. Foxpro.

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-07-19 : 01:34:42

ya i know.most of the the queries work in v foxpro only few ones like above dont.

Cheers

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2005-08-01 : 02:19:58

hey why dont u try this
might be u will have to change some syntax but concept is like this only
select A.patient,B.invoice
from tableA A , (select Invoice from TableA
where A.patient = B.patient) B

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-08-01 : 16:39:10

Thanks mate.
finally i resolved it with following query
SELECT patient, invoice;
FROM Patients PInvoice;
WHERE PInvoice.Invoice IN;
(SELECT Invoice FROM Patients PDup WHERE PDup.Patient # PInvoice.Patient);
GROUP BY PInvoice.patient, PInvoice.invoice;

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources