December 21, 2009 at 12:15 pm
Here's what I'm running:
-- 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 Union all
Select 3,8
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
select DonationID
from @vDonationPreferences Don_Pref
inner join @vPreferences Pref
on Don_Pref.PrefID = Pref.PrefID
where PrefDesc = 'Female';
-- Follows ALL preferences
select DonationID
from @vDonationPreferences Don_Pref
inner join @vPreferences Pref
on Don_Pref.PrefID = Pref.PrefID
where PrefDesc = 'Male'
intersect
select DonationID
from @vDonationPreferences Don_Pref
inner join @vPreferences Pref
on Don_Pref.PrefID = Pref.PrefID
where PrefDesc = 'Muslim'
intersect
select DonationID
from @vDonationPreferences Don_Pref
inner join @vPreferences Pref
on Don_Pref.PrefID = Pref.PrefID
where PrefDesc = 'America'
intersect
select DonationID
from @vDonationPreferences Don_Pref
inner join @vPreferences Pref
on Don_Pref.PrefID = Pref.PrefID
where PrefDesc = 'Age 21-30';
-- Follows ANY preferences
select DonationID
from @vDonationPreferences Don_Pref
inner join @vPreferences Pref
on Don_Pref.PrefID = Pref.PrefID
where PrefDesc = 'Male'
union
select DonationID
from @vDonationPreferences Don_Pref
inner join @vPreferences Pref
on Don_Pref.PrefID = Pref.PrefID
where PrefDesc = 'Muslim'
union
select DonationID
from @vDonationPreferences Don_Pref
inner join @vPreferences Pref
on Don_Pref.PrefID = Pref.PrefID
where PrefDesc = 'America'
union
select DonationID
from @vDonationPreferences Don_Pref
inner join @vPreferences Pref
on Don_Pref.PrefID = Pref.PrefID
where PrefDesc = 'Age 21-30';
The first query is a simple one, just one criterion.
The second query checks to make sure ALL criteria are met, which is only true of Donation ID 3.
The third makes sure ANY criteria are met, which is true for Donation IDs 1, 2, 3, and 4. That is what you are describing, but it doesn't get the results you suggest are correct in your first post.
Which one is wrong, the description, or the expected results? They are contradictory.
- 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:43 pm
Sorry GSquared, but the query results are incorrect.
I'll go Donation by donation:
Assuming that Patient is Male (PrefID = 1), Location is America (PrefID=5), Age Between 21-10 (PrefID=3) and is Muslim (PrefID=8).
This gives 1,5,3,8 as the Patient Preferences.
Now looking at the Donations,
Donation 1
This Donation has PrefIDs (1,3) (Male And Age Between 21-30)
So this Donation is VALID for Above Client
Donation 2
This Donation has PrefIDs (1,4,5) (Male, Age 31-50, America)
This Donation is INVALID because of Age. Or Patient has age PrefID 3 (Age 21-30)
Donation 3
This Donation has PrefIDs (1,2,3,5,6,8) (MAle,Female, Age 21-30, America, Africa, Muslim)
This Donation is VALID as Donor specified BOTH Genders, Male and Female, Age 21-30, Both locations America and Africa and Muslim. Our Patient valid for this donation as he is male (Donor specified validity for Both Male and Female), Age 21-30 (Donor specified the same). Our Patient is in America (Donor made donation valid for AMerica and Africa along with other Preferences) and PAtient is Muslim (Donor also specified Muslim)
Donation 4
This Donation has PrefIDs (5,8) (America, Muslim)
This Donation is also VALID as our patient is in AMerica and Muslim.
Donation 5
This Donation Has PrefID (5) (Female)
This Donation is INVALID as Our Patient is Male and Donor specified that the donation should go to Females Only.
Donation 6
This Donation has PrefIDs(1,2,3,7) (Male,Female,Age 21-30,Christian)
This Donation is INVALID just because of religion. Our Patient is Muslim and DOnor Specified that the donation should go to Christian Male Or Female with AGe between 21 and 30
Hope this will give another Clear View of the scenario.
December 22, 2009 at 7:13 am
Okay, that clarifies the rules enough to work with.
What you need to do is modify my Union query by adding a simple "Not In" for the contra-rule. That will exclude the options that are tied to options that are the opposite.
Can you add that, or do you need to see a sample of what I'm talking about?
- 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 22, 2009 at 11:38 pm
What you need to do is modify my Union query by adding a simple "Not In" for the contra-rule. That will exclude the options that are tied to options that are the opposite.
I didn't get it. Can nyou please post the sample? It will be very helpful.
Thanks
December 23, 2009 at 7:40 am
I dug into this a bit more, and there's a flaw in the structure based on the logic.
It allows someone to pick donation ID 3 if they want America but don't want Africa, or if they want Africa but not America, but it doesn't allow them to pick a donation that lists age 31-50 if they want age 21-30, or vice versa.
In other words, some of the rules are inclusive, some are exclusive. You can't turn that into a boolean yes/no without further data, which isn't present in the tables. Computers work on yes/no (1/0), not on any other logic, so you can't do this with the tables as designed.
You need to somehow specify which sets of data allow "or" operations and which specify "not" operations. If they select preference ID 3 (21-30), they don't get options that list preference ID 4(31-50). If they sepect preference ID 5 (America), they get options that list America even if they also list Africa.
That's how I'm reading your description of the rules. Am I missing something here?
- 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 23, 2009 at 9:34 pm
That's how I'm reading your description of the rules. Am I missing something here?
I think you are very close to the point. If you closely see the structure, it has 4 Pools (Gender, Location, Age, Religion). These pools are joined with 'AND' condition with each other. If you go further, each pool has its own valid set of values. For example, Pool Gender has valid values Male and Female.
Now here is the tricky part. If the Donor selects multiple options within the same pool, the operator between the values/IDs of the respective Pool will be 'OR'. For example, If the donor selects both Male and Female in the gender pool, the condition will be like Gender = 'Male' OR Gender = 'Female', to cover both as valid for donation.
I have done this but with the help of cursors.
December 24, 2009 at 1:22 am
First, I'm glad this is a SQL Server 2005 forum. Here is a possible solution, but how you implement it is all up to you since you haven't provided us with the necessary information to help with that part of the problem.
EDIT: Actually, this looks like it is still wrong, hang on while I revisit it a bit.
2nd EDIT: Actually, this code does satisify the requirements unless I missed something. For the second patient, the only valid DonationID that can be returned is 5. DonationID 3 is not valid for the second patient as no location or age is specified in the definition of the patient therefor no match can be made for this record.
-- 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
;
select
*
from
@vDonationPreferences dp
inner join @vPreferences p
on (dp.PrefId = p.PrefId)
right join @vPreferenceType pt
on (p.TypeID = pt.TypeId)
where
1 = 1
order by
dp.DonationID;
with GenderPref as (
select
dp.DonationID,
dp.PrefId
from
@vDonationPreferences dp
inner join @vPreferences p
on (dp.PrefId = p.PrefId)
inner join @vPreferenceType pt
on (p.TypeID = pt.TypeId)
where
pt.TypeId = 1
),
AgePref as (
select
dp.DonationID,
dp.PrefId
from
@vDonationPreferences dp
inner join @vPreferences p
on (dp.PrefId = p.PrefId)
inner join @vPreferenceType pt
on (p.TypeID = pt.TypeId)
where
pt.TypeId = 3
),
LocationPref as (
select
dp.DonationID,
dp.PrefId
from
@vDonationPreferences dp
inner join @vPreferences p
on (dp.PrefId = p.PrefId)
inner join @vPreferenceType pt
on (p.TypeID = pt.TypeId)
where
pt.TypeId = 2
),
ReligionPref as (
select
dp.DonationID,
dp.PrefId
from
@vDonationPreferences dp
inner join @vPreferences p
on (dp.PrefId = p.PrefId)
inner join @vPreferenceType pt
on (p.TypeID = pt.TypeId)
where
pt.TypeId = 4
),
DonationIds as (
select distinct
DonationID
from
@vDonationPreferences
)
select distinct
d.DonationID,
gp.PrefId,
ap.PrefId,
lp.PrefId,
rp.PrefId
from
DonationIds d
left outer join GenderPref gp
on (d.DonationID = gp.DonationID)
left outer join AgePref ap
on (d.DonationID = ap.DonationID)
left outer join LocationPref lp
on (d.DonationID = lp.DonationID)
left outer join ReligionPref rp
on (d.DonationID = rp.DonationID)
;
/*
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
*/
declare @Patient table (
PatientID int,
GenderType int null,
AgeType int null,
LocationType int null,
ReligionType int null
);
insert into @Patient
select 1, 1,3,5,8 union all
select 2, 2,null,null,null
;
select * from @Patient;
with GenderPref as (
select
dp.DonationID,
dp.PrefId
from
@vDonationPreferences dp
inner join @vPreferences p
on (dp.PrefId = p.PrefId)
inner join @vPreferenceType pt
on (p.TypeID = pt.TypeId)
where
pt.TypeId = 1
),
AgePref as (
select
dp.DonationID,
dp.PrefId
from
@vDonationPreferences dp
inner join @vPreferences p
on (dp.PrefId = p.PrefId)
inner join @vPreferenceType pt
on (p.TypeID = pt.TypeId)
where
pt.TypeId = 3
),
LocationPref as (
select
dp.DonationID,
dp.PrefId
from
@vDonationPreferences dp
inner join @vPreferences p
on (dp.PrefId = p.PrefId)
inner join @vPreferenceType pt
on (p.TypeID = pt.TypeId)
where
pt.TypeId = 2
),
ReligionPref as (
select
dp.DonationID,
dp.PrefId
from
@vDonationPreferences dp
inner join @vPreferences p
on (dp.PrefId = p.PrefId)
inner join @vPreferenceType pt
on (p.TypeID = pt.TypeId)
where
pt.TypeId = 4
),
DonationIds as (
select distinct
DonationID
from
@vDonationPreferences
),
Donations as (
select distinct
d.DonationID,
gp.PrefId as GenPref,
ap.PrefId as AgePref,
lp.PrefId as LocPref,
rp.PrefId as RelPref
from
DonationIds d
left outer join GenderPref gp
on (d.DonationID = gp.DonationID)
left outer join AgePref ap
on (d.DonationID = ap.DonationID)
left outer join LocationPref lp
on (d.DonationID = lp.DonationID)
left outer join ReligionPref rp
on (d.DonationID = rp.DonationID)
)
select
pat.PatientID,
don.DonationID
from
@Patient pat
inner join Donations don
on (coalesce(pat.GenderType,0) = coalesce(don.GenPref,pat.GenderType,0) and
coalesce(pat.AgeType,0) = coalesce(don.AgePref,pat.AgeType,0) and
coalesce(pat.LocationType,0) = coalesce(don.LocPref,pat.LocationType,0) and
coalesce(pat.ReligionType,0) = coalesce(don.RelPref,pat.ReligionType,0))
;
December 24, 2009 at 2:55 am
Long Code Lynn Pettis :-).
I will definitely check it out later this evening.
Thanks.
December 24, 2009 at 9:43 am
Well, since you thought the code was long, and having gotten some sleep after solving this particular SQL logic problem, here is a rewrite of the code I had posted earlier. I think you will find it shorter.
with Prefs as (
select
dp.DonationID,
dp.PrefId,
pt.TypeId
from
@vDonationPreferences dp
inner join @vPreferences p
on (dp.PrefId = p.PrefId)
inner join @vPreferenceType pt
on (p.TypeID = pt.TypeId)
),
DonationIds as (
select distinct
DonationID
from
@vDonationPreferences
)
,
Donations as (
select distinct
d.DonationID,
gp.PrefId as GenPref,
ap.PrefId as AgePref,
lp.PrefId as LocPref,
rp.PrefId as RelPref
from
DonationIds d
left outer join Prefs gp
on (d.DonationID = gp.DonationID
and gp.TypeId = 1)
left outer join Prefs ap
on (d.DonationID = ap.DonationID
and ap.TypeId = 3)
left outer join Prefs lp
on (d.DonationID = lp.DonationID
and lp.TypeId = 2)
left outer join Prefs rp
on (d.DonationID = rp.DonationID
and rp.TypeId = 4)
)
select
pat.PatientID,
don.DonationID
from
@Patient pat
inner join Donations don
on (coalesce(pat.GenderType,0) = coalesce(don.GenPref,pat.GenderType,0) and
coalesce(pat.AgeType,0) = coalesce(don.AgePref,pat.AgeType,0) and
coalesce(pat.LocationType,0) = coalesce(don.LocPref,pat.LocationType,0) and
coalesce(pat.ReligionType,0) = coalesce(don.RelPref,pat.ReligionType,0))
;
December 28, 2009 at 9:34 pm
THANKS ALOT Lynn Pettis...
The solution is working perfectly, also with the new patient scenarios. I will check it with a few more patient scenarios and will upload it on my production server.
I was using cursors top solve this particular issue. Things were alright but I knew there would be some way to solve it within the query.
A Million Thanks once again...
December 28, 2009 at 9:54 pm
Atif Sheikh (12/28/2009)
THANKS ALOT Lynn Pettis...The solution is working perfectly, also with the new patient scenarios. I will check it with a few more patient scenarios and will upload it on my production server.
I was using cursors top solve this particular issue. Things were alright but I knew there would be some way to solve it within the query.
A Million Thanks once again...
Glad to help. I do, however, have a question for you. Do you understand how and why the code works? Using the code I provided, are you able to expand the necessary logic to accommodate additional requirements?
December 28, 2009 at 10:01 pm
Like I said, i will check it with different Patient Preferences. Currently, I checked it with a few and it gave me the rigfht results.
The code that I wrote to accomplish this is as under ...
Set NoCount On
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'
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'
-- Select * from @vPreferences
-- 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 Union ALl
Select 3,8
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
Insert into @vDonationPreferences
Select 6,1 Union ALl
Select 6,2 Union All
Select 6,3 Union All
Select 6,7
--Now I have a Patient with Preference like
--Male ( PrefID=1)
-- Location America (PrefID=5)
-- Age between 25
-- I need a query to return valid Donations That are:
-- DonationID 1
-- DonationID 3
-- DonationID 4
Declare @vPatientPrefs varchar(100)
Declare @vPatientPrefTypes varchar(100)
Set @vPatientPrefs = '1,5,3,8'
--Set @vPatientPrefs = '1,2,3,4,5,6,7,8'
Declare @d1 int, @pID int, @tID int, @tID2 int, @vOK int
Declare @vDons Table(DID int)
Declare C1 Cursor for
Select Distinct DonationID from @vDonationPreferences
where PrefID in (Select [value] from dbo.fnSplit(@vPatientPrefs,','))
Open C1
Fetch Next from C1 into @d1
While @@Fetch_Status = 0
begin
Declare C2 Cursor for Select a.PrefID , TypeID
from @vDonationPreferences a
Inner Join @vPreferences b on b.PrefID = a.PrefID
where DonationID = @d1
Order by TypeID,a.PrefID
Open C2
Fetch Next from C2 into @pID, @tID
While @@Fetch_Status = 0
begin
-- If First PrefID is Matched
if Exists(Select [value] from dbo.fnSplit(@vPatientPrefs,',') where [value] = @pID)
begin
Set @vOK = 1
Set @tID2 = @tID
While @tID = @tID2 and @@Fetch_Status = 0
begin
--Scroll through the next within same type as they become optional
Fetch Next from C2 into @pID, @tID
end
end
else
begin
-- If not found, check subsequent within the same type
Set @vOK = 0
Set @tID2 = @tID
While @tID = @tID2 and @@Fetch_Status = 0
begin
if Exists(Select [value] from dbo.fnSplit(@vPatientPrefs,',') where [value] = @pID)
BEGIN
-- If found, GOOD
Set @vOK = 1
End
Fetch Next from C2 into @pID, @tID
end
-- If not found within the same typeID, Its Not Valid Donation
if @vOK = 0
BREAK
Fetch Next from C2 into @pID, @tID
end
end
Deallocate C2
if @vOK = 1
begin
Insert into @vDons
Select @d1
end
Fetch Next from C1 into @d1
end
Deallocate C1
Select * from @vDons
December 28, 2009 at 10:12 pm
Not what I asked. I asked if you understood the logic in MY code. If you need to modify it for additional requirements (new demographic requirements to be met for instance)? If you add additional Age brackets, does the code need any adjustments, or is it simply the addition of another code for that preference type?
I want to know that you understand what I wrote so that you can support it. If you don't understand it, then it has no business going into a production environment.
December 28, 2009 at 10:36 pm
Yes, I do understand the logic written in your code. The code will be changed to get it implemented as I have another three Preference Types. These are DonationFor, Procedures and PublishedCases.
Procedure is same as the rest of the Preference Types. In DonationFor and
PublishedCase, Patient is specifed.
I will let post if there is any problem.
Thanks once again...
December 28, 2009 at 10:38 pm
As far as values within the Preference types are concerned, they will be changed as it is configurable. But I dont think it will change the structure of your code. I checked it with adding new ager bracket (Age 51 - 60). It executed with the desired results.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply