November 23, 2012 at 5:40 am
I have a table PatientLocDtl in which I need to update AccountIDon basis of ChartNo and PatientID.
create table PatientLocDtl
(Account int ,
ChartNo varchar(10),
PatientID int)
Insert into PatientLocDtl (PatientId,chartNo)
select 8, '1-1'
union all
select 9,'1-2'
union all
select 10,'1-3'
union all
select 11,'6-1'
union all
select 12,'6-2'
Desired Result-- For all 3 rows whose chartno starting with 1 should insert AccountID be 8 as '1-1' is parent of all ChartNo and associated with PatientId 8..similarly for chartno starting with 6 accountid would be 11
i wrote the query as-
select
(select top 1 a.PatientID
from PatientLocDtl a
where Right(a.Chart_No,1) like '%[0-9]' and a.PatientID = b.PatientID
)as AccountID
,b.PatientID,b.chart_no
from PatientLocDtl b order by b.PatientID asc
but its not working correctly.
please suggest
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 23, 2012 at 6:06 am
hey, I am done with this....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 23, 2012 at 6:13 am
This was removed by the editor as SPAM
November 23, 2012 at 6:24 am
I create this one and it works- :-):-)
update PC
set pc.account =p.patientID
from PatientLocDtl pc
join patientlocdtl p on left(p.ChartNo,len(SUBSTRING(p.ChartNo,PATINDEX('%-%',''),len(p.ChartNo))))=left(pc.ChartNo,len(SUBSTRING(pc.ChartNo,PATINDEX('%-%',''),len(pc.ChartNo))))
where right(p.chartno,1) like '[0-9]'
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 23, 2012 at 6:26 am
how to post query in this format?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply