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 2008 Forums
 Transact-SQL (2008)
 Crosstab or Pivot

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_procedure


The 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, Procedure
DG1, 1, Proc1
DG1, 1, Proc2
DG2, 2, Proc1
DG2, 2, Proc2
DG3, 3, Proc1
DG3, 3, Proc2


If I could have display like, in oneline.:

Diagnosis1, Diagnosis2, Diagnosis3, Procedure1
DG1, DG2, DG3, Procedure1, Procedure2

The 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, Procedure
DG1, 1, Proc1
DG2, 2, Proc2
DG3, 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_procedure


The 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, Procedure
DG1, 1, Proc1
DG1, 1, Proc2
DG2, 2, Proc1
DG2, 2, Proc2
DG3, 3, Proc1
DG3, 3, Proc2


If I could have display like, in oneline.:

Diagnosis1, Diagnosis2, Diagnosis3, Procedure1
DG1, DG2, DG3, Procedure1, Procedure2

The 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, Procedure
DG1, 1, Proc1
DG2, 2, Proc2
DG3, 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?
Go to Top of Page

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_procedure
FROM 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.visitid

WHERE (FirstTable.discharged BETWEEN '2013-05-21 00:00:00.000' AND '2013-05-22 23:59:59.997')
Go to Top of Page

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.
Go to Top of Page

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.

Go to Top of Page

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.

Go to Top of Page

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.
Go to Top of Page

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 Patient
DG1, DG2, DG3, PROC1 <-- Many Diagnoses but one Proc.

Second Patient
DG1, <-- One Diagnosis No Proc

Or

Third Patient
DG1, 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.
Go to Top of Page
   

- Advertisement -