December 21, 2009 at 9:08 am
I need a query to get valid donations against the preferences of patient. The donationas are also made against preferences. Thedetails and table structure is as under;
-- I havePreference Types as Under;
Declare @vPreferenceType Table (TypeId int, TypeDesc varchar(10))
Insert into @vPreferenceType
Select 1,'Gender' Union All
Select 2,'Location' Union All
Select 3,'Age' Union All
Select 4,'Religion'
-- Preferences with respect to Types as Under;
Declare @vPreferences Table (PrefId int, TypeID int, PrefDesc varchar(10))
Insert into @vPreferences
Select 1,1,'Male' Union ALL
Select 2,1,'Female' Union ALL
Select 3,3,'Age 21-30' Union ALL
Select 4,3,'Age 31-50' Union ALL
Select 5,2,'America' Union ALL
Select 6,2,'Africa' Union ALL
Select 7,4,'Christian' Union ALL
Select 8,4,'Muslim'
-- Now I Have Different Donations according to above defined Preferences in @vPreferences
Declare @vDonationPreferences Table (DonationID int,PrefId int)
Insert into @vDonationPreferences
--Donation for Males and Age Between 21 - 30
-- No Matter what Location or Religion
Select 1,1 Union ALl
Select 1,3
Insert into @vDonationPreferences
--Donation for Males and Age Between 31 - 50 And Location is America
-- No Matter what Religion
Select 2,1 Union ALl
Select 2,4 Union ALl
Select 2,5
Insert into @vDonationPreferences
--Donation Both Males and Females
--and Age Between 21 - 30 And Location is America Or Africa
-- No Matter what Religion
Select 3,1 Union ALl
Select 3,2 Union ALl
Select 3,3 Union ALl
Select 3,5 Union ALl
Select 3,6
Insert into @vDonationPreferences
--Donation for Location is America and Religion is Muslim
-- No Matter what Gender or Age
Select 4,5 Union ALl
Select 4,8
Insert into @vDonationPreferences
--Donation for Female
-- No Matter what Location,Religion,Gender or Age
Select 5,2
Now I have a Patient with Preference like
Male ( PrefID=1)
Location America (PrefID=5)
Religion Muslim (PrefID=8)
Age between 25
I need a query to return valid Donations That are:
DonationID 1
DonationID 3
DonationID 4
Another Patient with Preference like
Female ( PrefID=2)
I need a query to return valid Donations That are:
DonationID 3
DonationID 5
Thanks in advance.
December 21, 2009 at 9:11 am
I'm not sure what you're asking for. The tables seem pretty clear, but is what you need just a query that joins preferences to people and to options? If so, it should be a pretty simple select statement. Or is there more to it that I'm not seeing?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 21, 2009 at 9:26 am
It seems quite simple. But I have been struggling with this for 4 hours.
I need a query to get the valid donations as discussed in the code.
December 21, 2009 at 9:32 am
Please Add the following record for DonationID 3 in vDonationPreferences
Select 3,8
Thanks
December 21, 2009 at 9:55 am
I'm afraid I don't understand the logic for the first query.
If I query it so that it gets the Donation IDs that match all criteria, it just comes up with ID 3, not the other 2 you mention. If I query so that it gets all possible matches, it gets a lot more than what you listed.
What are the rules you're using? When you say you want donations for male, muslim, American, age 21-30, do you want all possible matches, or do you just want matches that fit all given criteria, or something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 21, 2009 at 10:13 am
Patient with Preference like
Male ( PrefID=1)
Location America (PrefID=5)
Religion Muslim (PrefID=8)
Age between 25 (PrefID=3)
Here is the explanation;
The above condition means All the DonationIDs are valid if PrefID 1,5,8,3
are present within the same DonationID.
Any Donation having More than above Preferences are Invalid. For example, Say, if have another Preference type of City and donation stating that the city should be Newyork along with the above preferences, it will be invalid for selection.
BUT if Any donation with PrefID of Male and Female within it, it is valid for both Males and Females. Ofcoure, a patient would be having one gender and one age between 21-30 or 31-50 (assuming all patients will be between 20-50).
So the valid Donations will be: (Hope you have added the record i mentioned)
DonationID 1 --
DonationID 3 -- 3 is valid as it is for both Male and Female, For Americans (also for Africans ), Muslims, Age between 21-30, and Patient is Male with other criterias as stated in the scenario.
DonationID 4
I hope you got my point...:-D
December 21, 2009 at 10:35 am
Atif Sheikh (12/21/2009)
Patient with Preference likeMale ( PrefID=1)
Location America (PrefID=5)
Religion Muslim (PrefID=8)
Age between 25 (PrefID=3)
Here is the explanation;
The above condition means All the DonationIDs are valid if PrefID 1,5,8,3
are present within the same DonationID.
Any Donation having More than above Preferences are Invalid. For example, Say, if have another Preference type of City and donation stating that the city should be Newyork along with the above preferences, it will be invalid for selection.
BUT if Any donation with PrefID of Male and Female within it, it is valid for both Males and Females. Ofcoure, a patient would be having one gender and one age between 21-30 or 31-50 (assuming all patients will be between 20-50).
So the valid Donations will be: (Hope you have added the record i mentioned)
DonationID 1 --
DonationID 3 -- 3 is valid as it is for both Male and Female, For Americans (also for Africans ), Muslims, Age between 21-30, and Patient is Male with other criterias as stated in the scenario.
DonationID 4
I hope you got my point...:-D
You said that :
The above condition means All the DonationIDs are valid if PrefID 1,5,8,3
The only DonationId that meets this criteria is DonationID 3. Is this what you would expect?
December 21, 2009 at 10:43 am
DonationID 1 ,3 and 4 are valid for first Scenario.
Its because DonationID 1 is for PrefID 1 (Male) and PrefID 3 (Age between 21-30). Patient PrefIDs have PrefID 1 and PrefID 3. The rest Preferences are not stated so they become optional.
Same is case for DonationID 4. It is for America and Muslim. Patient PrefIDs have PrefID 5 and PrefID 8. The rest Preferences are not stated so they become optional.
December 21, 2009 at 10:54 am
How and where is the patient preferences stored?
This doesn't appear to be defined any where in your problem statement.
December 21, 2009 at 10:56 am
Doesn't that contradict your previous post? Before you said that the donation must meet all of the criteria and no more criteria
Any Donation having More than above Preferences are Invalid.
This is quite confusing, now some of the preferences are required and some are optional? Can you give us a better explanation of what you are trying to do? Maybe you can include a description of the business process this represents.
December 21, 2009 at 10:59 am
Patient Preferences are generated through the biodata of the patient in the system. Overe here, it could be assumed as stated in the Scenario.
December 21, 2009 at 11:16 am
Lets take the business process.
We are designing a Donation System.
1) Now, Donor Donates $100 for Patients with gender=Males and Age between 20-30.
This makes one donations.
2) Now we have a patient. He is Male, Age is 26, living in America and he is Muslim. This all information comes from his biodata that we get when he arrives to our center.
So the Patient Preferences are MALE, AGE Between 20-31, American and Muslim.
The Donation made above in (1) just stated that the Patient Should be MALE and age should be between 20-30. So tjis donation is applicable to our patient, registered in (2).
This means if Donor specifies a preference, it is mandatory for the patient to have it. Other wise, if Donor DID NOT specified a specific preference (American and Muslim are not specified by donor in our this specific case), so there becomeoptional. No need to check these PrefIDs with Patient.
Just think of a Donation company donating to certain patients in a hospital. Donors donates (sometimes) very specifically where to spent their donations and where NOT.
December 21, 2009 at 11:41 am
Atif Sheikh (12/21/2009)
Lets take the business process.We are designing a Donation System.
1) Now, Donor Donates $100 for Patients with gender=Males and Age between 20-30.
This makes one donations.
2) Now we have a patient. He is Male, Age is 26, living in America and he is Muslim. This all information comes from his biodata that we get when he arrives to our center.
So the Patient Preferences are MALE, AGE Between 20-31, American and Muslim.
The Donation made above in (1) just stated that the Patient Should be MALE and age should be between 20-30. So tjis donation is applicable to our patient, registered in (2).
This means if Donor specifies a preference, it is mandatory for the patient to have it. Other wise, if Donor DID NOT specified a specific preference (American and Muslim are not specified by donor in our this specific case), so there becomeoptional. No need to check these PrefIDs with Patient.
Just think of a Donation company donating to certain patients in a hospital. Donors donates (sometimes) very specifically where to spent their donations and where NOT.
You have a slight ambiguity in your definitions, I believe. DonationId 3 can be coded two ways. One way just as you have shown, the other by simply dropping the Male/Female requirement. Either way, it doesn't matter as you can only have Male or Female. Religion, on the other hand, even if you included all religions you have listed, could restrict a donation as a patient may have a religion other than one you specificly listed, such as Athiest, Agnostic, Jewish, Hindu, Taoist, etc.
December 21, 2009 at 11:50 am
Sorry if there is still ambiguity.
And you are right. If some onne donated for religion that is not followed by the patient, He is NOT eligible. Even if he meets other preferences. Any one preference mentioned by Donor and Patient is not holding it, he is NOT valid for that donation.
As faras Male/Female preference is concerned, a patient can be male of female. Donor can donate for male, female or both. thats his own choice. In case of both (assuming no other gender dpecified in the system for selection by Donor), this one GENDER preference will be matched by each patient. After this, it depopends on the rest of the preferences specified by the Donor (if any) to make the patient Valid for his/her donation.
December 21, 2009 at 11:59 am
I think you missed my point. Why code both Male and Female preferences when not coding either has the same result? No gender preference is the same as both genders being coded.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply