medical records"SQL HELP!"

hi. im martin.. you guys are really amzing!!i really love this group
discussion..
i am new in sql and i am working in a certain project..specifically,about
medical records..
my tables are:
"Patients"
patient_ID int
Lname nvarchar
Fname nvarchar
Mname nvarchar
DateofBirth datetime
Weight int
Temp int
Address nvarchar
"Diseases"
Disease_ID int
Patient_ID int
Description nvarchar
Medication nvarchar
Classification nvarchar
"Check_Ups"
CheckUP_ID int
Patient_ID int
DateofCheckUp datetime
Diagnosis nvarchar
Results nvarchar
my problem is,i need to make a report, this is how it looks like:
sample only:
DiseaseDescription age range/sex
Total Total
1-4 5-15 15-40 40-65
65-up
M l F M l F M l F M l F
M l F M l F
fever 2 l 2 2 l 2 2 l 2 2 l 2
2 l 2 10 l 10 = 20
Flu 1 l 3 1 l 4 1 l 3 1 l 5
1 l 4 5 l 19 = 24
can u plz help me how to do this and suggest on how can i improve my
tables?plZZZZ..THANKS A LOT in advance..
seeyah guyz..!Martin
Please post DDL+ sample data+ expected result . What is your Primary keys
defined on tables?
"I'm Martin plz help me!!!" <I'm Martin plz help
me!!!@.discussions.microsoft.com> wrote in message
news:48E16803-6D86-422B-A206-F407C0E7D48A@.microsoft.com...
> hi. im martin.. you guys are really amzing!!i really love this group
> discussion..
> i am new in sql and i am working in a certain project..specifically,about
> medical records..
> my tables are:
> "Patients"
> patient_ID int
> Lname nvarchar
> Fname nvarchar
> Mname nvarchar
> DateofBirth datetime
> Weight int
> Temp int
> Address nvarchar
> "Diseases"
> Disease_ID int
> Patient_ID int
> Description nvarchar
> Medication nvarchar
> Classification nvarchar
>
> "Check_Ups"
> CheckUP_ID int
> Patient_ID int
> DateofCheckUp datetime
> Diagnosis nvarchar
> Results nvarchar
> my problem is,i need to make a report, this is how it looks like:
> sample only:
> DiseaseDescription age range/sex
> Total Total
> 1-4 5-15 15-40 40-65
> 65-up
> M l F M l F M l F M l F
> M l F M l F
> fever 2 l 2 2 l 2 2 l 2 2 l 2
> 2 l 2 10 l 10 = 20
> Flu 1 l 3 1 l 4 1 l 3 1 l 5
> 1 l 4 5 l 19 = 24
>
> can u plz help me how to do this and suggest on how can i improve my
> tables?plZZZZ..THANKS A LOT in advance..
> seeyah guyz..!
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>|||primary keys are patient_id, disease_ID, CheckUp_ID
my tables are:
"Patients"
patient_ID int
Lname nvarchar
Fname nvarchar
Mname nvarchar
DateofBirth datetime
Weight int
Temp int
Address nvarchar
"Diseases"
Disease_ID int
Patient_ID int
Description nvarchar
Medication nvarchar
Classification nvarchar
"Check_Ups"
CheckUP_ID int
Patient_ID int
DateofCheckUp datetime
Diagnosis nvarchar
Results nvarchar
expected output: DAy or WEEk
Disease age range/sex Total Total
1-4 5-15 15-40 40-65 65-up
M l F M l F M l F M l F M l F M l F
fever 2 l 2 2 l 2 2 l 2 2 l 2 2 l 2 10 l 10 = 20
Flu 1 l 3 1 l 4 1 l 3 1 l 5 1 l 4 5 l 19 = 24
M=male F=female
sex from "patient" table
disease from "disease" table
age from "patient" table
count by age sex and age range. then get the total male and female
and the overall total..|||By posting DDL I meant
CREATE TABLE balala
(
col INT NOT NULL,
...
)
INSERT INTO bababa VALUES (babababab)
My expected result is
......
"I''m Martin plz help me!!!" <ImMartinplzhelpme@.discussions.microsoft.com>
wrote in message news:15DD238C-CA08-4F9E-8844-A59219F5DA60@.microsoft.com...
>
> primary keys are patient_id, disease_ID, CheckUp_ID
> my tables are:
> "Patients"
> patient_ID int
> Lname nvarchar
> Fname nvarchar
> Mname nvarchar
> DateofBirth datetime
> Weight int
> Temp int
> Address nvarchar
> "Diseases"
> Disease_ID int
> Patient_ID int
> Description nvarchar
> Medication nvarchar
> Classification nvarchar
>
> "Check_Ups"
> CheckUP_ID int
> Patient_ID int
> DateofCheckUp datetime
> Diagnosis nvarchar
> Results nvarchar
>
> expected output: DAy or WEEk
> Disease age range/sex Total Total
> 1-4 5-15 15-40 40-65 65-up
> M l F M l F M l F M l F M l F M l F
> fever 2 l 2 2 l 2 2 l 2 2 l 2 2 l 2 10 l 10 = 20
> Flu 1 l 3 1 l 4 1 l 3 1 l 5 1 l 4 5 l 19 = 24
> M=male F=female
> sex from "patient" table
> disease from "disease" table
> age from "patient" table
> count by age sex and age range. then get the total male and female
> and the overall total..
>|||I'm so sorry Uri..i'm still designing my project on papers.still don't know
how to implement it on the sql..just wanna have ideas from you guyzz..|||Well, maybe you eant to look at this article
http://www.databaseanswers.com/data_models/index.htm -- examples
database design
"I''m Martin plz help me!!!" <ImMartinplzhelpme@.discussions.microsoft.com>
wrote in message news:5E103CC2-37F6-41B5-8777-7EC4326AEFC3@.microsoft.com...
> I'm so sorry Uri..i'm still designing my project on papers.still don't
> know
> how to implement it on the sql..just wanna have ideas from you guyzz..|||plz..help me..|||thanksss...|||On Tue, 2 Aug 2005 00:25:02 -0700, "I'm Martin plz help me!!!" <I'm
Martin plz help me!!!@.discussions.microsoft.com> wrote:
>my problem is,i need to make a report, this is how it looks like:
Hi Martin,
No -- your problem is that this design has several errors.
>"Patients"
>patient_ID int
>Lname nvarchar
>Fname nvarchar
>Mname nvarchar
>DateofBirth datetime
>Weight int
>Temp int
>Address nvarchar
This means that a patient can only have one weight and one temp. Most
doctors prefer to see a history. Also, they will want to store weight
and temp with at least one decimal position. Remove Weight and Temp from
this table and put them in a new one:
"Examinations"
patient_ID int (PK, FK)
ExaminationDate datetime (PK)
Weight decimal(4,1)
Temp decimal(3,1)
>"Diseases"
>Disease_ID int
>Patient_ID int
>Description nvarchar
>Medication nvarchar
>Classification nvarchar
Remove the patient_ID from this table. The flu will always be the flu,
regardless of who has suffered from it.
Instead, create a new table that links patients to diseases. You might
want a date on that table too (maybe two dates: date diagnosed and date
cured)
Not sure about mediaction, as I'm not a doctor. Will the same medication
always be used for a disease, then it's in the right table. But it the
medication can be different for each patient, move it to the new table
mentioned above as well. Or maybe to yet another new table, if multiple
medications can be given for one disease, or if one medication can be
given to treat multiple diseases at once
>"Check_Ups"
>CheckUP_ID int
>Patient_ID int
>DateofCheckUp datetime
>Diagnosis nvarchar
>Results nvarchar
Ah, so you do have a table for checkups. This is where the weight and
temp columns should go. You don't need an examinations table after all.
>can u plz help me how to do this and suggest on how can i improve my
>tables?
I think that the best place to start is here:
http://www.amazon.com/exec/obidos/external-search?keyword=data%20modeling&mode=blended
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)