Programmin Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Programmin Help

Author

Topic

chrisww2
Starting Member

4 Posts

Posted - 2005-10-25 : 11:54:18

Please help if you can:

Here is a brief description from the dev document:

Purpose: Provide a flag for the generation procedures to use to remove records from batches that do not meet the minimum requirements as defined by the campaign. Examples would be valid Gender, on drug question, and age validation. Generation procedures will require a ‘1’ for generation to take place. This will also be used for reporting purposes.

The eligible flag state is determined at the campaign level by having all required business rules to receive communications as having been met. A daily maintenance SPROC for each campaign will be run which will check all of the information on participants which has been updated in the last day and set their Eligibility status accordingly.

Note 1: The Eligible flag is channel independent.
Note 2: If someone was eligible at one time then they could have received 1 or more communications and could be anywhere in the stream.

Participant Table
Create a new column in Participants table named “Campaign_Eligibility” as a small int.
NULL (default) = Unknown: required data is missing from the profile such as required questions or email address.
0 = Ineligible: participant has specific information that makes them ineligible such as an off drug question.

1 = Eligible: participant meets all of the minimum user data requirements and business rules necessary to be included in the generation of the first communication for a specific campaign, not to include opt status. This does include required demographic data and survey questions.

2 = Complete: participant has received all communications for a specific campaign and has graduated or finished the campaign.

Create a new column in Participants table named Date_Eligible as Datetime. This is set the first time when the Campaign_Eligibility state changes from NULL or 0 to 1.

Create a new column in Participants table named Date_Became_Ineligible as Datetime. This date is set when the Campaign_Eligibility state changes from NULL, 1, 2 to 0 for ineligible.

Create a new column in Participants table named Date_Campaign_Finished as Datetime. This date is set when the Campaign_Eligibility state changes from NULL, 0, 1, to 2.

Different States for the Campaign_Eligibility Column:

THIS IS WHERE I NEED SOME ASSISTANCE. I NEED HELP DETERMINING THE LOGIC OF THE TABLE BELOW.

Three columns here:

Initial Value New value Allowed?
Null 0 Yes
Null 1 Yes
Null 2 Yes
0 Null No
0 1 Yes*
0 2 Yes**
1 Null No
1 0 Yes
1 2 Yes
2 Null No
2 0 No
2 1 No

*This may happen but must be evaluated by campaign. It may require that no communications have been sent yet and/or a reset of enrollment date may need to occur.
**Only if a participant has received a prior communication and the final date has passed.

Here is the script that this needs to be added to. I am not the greatest at programming in thsi stuff so any help or guidance is apperciated:

/************************************************** ************************/
--
--
-- Returns a table of Campaign_Eligibility, Date_Eligible, Date_Became_Ineligible,
-- Date_Campaign_Finished
--
-- 0 for non-qualified
-- 1 for qualified, eligibe participant;
-- 2 for campaign complete
--
--
--
-- Transaction Mgt: None done by this procedure.
--
-- Returns: boolean 1 for qualified, 0 if not qualified, 2 for graduated.
/************************************************** ************************/
create function Eligibility_Test (
@Campaign_ID smallint,
@Person_ID int,
@Todays_Date datetime
)
returns @Eligibility_Info table
(Campaign_Eligibility smallint, Date_Eligible datetime, Date_Became_Ineligible datetime, Date_Campaign_Finished datetime)

AS
BEGIN

Declare @Campaign_Eligibility smallint

Select @Campaign_Eligibility = CASE
When pt.Mail_Date_Opt_In < DATEADD(dd, -180, @Todays_Date) Then 2
When (Select Count(*)
From Person p inner join participant pt on p.person_id = pt.person_id
inner join Address a on p.person_id = a.person_id
inner join participant_answers pa1 on p.person_id = pa1.person_id
inner join participant_answers pa2 on p.person_id = pa2.person_id
inner join participant_answers pa3 on p.person_id = pa3.person_id
inner join participant_answers pa4 on p.person_id = pa4.person_id
inner join participant_answers pa5 on p.person_id = pa5.person_id
inner join participant_answers pa6 on p.person_id = pa6.person_id
Where pt.campaign_id = @Campaign_ID
and p.person_id = @Person_ID
and LEN(First_Name) > 0 -- First Name Check
And LEN(Last_Name) > 0 -- Last Name Check
And valid_address = 1 -- Mailing Address Check
And CASE
WHEN dateadd(year, datediff (year, Date_Of_Birth, @Todays_Date), Date_Of_Birth) > @Todays_Date
THEN datediff (year, Date_Of_Birth, @Todays_Date) - 1
ELSE datediff (year, Date_Of_Birth, @Todays_Date)
END
>= 18
And Gender IN ('M', 'F') -- not accepting 'U' for BPSZ
And pa1.Answer_ID IN (1011, 1014, 1008) --Diovan, Diovan HCT, Lotrel
And ( pa2.Answer_ID IN (3042, 3043) -- Diovan
OR pa2.Answer_ID IN (3044, 3045) -- Diovan HCT
OR pa2.Answer_ID IN (3046, 3047)) -- Lotrel
And pa3.Answer_ID IN (3055, 3056, 3057, 3058, 3059, 3060) --ILOC/ELOC
And pa4.Answer_ID IN (3062, 3063, 3064, 3065, 3066, 3067, 3068)
And pa5.Answer_ID IN (3069, 3070, 3071, 3072, 3073, 3074, 3075)
And pa6.Answer_ID IN (3076, 3077, 3078, 3079, 3080, 3081, 3082)
and Comm_Track is not null
and Media_Status is not null
and Comm_Track != 'Online') = 1 Then 1 --Eligible

Else 0
End
From Person p inner join participant pt on p.person_id = pt.person_id
Where pt.campaign_id = @Campaign_ID
and p.person_id = @Person_ID

Insert @Eligibility_Info(Campaign_Eligibility) --initialize the temp table
Values (@Campaign_Eligibility)

--now set the dates
If (Select Campaign_Eligibility From @Eligibility_Info) = 0
Begin
Update @Eligibility_Info Set Date_Became_Ineligible = @Todays_Date
End
Else If (Select Campaign_Eligibility From @Eligibility_Info) = 1
Begin
Update @Eligibility_Info Set Date_Eligible = @Todays_Date
End
Else If (Select Campaign_Eligibility From @Eligibility_Info) = 2
Begin
Update @Eligibility_Info Set Date_Campaign_Finished = @Todays_Date
End

--Select Campaign_Eligibility, Date_Eligible, Date_Became_Ineligible, Date_Campaign_Finished from @Eligibility_Info
Return
end
/**** End of function Eligibility_Test ****/

--Select * from Eligibility_Test (65, 228,'10/5/05')

ANY HELP IS APPERCIATED!

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources