This is a toughie

This is a toughie

Post by Jim Warre » Thu, 06 May 1999 04:00:00



If you are admitted to any of the 180 hospitals in our state you are given
what should be a MRN (Medical Record Number). You would retain this number
at this hospital for life. If you go to another hospital you would recieve a
different MRN for that hospital ID. IF you are readmitted to any of these
hospitals you would have the same MRN that hospital originally gave you Some
one at hospital 98768 could have the same MRN that you have at hospid 96300
.My problem

Table MIDB
Field Mkey unique identifier
Field Hospid 5 digit code
Field MRN  7 digit not unique to hospid (multiple admitance and discharge)

I would like to examine the MRN's with multiple admittances to each hospid
how would I group by hospid and MRN to find dups at that hospid?

Thanks Much
Jim

Hope this doesn't confuse you as much as it does me!

 
 
 

This is a toughie

Post by Nick Drocha » Thu, 06 May 1999 04:00:00


Jim,

You could do something like:
select *,count(*) from midb group by HospID, MRN

You will get all the fields, plus a count column that will be grouped by
MRN's at each HospID.  I'm guessing that it's the "group by" clause that you
are not familiar with.  It's quite easy to use, as you can see.

HTH,
Nick


Quote:> If you are admitted to any of the 180 hospitals in our state you are given
> what should be a MRN (Medical Record Number). You would retain this number
> at this hospital for life. If you go to another hospital you would recieve
a
> different MRN for that hospital ID. IF you are readmitted to any of these
> hospitals you would have the same MRN that hospital originally gave you
Some
> one at hospital 98768 could have the same MRN that you have at hospid
96300
> .My problem

> Table MIDB
> Field Mkey unique identifier
> Field Hospid 5 digit code
> Field MRN  7 digit not unique to hospid (multiple admitance and discharge)

> I would like to examine the MRN's with multiple admittances to each hospid
> how would I group by hospid and MRN to find dups at that hospid?

> Thanks Much
> Jim

> Hope this doesn't confuse you as much as it does me!


 
 
 

This is a toughie

Post by Christopher Vea » Thu, 06 May 1999 04:00:00


select hospid, MRN, count(MRN) group by hospid, MRN having count(MRN) >
1
 
 
 

This is a toughie

Post by Anders Altber » Thu, 06 May 1999 04:00:00


SELECT hospid, mrn, COUNT(*) FROM Midb ;
GROUP BY hospid, mrn HAVING COUNT(*) > 1
If you want the complete records that are part of a muliple admittance set:
SELECT * FROM Midb AS M1 WHERE EXISTS ;
(SELECT * FROM Midb AS M2 GROUP BY hospid, mrn
 HAVING COUNT(*) > 1 WHERE M1.hospid = M2.hospid ;
AND M1.mnr = M2.mrn )

-Anders


Quote:> If you are admitted to any of the 180 hospitals in our state you are given
> what should be a MRN (Medical Record Number). You would retain this number
> at this hospital for life. If you go to another hospital you would recieve
a
> different MRN for that hospital ID. IF you are readmitted to any of these
> hospitals you would have the same MRN that hospital originally gave you
Some
> one at hospital 98768 could have the same MRN that you have at hospid
96300
> .My problem

> Table MIDB
> Field Mkey unique identifier
> Field Hospid 5 digit code
> Field MRN  7 digit not unique to hospid (multiple admitance and discharge)

> I would like to examine the MRN's with multiple admittances to each hospid
> how would I group by hospid and MRN to find dups at that hospid?

> Thanks Much
> Jim

> Hope this doesn't confuse you as much as it does me!

 
 
 

1. Toughie! Using SET IDENTITY in trigger

Developing in Access, inserting into table 1 with an identity with a
trigger that inserts a record into table 2 also with an identity,

Fixed this by setting up a dummy table 3 with an identity, grabbing

IDENTITY_INSERT ON for table 3 and inserting the original identity

Any user running this gets:
Msg 8104, Level 16, State 1
Current user is not the DBO or object owner for table 'Set_ID', unable
to perform SET operation

I also tried putting the IDENTITY ON statement in a stored procedure
and giving rights to the sp to the user, to no avail.

On-line Help mentions SET IDENTITY use in triggers.  How do I give db
users rights to set this property on my table?

Thx,
Gaby
UnionWare Inc.

2. Which DB connection should I use?

3. Toughie: multiple ADO recordsets fail using a SP

4. Export a table from SQL server to Excel

5. Another Toughie! Run Script from Value List Selection

6. What is Isam - book wanted

7. Toughie here...

8. DATETIME in Select

9. I am getting this message when i am tring to export or import anything using

10. error ORA-01855: AM/A.M. or PM/P.M. required

11. Busy Day = Slowdown from 12 AM - 5 AM

12. Use of @am, Am I dumb?

13. I am a pain in the butt, however I am learning !