Sum of known rows

domenica 15 aprile 2007 - 09.54

Elenco Threads

Stanze Forum

Aggiungi ai Preferiti

Cerca nel forum

aleplgr Profilo | Junior Member

51 messaggi | Data Invio: dom 15 apr 2007 - 09:54

Hi! I have a table of patients who executed a test, each test has 10 items and I have to generate a new column with the sum of 3 of those 10 items.
The original table columns are this: PatientID Date Items Points Tester.
Date is the date the test took place, a test is started and finished at the same date
Items are the 10 items of the test, one in each row
Tester is the doctor that applied the test to the patient.
Points is the score that the patient got in that item, that date, applied by that tester.
A tester can apply the same test to the same patient the same day more than once.
Two different testers can apply the same test to the same patient the same date so that date one patient will have 2 scores for each item.

I need to create a table with this columns:
PatientID Date Tester SumPoints
(where SumPoints is the sum of 3 known items of the test, for example Item1, Item2 and Item3)

I thought of doing something like this:
find maxpat minpat
while minpat < maxpat
select minpat date
select minpat tester
select minpat item1
select minpat item2
select minpat item3
result= item1+item2+item3
add result to final table
find another tester for the same date and patient calculate result, add to final table
find another date for the same tester and patient calculate result add to final table
increment minpat

what do you think? could this work? is there a better way to do it?
Thanks in advance

alx_81 Profilo | Guru

8.814 messaggi | Data Invio: mar 17 apr 2007 - 23:54

>Hi!
Hi!

>I have a table of patients who executed a test, each test
>has 10 items and I have to generate a new column with the sum
>of 3 of those 10 items.
>The original table columns are this: PatientID Date Items Points
>Tester.
>Date is the date the test took place, a test is started and finished
>at the same date
>Items are the 10 items of the test, one in each row
>Tester is the doctor that applied the test to the patient.
>Points is the score that the patient got in that item, that date,
>applied by that tester.
>A tester can apply the same test to the same patient the same
>day more than once.
>Two different testers can apply the same test to the same patient
>the same date so that date one patient will have 2 scores for
>each item.
>
>I need to create a table with this columns:
>PatientID Date Tester SumPoints
>(where SumPoints is the sum of 3 known items of the test, for
>example Item1, Item2 and Item3)
>
>I thought of doing something like this:
>find maxpat minpat
>while minpat < maxpat
>select minpat date
>select minpat tester
>select minpat item1
>select minpat item2
>select minpat item3
>result= item1+item2+item3
>add result to final table
>find another tester for the same date and patient calculate result,
>add to final table
>find another date for the same tester and patient calculate result
>add to final table
>increment minpat
maybe it's sufficient a sql script more simple than this, but i need to know:
- What's you DBMS? I can help you if you use SQL Server 2000/2005, Express, MSDE or Access

then
- Post us the create table statement
- Give us some sample data with sample insert statement, please
>
>what do you think? could this work? is there a better way to
>do it?
>Thanks in advance
i'll try

>
>
>

Alx81 =)

http://blogs.dotnethell.it/suxstellino

aleplgr Profilo | Junior Member

51 messaggi | Data Invio: gio 19 apr 2007 - 11:42

Hi! It turned to be easier than I thought
Here's a solution:

select PatientID, Date, Tester, Sum(Points) as SumPoints
from table
where Items in ('itemA', 'itemB', 'itemC')
group by PatientID, Date, Tester

Thanks anyway. I have plenty of queries to do and I'll be asking a lot...
Thanks

aleplgr Profilo | Junior Member

51 messaggi | Data Invio: gio 19 apr 2007 - 11:42

Hi! It turned to be easier than I thought
Here's a solution:

select PatientID, Date, Tester, Sum(Points) as SumPoints
from table
where Items in ('itemA', 'itemB', 'itemC')
group by PatientID, Date, Tester

Thanks anyway. I have plenty of queries to do and I'll be asking a lot...
Thanks

Torna su

Stanze Forum

Elenco Threads

Partecipa anche tu! Registrati!

Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !