![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
![]() |
Mon, Feb 15 2010 7:44 AM | Permanent Link |
Uli Becker | Hi,
I was not successful in writing a statement for this task: StatusTable (the status of a patient is stored) PatientID: integer; // any integer-value Status: integer; // 0 or 1 Private: boolean; Example: 32124,0,false; 32124,1,true; 32125,0,false; 32126,0,false; A Patient can have 1 or 2 entries here with status = 0 or status = 1 or both. What I want to know by running a query against this table is: Show me all records where the same patient has both status 0 and 1 and where ((status = 0 and private = false) and (status = 1 and private = true)) The reason is, that I have to correct the table if there is a record with the same PatientID and (status = 0, private = false) AND (status = 1, private = true); IOW: If (status = 1 and private = true) then status with value 0 has to be private too. I hope I could make that clear. Can that be done in one query? Thanks and regards Uli |
Mon, Feb 15 2010 8:18 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Uli
Not a finished product but select patientid, private, sum(status), sum(IF(private,1,0)+status)as both, sum(IF(private,1,0)) as single from uli where patientid in (select PatientID from Uli group by PatientID having count(PatientID) > 1) group by patientid In my quick tests if the patient is clean then both returns 3, single returns 1, if incorrect then both returns 2, single returns 1 Roy Lambert [Team Elevate] |
Mon, Feb 15 2010 9:39 AM | Permanent Link |
Uli Becker | Roy,
> select > patientid, > private, > sum(status), > sum(IF(private,1,0)+status)as both, > sum(IF(private,1,0)) as single > from uli > where patientid in (select PatientID from Uli > group by PatientID > having count(PatientID) > 1) > group by patientid > > In my quick tests if the patient is clean then both returns 3, single returns 1, if incorrect then both returns 2, single returns 1 Not bad. Good idea to accumulate status and private. Just having a deep look into it, in order to understand what's happening there. ![]() Uli |
Mon, Feb 15 2010 10:36 AM | Permanent Link |
Uli Becker | Roy,
that works. Thank you. Here the final statement: select patientenid, sum(IF(privat,1,0)+status)as both, sum(IF(privat,1,0)) as single from scheine where patientenid in (select patientenid from Scheine group by patientenid having count(patientenid) = 2) group by patientenid having both = 3 and single = 1 Regards Uli |
Mon, Feb 15 2010 10:48 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Uli
I thought you wanted the invalid ones, doesn't that give you the valid ones or am I getting confused. Roy Lambert |
Mon, Feb 15 2010 11:10 AM | Permanent Link |
Uli Becker | Roy,
> I thought you wanted the invalid ones, doesn't that give you the valid ones or am I getting confused. You are not. ![]() Just a typo, I realized it when executing the query. Uli |
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |