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 2005 Forums
 Transact-SQL (2005)
 Concatenating

Author  Topic 

sross81
Posting Yak Master

228 Posts
Posted - 2008-10-29 : 10:59:35
Hello,

I have a query that should return one row. Although if there a multiples problems associated with the specific date and person it will return a row for each problem. I wanted to know if there was a way to write into my query that if there are more than one problem that it would just concatenate them into a list.

small version of data (not all rows)

person, date, problem
1,10/29/08, pain
1,10/29/08, sick

I want it to return

person, date, problem list
1,10/29/08, pain, sick

select distinct c.enc_id,a.person_id,LTRIM(a.person_nbr) as AcctNbr, a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth, max(b.rx_refills) as refills,c.create_timestamp,
e.first_name + ' ' + e.last_name as provname,
e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,f.phone,f.fax,
f.location_name,b.diagnosis_code_id as dxcode,
g.description as problem
from person a
join patient_medication b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id
join fdb_medication d on b.ndc_id = d.ndc_id
join provider_mstr e on b.provider_id = e.provider_id
left join diagnosis_code_mstr g on b.diagnosis_code_id = g.diagnosis_code_id
where d.gcn = '94200' and b.date_stopped = ' '
group by c.enc_id,a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth,
c.create_timestamp,
e.first_name,e.last_name,e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,
f.phone,f.fax,f.location_name,b.diagnosis_code_id,g.description
order by c.enc_id, fullname

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-29 : 11:05:52
just use this

select distinct c.enc_id,a.person_id,LTRIM(a.person_nbr) as AcctNbr, a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth, max(b.rx_refills) as refills,c.create_timestamp,
e.first_name + ' ' + e.last_name as provname, 
e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,f.phone,f.fax,
f.location_name,b.diagnosis_code_id as dxcode,
coalesce(left(dl.desclist,len(dl.desclist)-1),'') as problem
from person a
join patient_medication b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id
join fdb_medication d on b.ndc_id = d.ndc_id
join provider_mstr e on b.provider_id = e.provider_id
outer apply (select description + ','
             from diagnosis_code_mstr  
             where diagnosis_code_id=b.diagnosis_code_id
             for xml path(''))dl(desclist)
where d.gcn = '94200' and b.date_stopped = ' ' 
group by c.enc_id,a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth,
c.create_timestamp,
e.first_name,e.last_name,e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,
f.phone,f.fax,f.location_name,b.diagnosis_code_id,coalesce(left(dl.desclist,len(dl.desclist)-1),'')
order by c.enc_id, fullname
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-10-29 : 11:17:31
Thanks for your response.

I tried the code and I still get two rows one for each problem instead of them combined into a new field.

select distinct c.enc_id,a.person_id,LTRIM(a.person_nbr) as AcctNbr,
a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth, max(b.rx_refills) as refills,c.create_timestamp,
e.first_name + ' ' + e.last_name as provname,
e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,f.phone,f.fax,
f.location_name,
coalesce(left(dl.desclist,len(dl.desclist)-1),'') as problem
from person a
join patient_medication b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id
join fdb_medication d on b.ndc_id = d.ndc_id
join provider_mstr e on b.provider_id = e.provider_id
join location_mstr f on b.location_id = f.location_id
outer apply (select description + ','
from diagnosis_code_mstr
where diagnosis_code_id=b.diagnosis_code_id
for xml path(''))dl(desclist)
where d.gcn = '94200' and b.date_stopped = ' ' and a.person_nbr = 77622
group by c.enc_id,a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth,
c.create_timestamp,
e.first_name,e.last_name,e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,
f.phone,f.fax,f.location_name,b.diagnosis_code_id,dl.desclist
order by c.enc_id, fullname


quote:
Originally posted by visakh16

just use this

select distinct c.enc_id,a.person_id,LTRIM(a.person_nbr) as AcctNbr, a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth, max(b.rx_refills) as refills,c.create_timestamp,
e.first_name + ' ' + e.last_name as provname, 
e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,f.phone,f.fax,
f.location_name,b.diagnosis_code_id as dxcode,
coalesce(left(dl.desclist,len(dl.desclist)-1),'') as problem
from person a
join patient_medication b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id
join fdb_medication d on b.ndc_id = d.ndc_id
join provider_mstr e on b.provider_id = e.provider_id
outer apply (select description + ','
             from diagnosis_code_mstr  
             where diagnosis_code_id=b.diagnosis_code_id
             for xml path(''))dl(desclist)
where d.gcn = '94200' and b.date_stopped = ' ' 
group by c.enc_id,a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth,
c.create_timestamp,
e.first_name,e.last_name,e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,
f.phone,f.fax,f.location_name,b.diagnosis_code_id,coalesce(left(dl.desclist,len(dl.desclist)-1),'')
order by c.enc_id, fullname



Thanks in Advance!
Sherri
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-29 : 11:21:46
quote:
Originally posted by sross81

Thanks for your response.

I tried the code and I still get two rows one for each problem instead of them combined into a new field.

select distinct c.enc_id,a.person_id,LTRIM(a.person_nbr) as AcctNbr, a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth, max(b.rx_refills) as refills,c.create_timestamp,
e.first_name + ' ' + e.last_name as provname,
e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,f.phone,f.fax,
f.location_name,b.diagnosis_code_id as dxcode,
coalesce(left(dl.desclist,len(dl.desclist)-1),'') as problem
from person a
join patient_medication b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id
join fdb_medication d on b.ndc_id = d.ndc_id
join provider_mstr e on b.provider_id = e.provider_id
join location_mstr f on b.location_id = f.location_id
left join diagnosis_code_mstr g on b.diagnosis_code_id = g.diagnosis_code_id
outer apply (select description + ','
from diagnosis_code_mstr
where diagnosis_code_id=b.diagnosis_code_id
for xml path(''))dl(desclist)
where d.gcn = '94200' and b.date_stopped = ' ' and a.person_nbr = 77622
group by c.enc_id,a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth,
c.create_timestamp,
e.first_name,e.last_name,e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,
f.phone,f.fax,f.location_name,b.diagnosis_code_id,g.description,dl.desclist
order by c.enc_id, fullname



quote:
Originally posted by visakh16

just use this

select distinct c.enc_id,a.person_id,LTRIM(a.person_nbr) as AcctNbr, a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth, max(b.rx_refills) as refills,c.create_timestamp,
e.first_name + ' ' + e.last_name as provname, 
e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,f.phone,f.fax,
f.location_name,b.diagnosis_code_id as dxcode,
coalesce(left(dl.desclist,len(dl.desclist)-1),'') as problem
from person a
join patient_medication b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id
join fdb_medication d on b.ndc_id = d.ndc_id
join provider_mstr e on b.provider_id = e.provider_id
outer apply (select description + ','
             from diagnosis_code_mstr  
             where diagnosis_code_id=b.diagnosis_code_id
             for xml path(''))dl(desclist)
where d.gcn = '94200' and b.date_stopped = ' ' 
group by c.enc_id,a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth,
c.create_timestamp,
e.first_name,e.last_name,e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,
f.phone,f.fax,f.location_name,b.diagnosis_code_id,coalesce(left(dl.desclist,len(dl.desclist)-1),'')
order by c.enc_id, fullname



Thanks in Advance!
Sherri

please use code as posted. my posted code does not contain left join
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-10-29 : 11:24:14
I copied and pasted your exact code removing the lines you crossed out in red but I still am getting two rows when there are two problems.


quote:
Originally posted by visakh16

quote:
Originally posted by sross81

Thanks for your response.

I tried the code and I still get two rows one for each problem instead of them combined into a new field.

select distinct c.enc_id,a.person_id,LTRIM(a.person_nbr) as AcctNbr, a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth, max(b.rx_refills) as refills,c.create_timestamp,
e.first_name + ' ' + e.last_name as provname,
e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,f.phone,f.fax,
f.location_name,b.diagnosis_code_id as dxcode,
coalesce(left(dl.desclist,len(dl.desclist)-1),'') as problem
from person a
join patient_medication b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id
join fdb_medication d on b.ndc_id = d.ndc_id
join provider_mstr e on b.provider_id = e.provider_id
join location_mstr f on b.location_id = f.location_id
left join diagnosis_code_mstr g on b.diagnosis_code_id = g.diagnosis_code_id
outer apply (select description + ','
from diagnosis_code_mstr
where diagnosis_code_id=b.diagnosis_code_id
for xml path(''))dl(desclist)
where d.gcn = '94200' and b.date_stopped = ' ' and a.person_nbr = 77622
group by c.enc_id,a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth,
c.create_timestamp,
e.first_name,e.last_name,e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,
f.phone,f.fax,f.location_name,b.diagnosis_code_id,g.description,dl.desclist
order by c.enc_id, fullname



quote:
Originally posted by visakh16

just use this

select distinct c.enc_id,a.person_id,LTRIM(a.person_nbr) as AcctNbr, a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth, max(b.rx_refills) as refills,c.create_timestamp,
e.first_name + ' ' + e.last_name as provname, 
e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,f.phone,f.fax,
f.location_name,b.diagnosis_code_id as dxcode,
coalesce(left(dl.desclist,len(dl.desclist)-1),'') as problem
from person a
join patient_medication b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id
join fdb_medication d on b.ndc_id = d.ndc_id
join provider_mstr e on b.provider_id = e.provider_id
outer apply (select description + ','
             from diagnosis_code_mstr  
             where diagnosis_code_id=b.diagnosis_code_id
             for xml path(''))dl(desclist)
where d.gcn = '94200' and b.date_stopped = ' ' 
group by c.enc_id,a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth,
c.create_timestamp,
e.first_name,e.last_name,e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,
f.phone,f.fax,f.location_name,b.diagnosis_code_id,coalesce(left(dl.desclist,len(dl.desclist)-1),'')
order by c.enc_id, fullname



Thanks in Advance!
Sherri

please use code as posted. my posted code does not contain left join


Thanks in Advance!
Sherri
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-29 : 11:32:43
show your sample data. i'm sure you've some unwanted columns included in group by
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-10-29 : 11:34:00
Here is my sample code. I looked through the 2 rows returned and all data is the same in each field except for the problem field.


select distinct c.enc_id,a.person_id,LTRIM(a.person_nbr) as AcctNbr, a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth, max(b.rx_refills) as refills,c.create_timestamp,
e.first_name + ' ' + e.last_name as provname,
e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,f.phone,f.fax,
f.location_name,
coalesce(left(dl.desclist,len(dl.desclist)-1),'') as problem
from person a
join patient_medication b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id
join fdb_medication d on b.ndc_id = d.ndc_id
join provider_mstr e on b.provider_id = e.provider_id
join location_mstr f on b.location_id = f.location_id
outer apply (select description + ','
from diagnosis_code_mstr
where diagnosis_code_id=b.diagnosis_code_id
for xml path(''))dl(desclist)
where d.gcn = '94200' and b.date_stopped = ' ' and a.person_nbr = 77622
group by dl.desclist,c.enc_id,a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth,
c.create_timestamp,
e.first_name,e.last_name,e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,
f.phone,f.fax,f.location_name
order by c.enc_id, fullname



quote:
Originally posted by visakh16

show your sample data. i'm sure you've some unwanted columns included in group by


Thanks in Advance!
Sherri
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-29 : 11:39:09
can you post sample of your output?
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-10-29 : 11:44:40
Yes I guess that is fine since its just a fake patient I created to test.

E7F7E3C4-8536-4996-938C-F777A83AB910,6BED010B-372C-4A79-A607-9F0978E16775,77622,Lady PatientSmith,19580402,0,2008-10-29 07:47:09.633,Yuriy Bilan PA,P49260,1102 South I St Tacoma WA 98405,2535973813,2535973815,CHC Downtown Clinic,DERMATOPHYTOSIS OF FOOT


E7F7E3C4-8536-4996-938C-F777A83AB910,6BED010B-372C-4A79-A607-9F0978E16775,77622,Lady PatientSmith,19580402,0,2008-10-29 07:47:09.633,Yuriy Bilan PA,P49260,1102 South I St Tacoma WA 98405, 235973813,2535973815,CHC Downtown Clinic, DIABETIC CATARACT



quote:
Originally posted by visakh16

can you post sample of your output?


Thanks in Advance!
Sherri
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-29 : 12:00:04
how many patient_medication records exist for a given person_id?
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-10-29 : 12:15:17
There is no limit

quote:
Originally posted by visakh16

how many patient_medication records exist for a given person_id?


Thanks in Advance!
Sherri
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-29 : 12:19:31
that will duplicate the results in cases where you've more than one person_medication records existing for same person_id as we're using it to get problem value.
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-10-29 : 12:29:03
Ok I tried taking it out and I still get the extra row. I think its because account number. But I at least need to return account number. Is there a way I can make this work?

quote:
Originally posted by visakh16

that will duplicate the results in cases where you've more than one person_medication records existing for same person_id as we're using it to get problem value.


Thanks in Advance!
Sherri
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-10-29 : 12:34:16
Or if I cannot create a list is there a way to select just one of them so that I don't get the two rows. I need to just have one row so that only one page prints on my report.

quote:
Originally posted by sross81

Ok I tried taking it out and I still get the extra row. I think its because account number. But I at least need to return account number. Is there a way I can make this work?

quote:
Originally posted by visakh16

that will duplicate the results in cases where you've more than one person_medication records existing for same person_id as we're using it to get problem value.


Thanks in Advance!
Sherri


Thanks in Advance!
Sherri
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-29 : 12:37:19
quote:
Originally posted by sross81

Or if I cannot create a list is there a way to select just one of them so that I don't get the two rows. I need to just have one row so that only one page prints on my report.

quote:
Originally posted by sross81

Ok I tried taking it out and I still get the extra row. I think its because account number. But I at least need to return account number. Is there a way I can make this work?

quote:
Originally posted by visakh16

that will duplicate the results in cases where you've more than one person_medication records existing for same person_id as we're using it to get problem value.


Thanks in Advance!
Sherri


Thanks in Advance!
Sherri

for getting just one use below

select distinct c.enc_id,a.person_id,LTRIM(a.person_nbr) as AcctNbr, 
a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth, max(b.rx_refills) as refills,c.create_timestamp,
e.first_name + ' ' + e.last_name as provname, 
e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,f.phone,f.fax,
f.location_name,
max(left(dl.desclist,len(dl.desclist)-1)) as problem
from person a
join patient_medication b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id
join fdb_medication d on b.ndc_id = d.ndc_id
join provider_mstr e on b.provider_id = e.provider_id
join location_mstr f on b.location_id = f.location_id
outer apply (select description + ','
from diagnosis_code_mstr 
where diagnosis_code_id=b.diagnosis_code_id
for xml path(''))dl(desclist)
where d.gcn = '94200' and b.date_stopped = ' ' and a.person_nbr = 77622
group by c.enc_id,a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth,
c.create_timestamp,
e.first_name,e.last_name,e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,
f.phone,f.fax,f.location_name,b.diagnosis_code_id
order by c.enc_id, fullname
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-10-29 : 12:41:08
That still returns 2. Its really weird to me how every row is the same but the problem but there is no way I can choose like the max one. I tried max(g.description)without using the new code you provided and I still get duplicate rows. Its not working the way any normal query I have ever done has worked before.

quote:
Originally posted by visakh16

quote:
Originally posted by sross81

Or if I cannot create a list is there a way to select just one of them so that I don't get the two rows. I need to just have one row so that only one page prints on my report.

quote:
Originally posted by sross81

Ok I tried taking it out and I still get the extra row. I think its because account number. But I at least need to return account number. Is there a way I can make this work?

quote:
Originally posted by visakh16

that will duplicate the results in cases where you've more than one person_medication records existing for same person_id as we're using it to get problem value.


Thanks in Advance!
Sherri


Thanks in Advance!
Sherri

for getting just one use below

select distinct c.enc_id,a.person_id,LTRIM(a.person_nbr) as AcctNbr, 
a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth, max(b.rx_refills) as refills,c.create_timestamp,
e.first_name + ' ' + e.last_name as provname, 
e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,f.phone,f.fax,
f.location_name,
max(left(dl.desclist,len(dl.desclist)-1)) as problem
from person a
join patient_medication b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id
join fdb_medication d on b.ndc_id = d.ndc_id
join provider_mstr e on b.provider_id = e.provider_id
join location_mstr f on b.location_id = f.location_id
outer apply (select description + ','
from diagnosis_code_mstr 
where diagnosis_code_id=b.diagnosis_code_id
for xml path(''))dl(desclist)
where d.gcn = '94200' and b.date_stopped = ' ' and a.person_nbr = 77622
group by c.enc_id,a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth,
c.create_timestamp,
e.first_name,e.last_name,e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,
f.phone,f.fax,f.location_name,b.diagnosis_code_id
order by c.enc_id, fullname



Thanks in Advance!
Sherri
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-29 : 12:44:57
that means there are still many to one relations existing.let me try one more time. can you provide necessary info as provided in link below

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-10-29 : 16:46:00
I think the problem is that for each entry into the patient medication table it creates a separate row even if its the same person_id and enc_id. The patient_medication table has a primary key called uniq_id.

So for example
uniq_id,person, enc, problem
A1,1,1,sick
A2,1,1,pain

I am not sure how to combine them into one row like we have been discussing but I think the reason it is not working is as you said because there is still a many to one relationship and its because there can be many entries in the patient_medication table for a person/encounter combination.

I was not returning the uniq_id from patient_medication but should I so that I can group it in some way?

select distinct c.enc_id,a.person_id,LTRIM(a.person_nbr) as AcctNbr, a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth, max(b.rx_refills) as refills,c.create_timestamp,
e.first_name + ' ' + e.last_name as provname,
e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,f.phone,f.fax,
f.location_name,
coalesce(left(dl.desclist,len(dl.desclist)-1),'') as problem
from person a
join patient_medication b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id
join fdb_medication d on b.ndc_id = d.ndc_id
join provider_mstr e on b.provider_id = e.provider_id
join location_mstr f on b.location_id = f.location_id
outer apply (select description + ','
from diagnosis_code_mstr
where diagnosis_code_id=b.diagnosis_code_id
for xml path(''))dl(desclist)
where d.gcn = '94200' and b.date_stopped = ' ' and a.person_nbr = 77622
group by dl.desclist,c.enc_id,a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth,
c.create_timestamp,
e.first_name,e.last_name,e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,
f.phone,f.fax,f.location_name
order by c.enc_id, fullname




quote:
Originally posted by visakh16

that means there are still many to one relations existing.let me try one more time. can you provide necessary info as provided in link below

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Thanks in Advance!
Sherri
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-30 : 04:04:14
why have you included c.create_timestamp in group by? will it have same value for both the records you get as duplicates?
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-10-30 : 10:34:45
Yes c.create_timestamp would have the same value. C is the alias for patient_encounter and it is created once but the B table
patient_medication can have multiple entries for a patient_encounter. So I could see if I used b.createtimestamp that it would duplicate rows because when I run the query with that I see two different time stamps but if I run it with c.create_timestamp its an idential timestamp. Its something to do with the B table patient_medication I took it row by rown and join by join and got only one row until I add the problem column that was created to do the concatenation. In the patien_medication table where the problem column exists it is linked to a person_id and enc_id but it will have a separate row for each problem. As follows.

med_id, person_id, enc_id, problem
M1, 1, 1, sick
M2, 1, 1, pain

I just am confused because I am not including any other field from patient_medication beside the problem field and I the the concantenation code I thought would roll it up into one column but the table design is preventing it. I am still lost with it. I hate to only use the one problem since it may not apply to all medications that will print on the sub report but I am starting to think its impossible.



quote:
Originally posted by visakh16

why have you included c.create_timestamp in group by? will it have same value for both the records you get as duplicates?


Thanks in Advance!
Sherri
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-30 : 13:30:16
only thing i can suggest until you post some sample data from tables is to try removing fields in group by one and one and see if you dont get duplicates each time.
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site