|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
MS SQL 2000 Query HelpI have patient data - am interested in patients receiving a certain
diagnostic procedure. Need to find out on an average in the group of patients I am analyzing, on which visit they receive this procedure. Suppose there is a consumer X, who visits doc A - on the 5th visit the doc administers this diagnostic test and another doc could administer this test to a different consumer on the second visit - need to know overall the visit in which the patients get this procedure. Can anybody suggest a way to accomplish this.. Thanks
Show quote
"SJ" <S*@discussions.microsoft.com> wrote in message How are we supposed to guess what your data, tables, keys, constraints, etc news:9F11FE30-FF4D-4B63-BF03-8FD6072442E5@microsoft.com... >I have patient data - am interested in patients receiving a certain > diagnostic procedure. Need to find out on an average in the group of > patients > I am analyzing, on which visit they receive this procedure. > Suppose there is a consumer X, who visits doc A - on the 5th visit the doc > administers this diagnostic test and another doc could administer this > test > to a different consumer on the second visit - need to know overall the > visit > in which the patients get this procedure. > Can anybody suggest a way to accomplish this.. > Thanks look like? Try posting again after you've read the advice in the following article. I'm sure someone can help you out if you include more information. http://www.aspfaq.com/etiquette.asp?id=5006 -- David Portas SQL Server MVP -- CREATE TABLE #temp
(patient_id INT NULL, doc_id CHAR(9) NULL, serv_date DATETIME NULL, proc_code CHAR(6) NULL) The patient id is unique to each patient. acs_id prov_id serv_date proc_code 2001093 000175900 2005-10-03 00:00:00.000 90853 1929596 100175901 2005-10-03 00:00:00.000 90806 1443313 000175902 2005-09-29 00:00:00.000 90806 2001093 000175900 2005-10-05 00:00:00.000 90801 1929596 100175901 2005-10-07 00:00:00.000 90806 1929596 100175901 2005-10-10 00:00:00.000 90801 1443313 000175902 2005-10-29 00:00:00.000 90805 1443313 000175902 2005-10-29 00:00:00.000 90801 I am trying to get on an average the visit (first/second/third) during which they get the 90801 proc_code? Hope this is clear. Thanks Show quote "David Portas" wrote: > "SJ" <S*@discussions.microsoft.com> wrote in message > news:9F11FE30-FF4D-4B63-BF03-8FD6072442E5@microsoft.com... > >I have patient data - am interested in patients receiving a certain > > diagnostic procedure. Need to find out on an average in the group of > > patients > > I am analyzing, on which visit they receive this procedure. > > Suppose there is a consumer X, who visits doc A - on the 5th visit the doc > > administers this diagnostic test and another doc could administer this > > test > > to a different consumer on the second visit - need to know overall the > > visit > > in which the patients get this procedure. > > Can anybody suggest a way to accomplish this.. > > Thanks > > How are we supposed to guess what your data, tables, keys, constraints, etc > look like? Try posting again after you've read the advice in the following > article. I'm sure someone can help you out if you include more information. > http://www.aspfaq.com/etiquette.asp?id=5006 > > -- > David Portas > SQL Server MVP > -- > > >
Show quote
On Thu, 1 Dec 2005 13:44:02 -0800, "SJ" <S*@discussions.microsoft.com> It's clear.wrote: >CREATE TABLE #temp >(patient_id INT NULL, > doc_id CHAR(9) NULL, > serv_date DATETIME NULL, > proc_code CHAR(6) NULL) > >The patient id is unique to each patient. > >acs_id prov_id serv_date > proc_code >2001093 000175900 2005-10-03 00:00:00.000 90853 >1929596 100175901 2005-10-03 00:00:00.000 90806 >1443313 000175902 2005-09-29 00:00:00.000 90806 >2001093 000175900 2005-10-05 00:00:00.000 90801 >1929596 100175901 2005-10-07 00:00:00.000 90806 >1929596 100175901 2005-10-10 00:00:00.000 90801 >1443313 000175902 2005-10-29 00:00:00.000 90805 >1443313 000175902 2005-10-29 00:00:00.000 90801 > >I am trying to get on an average the visit (first/second/third) during which >they get the 90801 proc_code? >Hope this is clear. >Thanks Very common problem, but "n'th" is a tough topic in relational. The approximate solution is to insert these records, sorted, into a new (temp) table that also has an indentity key. Take the max(key) - min(key) for each patient, that gives you the n'th! Hope that's enough of a hint ... Josh CREATE TABLE #temp
(patient_id INT NULL, doc_id CHAR(9) NULL, serv_date DATETIME NULL, proc_code CHAR(6) NULL) go insert #temp(patient_id, doc_id, serv_date, proc_code ) values(2001093,'000175900','2005-10-03 00:00:00.000','90853') insert #temp(patient_id, doc_id, serv_date, proc_code ) values(1929596,'100175901','2005-10-03 00:00:00.000','90806') insert #temp(patient_id, doc_id, serv_date, proc_code ) values(1443313,'000175902','2005-09-29 00:00:00.000','90806') insert #temp(patient_id, doc_id, serv_date, proc_code ) values(2001093,'000175900','2005-10-05 00:00:00.000','90801') insert #temp(patient_id, doc_id, serv_date, proc_code ) values(1929596,'100175901','2005-10-07 00:00:00.000','90806') insert #temp(patient_id, doc_id, serv_date, proc_code ) values(1929596,'100175901','2005-10-10 00:00:00.000','90801') insert #temp(patient_id, doc_id, serv_date, proc_code ) values(1443313,'000175902','2005-10-29 00:00:00.000','90805') insert #temp(patient_id, doc_id, serv_date, proc_code ) values(1443313,'000175902','2005-10-29 00:00:00.000','90801') select avg(cnt) from ( select t1.patient_id, count(*) as cnt from #temp t1 where t1.serv_date<=( select min(t2.serv_date) from #temp t2 where t2.patient_id=t1.patient_id and t2.proc_code='90801') group by t1.patient_id) t dean Show quote "SJ" <S*@discussions.microsoft.com> wrote in message news:B85C4F8E-48A7-4C70-A018-3E22BC3D0732@microsoft.com... > CREATE TABLE #temp > (patient_id INT NULL, > doc_id CHAR(9) NULL, > serv_date DATETIME NULL, > proc_code CHAR(6) NULL) > > The patient id is unique to each patient. > > acs_id prov_id serv_date > proc_code > 2001093 000175900 2005-10-03 00:00:00.000 90853 > 1929596 100175901 2005-10-03 00:00:00.000 90806 > 1443313 000175902 2005-09-29 00:00:00.000 90806 > 2001093 000175900 2005-10-05 00:00:00.000 90801 > 1929596 100175901 2005-10-07 00:00:00.000 90806 > 1929596 100175901 2005-10-10 00:00:00.000 90801 > 1443313 000175902 2005-10-29 00:00:00.000 90805 > 1443313 000175902 2005-10-29 00:00:00.000 90801 > > I am trying to get on an average the visit (first/second/third) during > which > they get the 90801 proc_code? > Hope this is clear. > Thanks > > > > "David Portas" wrote: > >> "SJ" <S*@discussions.microsoft.com> wrote in message >> news:9F11FE30-FF4D-4B63-BF03-8FD6072442E5@microsoft.com... >> >I have patient data - am interested in patients receiving a certain >> > diagnostic procedure. Need to find out on an average in the group of >> > patients >> > I am analyzing, on which visit they receive this procedure. >> > Suppose there is a consumer X, who visits doc A - on the 5th visit the >> > doc >> > administers this diagnostic test and another doc could administer this >> > test >> > to a different consumer on the second visit - need to know overall the >> > visit >> > in which the patients get this procedure. >> > Can anybody suggest a way to accomplish this.. >> > Thanks >> >> How are we supposed to guess what your data, tables, keys, constraints, >> etc >> look like? Try posting again after you've read the advice in the >> following >> article. I'm sure someone can help you out if you include more >> information. >> http://www.aspfaq.com/etiquette.asp?id=5006 >> >> -- >> David Portas >> SQL Server MVP >> -- >> >> >> Thank You - this is cool.
Show quote "Dean" wrote: > CREATE TABLE #temp > (patient_id INT NULL, > doc_id CHAR(9) NULL, > serv_date DATETIME NULL, > proc_code CHAR(6) NULL) > go > > > insert #temp(patient_id, doc_id, serv_date, proc_code ) > values(2001093,'000175900','2005-10-03 00:00:00.000','90853') > insert #temp(patient_id, doc_id, serv_date, proc_code ) > values(1929596,'100175901','2005-10-03 00:00:00.000','90806') > insert #temp(patient_id, doc_id, serv_date, proc_code ) > values(1443313,'000175902','2005-09-29 00:00:00.000','90806') > insert #temp(patient_id, doc_id, serv_date, proc_code ) > values(2001093,'000175900','2005-10-05 00:00:00.000','90801') > insert #temp(patient_id, doc_id, serv_date, proc_code ) > values(1929596,'100175901','2005-10-07 00:00:00.000','90806') > insert #temp(patient_id, doc_id, serv_date, proc_code ) > values(1929596,'100175901','2005-10-10 00:00:00.000','90801') > insert #temp(patient_id, doc_id, serv_date, proc_code ) > values(1443313,'000175902','2005-10-29 00:00:00.000','90805') > insert #temp(patient_id, doc_id, serv_date, proc_code ) > values(1443313,'000175902','2005-10-29 00:00:00.000','90801') > > > select avg(cnt) > from ( > select t1.patient_id, count(*) as cnt > from #temp t1 > where t1.serv_date<=( > select min(t2.serv_date) > from #temp t2 > where t2.patient_id=t1.patient_id and t2.proc_code='90801') > group by t1.patient_id) t > > dean > > "SJ" <S*@discussions.microsoft.com> wrote in message > news:B85C4F8E-48A7-4C70-A018-3E22BC3D0732@microsoft.com... > > CREATE TABLE #temp > > (patient_id INT NULL, > > doc_id CHAR(9) NULL, > > serv_date DATETIME NULL, > > proc_code CHAR(6) NULL) > > > > The patient id is unique to each patient. > > > > acs_id prov_id serv_date > > proc_code > > 2001093 000175900 2005-10-03 00:00:00.000 90853 > > 1929596 100175901 2005-10-03 00:00:00.000 90806 > > 1443313 000175902 2005-09-29 00:00:00.000 90806 > > 2001093 000175900 2005-10-05 00:00:00.000 90801 > > 1929596 100175901 2005-10-07 00:00:00.000 90806 > > 1929596 100175901 2005-10-10 00:00:00.000 90801 > > 1443313 000175902 2005-10-29 00:00:00.000 90805 > > 1443313 000175902 2005-10-29 00:00:00.000 90801 > > > > I am trying to get on an average the visit (first/second/third) during > > which > > they get the 90801 proc_code? > > Hope this is clear. > > Thanks > > > > > > > > "David Portas" wrote: > > > >> "SJ" <S*@discussions.microsoft.com> wrote in message > >> news:9F11FE30-FF4D-4B63-BF03-8FD6072442E5@microsoft.com... > >> >I have patient data - am interested in patients receiving a certain > >> > diagnostic procedure. Need to find out on an average in the group of > >> > patients > >> > I am analyzing, on which visit they receive this procedure. > >> > Suppose there is a consumer X, who visits doc A - on the 5th visit the > >> > doc > >> > administers this diagnostic test and another doc could administer this > >> > test > >> > to a different consumer on the second visit - need to know overall the > >> > visit > >> > in which the patients get this procedure. > >> > Can anybody suggest a way to accomplish this.. > >> > Thanks > >> > >> How are we supposed to guess what your data, tables, keys, constraints, > >> etc > >> look like? Try posting again after you've read the advice in the > >> following > >> article. I'm sure someone can help you out if you include more > >> information. > >> http://www.aspfaq.com/etiquette.asp?id=5006 > >> > >> -- > >> David Portas > >> SQL Server MVP > >> -- > >> > >> > >> > > >  |
|||||||||||||||||||||||
Other interesting topics