need counts of unique patients

Question

need counts of unique patients

syam.tunuguntla

SSC Veteran

Points: 224
More actions
June 15, 2017 at 9:02 am

#338186

Friends,
i am counting unique patients that were referred by a doctor.i simply used distinct count by referring doctor to get it. but modified requirement says they want to see the where the patient went for treatment location field. as soon as i added this field my distinct patient count changes which is correct.
but how to keep the unique count of patients that was there before i add the treatment location field. because 1 patient can get treatment at several places, hence the change in count. but i need to count him as 1 patient.
thanks in advance
sam

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

syam.tunuguntla - Thursday, June 15, 2017 9:02 AM
Friends,
i am counting unique patients that were referred by a doctor.i simply used distinct count by referring doctor to get it. but modified requirement says they want to see the where the patient went for treatment location field. as soon as i added this field my distinct patient count changes which is what i expect.
but how to keep the unique count of patients that was there before i add the treatment location field. because 1 patient can get treatment at several places, hence the change in count
thanks in advance
sam

Can you post sample data and expected results? The best way to post it (DDL, insert statements) is explained on the articles in my signature.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 2

Further on Luis's respond, what have you tried so far?
😎

Steve Jones - SSC Editor SSC Guru Points: 738632 More actions · Answer 3

Think carefully about what makes a unique patient. There should be some PK if this is an entity in a table, but if not, how do you determine that a row containing a patient is unique?

syam.tunuguntla SSC Veteran Points: 224 More actions · Answer 4

here it is in detail

select
referringdoctor, city,state,zip,count(distinct patientid) as cnt from nationaltable group by referringdoctor, city,state,zip--gave me for a specific doc 1000 cnt
referringdoctor, city,state,zip,referredto,count(distinct patientid) as cnt from nationaltable group by referringdoctor, city,state,zip,referredto--for the same doc i got 1100 as cnt
so even though that specific doctor referred 1000 unique patients (in year time) the patient has seen multiple doctors once referred.
the referredto field was added recently. i know the granularity changed thats what caused but the client like to see 1000 as cnt. i use tableau to show the data

--thanks in advance

Mr. Brian Gale SSC-Insane Points: 24933 More actions · Answer 5

syam.tunuguntla - Thursday, June 15, 2017 12:10 PM
here it is in detail
select
referringdoctor, city,state,zip,count(distinct patientid) as cnt from nationaltable group by referringdoctor, city,state,zip--gave me for a specific doc 1000 cnt
referringdoctor, city,state,zip,referredto,count(distinct patientid) as cnt from nationaltable group by referringdoctor, city,state,zip,referredto--for the same doc i got 1100 as cnt
so even though that specific doctor referred 1000 unique patients (in year time) the patient has seen multiple doctors once referred.
the referredto field was added recently. i know the granularity changed thats what caused but the client like to see 1000 as cnt. i use tableau to show the data
--thanks in advance

I think to solve that, you'd need to remove the "referredto" column in your grouping. Since this is not in an aggregate, you would need to partition the results. So something like this I think should work possibly (I did not test it as I have no sample data to try):
SELECT referringdoctor, city, state, zip, referredto, COUNT(DISTINCT patientid) OVER (PARTITION BY referringdoctor, city, state, zip) as cnt FROM nationaltable
I believe that should do what you want. Might need to toss a distinct onto the whole thing... I'm not sure. This is just going off the top of my head, but I think that should be good?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

jcelko212 32090 SSCrazy Eights Points: 9304 More actions · Answer 6

syam.tunuguntla - Thursday, June 15, 2017 12:10 PM

How do you expect anyone to be able to help you when you will not post DDL? We have to guess at the keys; we have to write the entire schema; we have to provide our own sample data; etc. if your boss expects you to be able to do this magic mind-reading, it is probably time to update your resume and go to some place where people are sane. Also, it really bothers me when people don't know the difference between a field and a column in SQL.

Please post DDL and follow ANSI/ISO standards when asking for help.

syam.tunuguntla SSC Veteran Points: 224 More actions · Answer 7

sorry for not providing you with clear details. let try get them with some sample data as well