Author |
Topic |
babloo
Starting Member
35 Posts |
Posted - 2013-05-22 : 10:51:14
|
Hi I have this select statement:SELECT distinct FirstTable.last_name, FirstTable.first_name, SecondTable.ins_payor_search, ThirdTable.visit_dc_status, FourthTable.drg, FourthTable.primary_dx, FifthTable.diagnosis, FifthTable.diag_prsnt_admit, FifthTable.diag_sequence_num, FourthTable.principal_proc, SixthTable.proc_procedureThe current output is repeatation of 'Diagnosis' and 'Procedure' field, if I remove the Procedure Field I am not gettin repeats. I was wondering instead of Diagnosis appearing like this:Diagnois, Sequence Number, ProcedureDG1, 1, Proc1DG1, 1, Proc2DG2, 2, Proc1DG2, 2, Proc2DG3, 3, Proc1DG3, 3, Proc2If I could have display like, in oneline.:Diagnosis1, Diagnosis2, Diagnosis3, Procedure1DG1, DG2, DG3, Procedure1, Procedure2The diagnosis codes may go upto 100.Or if there is a way I don't get repeats for Procedure codes. Simply like this:Diagnois, Sequence Number, ProcedureDG1, 1, Proc1DG2, 2, Proc2DG3, 3, Thanks alot. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-22 : 12:22:57
|
quote: Originally posted by babloo Hi I have this select statement:SELECT distinct FirstTable.last_name, FirstTable.first_name, SecondTable.ins_payor_search, ThirdTable.visit_dc_status, FourthTable.drg, FourthTable.primary_dx, FifthTable.diagnosis, FifthTable.diag_prsnt_admit, FifthTable.diag_sequence_num, FourthTable.principal_proc, SixthTable.proc_procedureThe current output is repeatation of 'Diagnosis' and 'Procedure' field, if I remove the Procedure Field I am not gettin repeats. I was wondering instead of Diagnosis appearing like this:Diagnois, Sequence Number, ProcedureDG1, 1, Proc1DG1, 1, Proc2DG2, 2, Proc1DG2, 2, Proc2DG3, 3, Proc1DG3, 3, Proc2If I could have display like, in oneline.:Diagnosis1, Diagnosis2, Diagnosis3, Procedure1DG1, DG2, DG3, Procedure1, Procedure2The diagnosis codes may go upto 100.Or if there is a way I don't get repeats for Procedure codes. Simply like this:Diagnois, Sequence Number, ProcedureDG1, 1, Proc1DG2, 2, Proc2DG3, 3, Thanks alot.
Either or both of the possibilities that you suggested can be done - but it is easier to fix the query rather the output. To suggest how you might do it, we need to see the join conditions that you are using on the tables. Can you post your full query rather than just the select part? |
|
|
babloo
Starting Member
35 Posts |
Posted - 2013-05-22 : 12:31:38
|
Thanks James for your responce, here is my complete Query:SELECT distinct FirstTable.mrn, FirstTable.bill_no, FirstTable.last_name, FirstTable.first_name, FirstTable.admission_date, FirstTable.discharged, FirstTable.dob, FirstTable.v_facility_code, FirstTable.patient_type, FirstTable.sex, FirstTable.v_financial_cls, SixthTable.ins_payor_search, FifthTable.visit_dc_status,FourthTable.enc_drg, FourthTable.enc_primary_dx, SecondTable.diagnosis, SecondTable.diag_prsnt_admit, SecondTable.diag_sequence_num, FourthTable.enc_principal_proc, ThirdTable.proc_procedureFROM FirstTable LEFT JOIN SecondTable ON FirstTable.visitid=SecondTable.visitid LEFT JOIN ThirdTable ON FirstTable.visitid=ThirdTable.visitid LEFT JOIN FourthTable ON FirstTable.visitid=FourthTable.visitid LEFT JOIN FifthTable ON FirstTable.visitid=FifthTable.visitid LEFT JOIN SixthTable ON FirstTable.visitid=SixthTable.visitidWHERE (FirstTable.discharged BETWEEN '2013-05-21 00:00:00.000' AND '2013-05-22 23:59:59.997') |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-22 : 13:21:40
|
Is there something in your SecndTable and ThirdTable that can be used to relate Diagnosis DG1 to Procedure Proc1, Diagnosis DG2 to Procedure Proc2 etc.? If you do, you need to join on those also when joining the ThirdTable. |
|
|
babloo
Starting Member
35 Posts |
Posted - 2013-05-22 : 13:26:49
|
Second table only hold Diagnosis information, Third Table hold only Procedure data and they are both linked with the first table. Yes, we can link Second and third table using Visit Id but Secondtable will always have a visit id not necesarily third table will have any entry. Every patient will have atleast one diagnosis code but may not have a procedure. |
|
|
babloo
Starting Member
35 Posts |
Posted - 2013-05-22 : 13:29:53
|
Is there a way using my Query above we can display the whole data in oneline using , or any | as delimitor and wont show any repeats. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-22 : 13:47:49
|
quote: Originally posted by babloo Is there a way using my Query above we can display the whole data in oneline using , or any | as delimitor and wont show any repeats.
That can be done, the problem is how to define the repeats. In your example, you picked DG1,Proc1 and DG2, Proc2. Why not DG1, Proc2 and DG2, Proc1 instead? Or any other combination?So there must be SOMETHING that you used to say that DG1 goes with Proc1 and not with Proc2 or anything else. That something is what is required to eliminate the repeats. |
|
|
babloo
Starting Member
35 Posts |
Posted - 2013-05-22 : 14:37:30
|
Actually, there is no combination of DG to PROC, like I said a patient can have 1 Diagnosis code but may or maynot have a procedure so my data can look like this:First PatientDG1, DG2, DG3, PROC1 <-- Many Diagnoses but one Proc.Second PatientDG1, <-- One Diagnosis No ProcOrThird PatientDG1, PROC1, PROC2, PROC3, PROCN... <-- One Diagnosis but many Procedures.The reason why I mentioned DG1, PROC1, DG1, PROC2 above is because that's how the data is currently being output to me and which is causing Repeats, so I was wondering if I could have all diagnoses in one line with Column deliminated and Procedures with column deminated if any.Thanks. |
|
|
|
|
|