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/ex...g&mode=blended
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql
Showing posts with label certain. Show all posts
Showing posts with label certain. Show all posts
Friday, March 23, 2012
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/e...ble
nded
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
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/e...ble
nded
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
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)
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)
Wednesday, March 21, 2012
measuring traffic
hi
how can i measure the inbound/outbound traffic of a certain db?
(bytes sended / received)
thankx
mike schwarzYou can use SQL Server profiler to measure the stats on a specific DB, but
you won't be able to measure bytes sent / received. Open up Profiler on a
test machine so you can view all the available counters without slowing down
your production server. You can measure transactions, RPC calls,
NTUserNames, etc. But only Performance Monitor measures bytes sent/received
and since it's a Windows Tool, not a SQL tool, you won't be able to do it by
database unless you have a machine that has only SQL on it and only one
database in that instance.
Also, check Books Online for more Profiler information.
Hope that helps.
"Mike Schwarz" wrote:
> hi
> how can i measure the inbound/outbound traffic of a certain db?
> (bytes sended / received)
> thankx
> mike schwarz
>
>|||thankx... not helping much... maybe i will write something for my own
listening on port 1433 and sniffnig some packages out
thankx
"Catadmin" <Catadmin@.discussions.microsoft.com> schrieb im Newsbeitrag
news:04E1D671-2A98-479A-A938-DEB842BB3F60@.microsoft.com...
> You can use SQL Server profiler to measure the stats on a specific DB, but
> you won't be able to measure bytes sent / received. Open up Profiler on a
> test machine so you can view all the available counters without slowing
down
> your production server. You can measure transactions, RPC calls,
> NTUserNames, etc. But only Performance Monitor measures bytes
sent/received
> and since it's a Windows Tool, not a SQL tool, you won't be able to do it
by
> database unless you have a machine that has only SQL on it and only one
> database in that instance.
> Also, check Books Online for more Profiler information.
> Hope that helps.
>
> "Mike Schwarz" wrote:
> > hi
> > how can i measure the inbound/outbound traffic of a certain db?
> > (bytes sended / received)
> >
> > thankx
> >
> > mike schwarz
> >
> >
> >
how can i measure the inbound/outbound traffic of a certain db?
(bytes sended / received)
thankx
mike schwarzYou can use SQL Server profiler to measure the stats on a specific DB, but
you won't be able to measure bytes sent / received. Open up Profiler on a
test machine so you can view all the available counters without slowing down
your production server. You can measure transactions, RPC calls,
NTUserNames, etc. But only Performance Monitor measures bytes sent/received
and since it's a Windows Tool, not a SQL tool, you won't be able to do it by
database unless you have a machine that has only SQL on it and only one
database in that instance.
Also, check Books Online for more Profiler information.
Hope that helps.
"Mike Schwarz" wrote:
> hi
> how can i measure the inbound/outbound traffic of a certain db?
> (bytes sended / received)
> thankx
> mike schwarz
>
>|||thankx... not helping much... maybe i will write something for my own
listening on port 1433 and sniffnig some packages out
thankx
"Catadmin" <Catadmin@.discussions.microsoft.com> schrieb im Newsbeitrag
news:04E1D671-2A98-479A-A938-DEB842BB3F60@.microsoft.com...
> You can use SQL Server profiler to measure the stats on a specific DB, but
> you won't be able to measure bytes sent / received. Open up Profiler on a
> test machine so you can view all the available counters without slowing
down
> your production server. You can measure transactions, RPC calls,
> NTUserNames, etc. But only Performance Monitor measures bytes
sent/received
> and since it's a Windows Tool, not a SQL tool, you won't be able to do it
by
> database unless you have a machine that has only SQL on it and only one
> database in that instance.
> Also, check Books Online for more Profiler information.
> Hope that helps.
>
> "Mike Schwarz" wrote:
> > hi
> > how can i measure the inbound/outbound traffic of a certain db?
> > (bytes sended / received)
> >
> > thankx
> >
> > mike schwarz
> >
> >
> >
measuring traffic
hi
how can i measure the inbound/outbound traffic of a certain db?
(bytes sended / received)
thankx
mike schwarzYou can use SQL Server profiler to measure the stats on a specific DB, but
you won't be able to measure bytes sent / received. Open up Profiler on a
test machine so you can view all the available counters without slowing down
your production server. You can measure transactions, RPC calls,
NTUserNames, etc. But only Performance Monitor measures bytes sent/received
and since it's a Windows Tool, not a SQL tool, you won't be able to do it by
database unless you have a machine that has only SQL on it and only one
database in that instance.
Also, check Books Online for more Profiler information.
Hope that helps.
"Mike Schwarz" wrote:
> hi
> how can i measure the inbound/outbound traffic of a certain db?
> (bytes sended / received)
> thankx
> mike schwarz
>
>|||thankx... not helping much... maybe i will write something for my own
listening on port 1433 and sniffnig some packages out
thankx
"Catadmin" <Catadmin@.discussions.microsoft.com> schrieb im Newsbeitrag
news:04E1D671-2A98-479A-A938-DEB842BB3F60@.microsoft.com...
> You can use SQL Server profiler to measure the stats on a specific DB, but
> you won't be able to measure bytes sent / received. Open up Profiler on a
> test machine so you can view all the available counters without slowing
down
> your production server. You can measure transactions, RPC calls,
> NTUserNames, etc. But only Performance Monitor measures bytes
sent/received
> and since it's a Windows Tool, not a SQL tool, you won't be able to do it
by[vbcol=seagreen]
> database unless you have a machine that has only SQL on it and only one
> database in that instance.
> Also, check Books Online for more Profiler information.
> Hope that helps.
>
> "Mike Schwarz" wrote:
>
how can i measure the inbound/outbound traffic of a certain db?
(bytes sended / received)
thankx
mike schwarzYou can use SQL Server profiler to measure the stats on a specific DB, but
you won't be able to measure bytes sent / received. Open up Profiler on a
test machine so you can view all the available counters without slowing down
your production server. You can measure transactions, RPC calls,
NTUserNames, etc. But only Performance Monitor measures bytes sent/received
and since it's a Windows Tool, not a SQL tool, you won't be able to do it by
database unless you have a machine that has only SQL on it and only one
database in that instance.
Also, check Books Online for more Profiler information.
Hope that helps.
"Mike Schwarz" wrote:
> hi
> how can i measure the inbound/outbound traffic of a certain db?
> (bytes sended / received)
> thankx
> mike schwarz
>
>|||thankx... not helping much... maybe i will write something for my own
listening on port 1433 and sniffnig some packages out
thankx
"Catadmin" <Catadmin@.discussions.microsoft.com> schrieb im Newsbeitrag
news:04E1D671-2A98-479A-A938-DEB842BB3F60@.microsoft.com...
> You can use SQL Server profiler to measure the stats on a specific DB, but
> you won't be able to measure bytes sent / received. Open up Profiler on a
> test machine so you can view all the available counters without slowing
down
> your production server. You can measure transactions, RPC calls,
> NTUserNames, etc. But only Performance Monitor measures bytes
sent/received
> and since it's a Windows Tool, not a SQL tool, you won't be able to do it
by[vbcol=seagreen]
> database unless you have a machine that has only SQL on it and only one
> database in that instance.
> Also, check Books Online for more Profiler information.
> Hope that helps.
>
> "Mike Schwarz" wrote:
>
measuring traffic
hi
how can i measure the inbound/outbound traffic of a certain db?
(bytes sended / received)
thankx
mike schwarz
You can use SQL Server profiler to measure the stats on a specific DB, but
you won't be able to measure bytes sent / received. Open up Profiler on a
test machine so you can view all the available counters without slowing down
your production server. You can measure transactions, RPC calls,
NTUserNames, etc. But only Performance Monitor measures bytes sent/received
and since it's a Windows Tool, not a SQL tool, you won't be able to do it by
database unless you have a machine that has only SQL on it and only one
database in that instance.
Also, check Books Online for more Profiler information.
Hope that helps.
"Mike Schwarz" wrote:
> hi
> how can i measure the inbound/outbound traffic of a certain db?
> (bytes sended / received)
> thankx
> mike schwarz
>
>
|||thankx... not helping much... maybe i will write something for my own
listening on port 1433 and sniffnig some packages out
thankx
"Catadmin" <Catadmin@.discussions.microsoft.com> schrieb im Newsbeitrag
news:04E1D671-2A98-479A-A938-DEB842BB3F60@.microsoft.com...
> You can use SQL Server profiler to measure the stats on a specific DB, but
> you won't be able to measure bytes sent / received. Open up Profiler on a
> test machine so you can view all the available counters without slowing
down
> your production server. You can measure transactions, RPC calls,
> NTUserNames, etc. But only Performance Monitor measures bytes
sent/received
> and since it's a Windows Tool, not a SQL tool, you won't be able to do it
by[vbcol=seagreen]
> database unless you have a machine that has only SQL on it and only one
> database in that instance.
> Also, check Books Online for more Profiler information.
> Hope that helps.
>
> "Mike Schwarz" wrote:
sql
how can i measure the inbound/outbound traffic of a certain db?
(bytes sended / received)
thankx
mike schwarz
You can use SQL Server profiler to measure the stats on a specific DB, but
you won't be able to measure bytes sent / received. Open up Profiler on a
test machine so you can view all the available counters without slowing down
your production server. You can measure transactions, RPC calls,
NTUserNames, etc. But only Performance Monitor measures bytes sent/received
and since it's a Windows Tool, not a SQL tool, you won't be able to do it by
database unless you have a machine that has only SQL on it and only one
database in that instance.
Also, check Books Online for more Profiler information.
Hope that helps.
"Mike Schwarz" wrote:
> hi
> how can i measure the inbound/outbound traffic of a certain db?
> (bytes sended / received)
> thankx
> mike schwarz
>
>
|||thankx... not helping much... maybe i will write something for my own
listening on port 1433 and sniffnig some packages out
thankx
"Catadmin" <Catadmin@.discussions.microsoft.com> schrieb im Newsbeitrag
news:04E1D671-2A98-479A-A938-DEB842BB3F60@.microsoft.com...
> You can use SQL Server profiler to measure the stats on a specific DB, but
> you won't be able to measure bytes sent / received. Open up Profiler on a
> test machine so you can view all the available counters without slowing
down
> your production server. You can measure transactions, RPC calls,
> NTUserNames, etc. But only Performance Monitor measures bytes
sent/received
> and since it's a Windows Tool, not a SQL tool, you won't be able to do it
by[vbcol=seagreen]
> database unless you have a machine that has only SQL on it and only one
> database in that instance.
> Also, check Books Online for more Profiler information.
> Hope that helps.
>
> "Mike Schwarz" wrote:
sql
Measure should only be calculated for certain Dimension Attributes
Hi,
I've created a Dimension - Organisation (company_name,
department_name,room_name). The primary key is Organisation_ID and I
have a Measure Room Utilization. I'd like to show this Measure only
when Room_Name is selected and with every other member of this
Dimension there should occur a 0 for Room Utilization.
It works when I create a hierachy inside the Dimension with the
following MDX statement:
CREATE MEMBER CURRENTCUBE.[MEASURES].Room_Utilization
AS
case when [Organisation].CurrentMember.Level.Ordinal = 4
then [Measures].Room_Utilization
else 0
end
VISIBLE = 1;
how can I get it going when my Organisation Dimension has the following
attributes:
company_name,
department_name,
room_name
without hierachy and I only want to calculate the Measure Room
Utilization for the room_name attribute?
Here is how you can do it with Analysis Services 2005
Create RoomUtilization;
(Organization.[Room Name].[Room Name].MEMBERS, Measures.RoomUtilization) = Measures.PhysicalMeasureRoomUtilization;
And, of course, you would make PhysicalMeasureRoomUtilization a hidden one.
HTH,
Mosha (http://www.mosha.com/msolap)
sqlMonday, March 19, 2012
MDX: Null value to replace column in Query
I have an MDX query that is not doing what I want it to do.
Currently, I have an application that expects to recieve a certain
number of columns in order to make a chart.In SQL, if I was not
requesting data for all of the columns I could replace the column name
with a NULL and still recieve the other information with the in the
appropriate format (Except for that column would have all NULL values).
I cannot get this to work in MDX... an example in SQL which does work
follows:
i.e.
Requesting real information
SELECT id as col1, dog as col2 FROM SQL
Replace column with null but recieve same table format
SELECT NULL as col1, dog as col2 FROM SQL
I cannot figure out how to do this when requesting data with MDX in
Analysis Services. I have looked all through "MDX Solutions" and cannot
find the solution :) Although there was tons of great stuff in there.
TONS!
My MDX query that requests real data for all columns and works fine is
as follows:
SELECT {
[Measures].[YN] ,
[Measures].[YD] ,
[Measures].[YV] ,
} ON 0,
NONEMPTY(
{[Product].[p Hier Ty3 Bg 1 1].&[R104],[Product].[p Hier Ty3 Bg 1
1].&[R706]}
*{[Customer].[c Hier Ty3 Bg 1 3].&[Australia]}
*EXCEPT([Date 1].[c_month].[2004_M01]:[Date 1].[c_month].[2004_M03],
[Date 1].[c_month].[ALL])
*EXCEPT([Customer].[c Hier Ty3 Bg 2 2].Members,[Customer].[c Hier Ty3
Bg 2 2].[All])
) ON 1
FROM Mimir04
But if I want an empty column to replace {[Customer].[c Hier Ty3 Bg 1
3].&[Australia]}, my guess at a solution (coming from SQL and being a
newbie at MDX) was to put a null set in place of this dimension call...
{NULL}. Unfortunately, this means that nothing gets returned
What exactly is the solution to returning an empty column?
Could you please clarify whether you'd like empty rows or empty columns?
Or, best, show an example of how you expect the query result to look like (axes contents and cell data)?
Thank you
Monday, March 12, 2012
MDX to aggregate measure over specific dimensions
Hi,
Hi Chris, many thanks for your reply.
This indeed worked. Going back to my previous example, I created a calculated member as:
Hi, yes I thought those were my two options.
I don't want to create another measure group as I'll have duplicate measures and the table with this measure in is large (it's a requirement in our system to keep processing time to a minimum). I was hoping that there would be a solution where I didn't have to list every dimension I wanted to remove from the slice (and remember to add to the list if I add dimensions in the future), but at least I have a solution!
Many thanks for your help.
James
I'm trying to write a calculated member in SSAS 2005 that will only aggregate across certain dimensions. For example, say I have five dimensions: D1 - 5. I only want the member to aggregate across three of these dimensions. So in the cube browser, when I drag these three dimensions in, I get the correct aggregated value. But when I then drag dimensions four and five in, I want this value to stay the same. (The measure is currently in a measure group that uses all five dimensions).
I was thinking that the solution would be to have an MDX expression of the form
([Measures].[Measure],
[D1].CurrentMember,
[D2].CurrentMember,
[D3].CurrentMember,
[D4].[(All)],
[D5].[(All)])
but I would prefer not to have to list all dimensions and their hierarchies, and have to remember to add to this list if I add a dimension in the future. In SSAS 2000 I had a lookup cube that only contained the dimensions I wanted to slice by.
My other solution was to create a named query on the fact table that this measure is currently in, create a measure group on that, and then set up the dimension usage so that only the dimensions I want to slice by are referenced. However, I'm thinking that there must be a better solution, probably using some MDX that I don't know about!
Thanks in advance.
James
Take a look at the MDX Root function. Here's an example of something that might work for you using Adventure Works:
with member measures.rootdemo as (
root([Date].[Calendar Year].currentmember),
root([Product].[Category].currentmember),
root([Customer]),
[Measures].[Internet Sales Amount]
)
select
[Date].[Calendar Year].[Calendar Year].members
*
[Date].[Calendar Semester of Year].[Calendar Semester of Year].members
*
[Customer].[Country].[Country].members
on 0,
[Product].[Category].[Category].members
on 1
from [Adventure Works]
where(measures.rootdemo)
You still need to list all the dimensions but at least you don't need to list the hierarchies. Watch out for this 'feature' of the function, though, which occurs when more than one member from a hierarchy is in scope:
with member measures.rootdemo as (
root([Date].[Calendar Year].currentmember),
root([Product].[Category].currentmember),
root([Customer]),
[Measures].[Internet Sales Amount]
)
select
[Date].[Calendar Year].[Calendar Year].members
*
[Date].[Calendar Semester of Year].[Calendar Semester of Year].members
on 0,
[Product].[Category].[Category].members
on 1
from [Adventure Works]
where(measures.rootdemo,{[Customer].[Country].&[Australia],[Customer].[Country].&[United Kingdom]})
HTH,
Chris
|||Hi Chris, many thanks for your reply.
This indeed worked. Going back to my previous example, I created a calculated member as:
(Root([D4]),
Root([D5]),
[Measures].[Measure])
and the measure is only sliced by dimensions D1, D2 and D3.
Thanks for your help!
James
|||
Another approach to this problem is to put these measures into dedicated measure group which excludes dimensions D4 and D5 - then you will get the aggregates you need without calculated members. Of course, if you sometimes do need detailed information over them, then the approach with calculated member is the right one.
One more note - you don't have to use Root() function if all the attributes in your dimensions are aggregatable. You will get better performance if you simply use
([D4].[All], [D5].[All], [Measures].[Measure])
|||Hi, yes I thought those were my two options.
I don't want to create another measure group as I'll have duplicate measures and the table with this measure in is large (it's a requirement in our system to keep processing time to a minimum). I was hoping that there would be a solution where I didn't have to list every dimension I wanted to remove from the slice (and remember to add to the list if I add dimensions in the future), but at least I have a solution!
Many thanks for your help.
James
Subscribe to:
Posts (Atom)