MS SQL 2000 Query Help

I have patient data - am interested in patients receiving a certain
diagnostic procedure. Need to find out on an average in the group of patient
s
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"SJ" <SJ@.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
--|||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" <SJ@.discussions.microsoft.com> wrote in message
> news:9F11FE30-FF4D-4B63-BF03-8FD6072442E5@.microsoft.com...
> How are we supposed to guess what your data, tables, keys, constraints, et
c
> 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
> --
>
>|||On Thu, 1 Dec 2005 13:44:02 -0800, "SJ" <SJ@.discussions.microsoft.com>
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 whic
h
>they get the 90801 proc_code?
>Hope this is clear.
>Thanks
It's clear.
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
"SJ" <SJ@.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:
>|||Thank You - this is .
"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" <SJ@.discussions.microsoft.com> wrote in message
> news:B85C4F8E-48A7-4C70-A018-3E22BC3D0732@.microsoft.com...
>
>