Full Version: Better Than A Complex Switch Function
redphoneconsulting
Howdy.

I'm trying to assign list records a segment ID that is based on some math. I'm getting an error that the switch function is too complex. Is there a better way to do this?

This should work in theory of receiving a mail code from the user, then attaching a segment field to each record based on their donation performance.

ie.

ContactKey Segment
23423 NGH1101A
32432 NGH1101E
etc... etc...

CODE
SELECT Donation_Extremes.ContactKey, [MailingCode] & Switch(

[HPC]>99 And DateDiff('m',[MRC],Date())<13 And [Contacts_OfflineOnlyDonors].[ContactKey]>0,"A",
[HPC]>99 And DateDiff('m',[MRC],Date())>12 and Contacts_OfflineOnlyDonors.ContactKey>0,"B",
DateDiff('m',[MRC],Date())<19 And [HPC]>49 And [HPC]<100 and Contacts_OfflineOnlyDonors.ContactKey>0,"C",
DateDiff('m',[MRC],Date())<13 And [HPC]>24 And [HPC]<50 and Contacts_OfflineOnlyDonors.ContactKey>0,"D",
DateDiff('m',[MRC],Date())<10 And [HPC]>9 And [HPC]<25 and Contacts_OfflineOnlyDonors.ContactKey>0,"E",

[HPC]>99 And DateDiff('m',[MRC],Date())<13 And [Contacts_MixedChannelDonors].[ContactKey]>0,"H",
[HPC]>99 And DateDiff('m',[MRC],Date())>12 and Contacts_MixedChannelDonors.ContactKey>0,"J",
DateDiff('m',[MRC],Date())<19 And [HPC]>49 And [HPC]<100 and Contacts_MixedChannelDonors.ContactKey>0,"K",
DateDiff('m',[MRC],Date())<13 And [HPC]>24 And [HPC]<50 and Contacts_MixedChannelDonors.ContactKey>0,"L",
DateDiff('m',[MRC],Date())<10 And [HPC]>9 And [HPC]<25 and Contacts_MixedChannelDonors.ContactKey>0,"M",

[HPC]>99 And DateDiff('m',[MRC],Date())<13 And [Contacts_OnlineOnlyDonors].[ContactKey]>0,"V",
[HPC]>99 And DateDiff('m',[MRC],Date())>12 and Contacts_OnlineOnlyDonors.ContactKey>0,"W",
DateDiff('m',[MRC],Date())<19 And [HPC]>49 And [HPC]<100 and Contacts_OnlineOnlyDonors.ContactKey>0,"X",
DateDiff('m',[MRC],Date())<13 And [HPC]>24 And [HPC]<50 and Contacts_OnlineOnlyDonors.ContactKey>0,"Y",
DateDiff('m',[MRC],Date())<10 And [HPC]>9 And [HPC]<25 and Contacts_OnlineOnlyDonors.ContactKey>0,"Z"

) AS Segment, Donation_Extremes.HPC, Donation_Extremes.MRC, Contacts_OnlineOnlyDonors.ContactKey, Contacts_MixedChannelDonors.ContactKey, Contacts_OfflineOnlyDonors.ContactKey
FROM (((Donation_Inception INNER JOIN Donation_Extremes ON Donation_Inception.ContactKey = Donation_Extremes.ContactKey) LEFT JOIN Contacts_OfflineOnlyDonors ON Donation_Extremes.ContactKey = Contacts_OfflineOnlyDonors.ContactKey) LEFT JOIN Contacts_MixedChannelDonors ON Donation_Extremes.ContactKey = Contacts_MixedChannelDonors.ContactKey) LEFT JOIN Contacts_OnlineOnlyDonors ON Donation_Extremes.ContactKey = Contacts_OnlineOnlyDonors.ContactKey;
BananaRepublic
1) Sometimes it's the case that "Too Complex" error is actually a consequence of bad syntax - have you tried stripping away one term by one until it works and re-add it back?

2) I have to admit the criteria is not that entirely clear to me and I really think we need to see the source data (at least a bogus representation) and the intended output) - we may be able to suggest a SQL statement in place of Switch().
John Spencer
I would use a public function to do something this complex.

CODE
Public Function fSegment(iHPC, dteMRC, iContactKey)
Dim iMonths as Long; iMonths = -99

IF isDate(dteMRC) then
iMonths = DateDiff("m",dteMRC,Date())
End if

IF iContactKey <= 0  or Imonths = -99 Then
'Return null or empty string
fSegment = Null
else
If iHPC > 99 and imonth < 13 Then
fSegment = "A"
ElseIf iHPC >99 and iMonth > 12
fSegment = "B"
ElseIf iHPC >49 and iHPC <100 and iMonth < 13
fSegment = "C"
ElseIf iHPC >24 and iHPC < 50 and iMonth > 12
fSegment = "D"
ElseIf iHPC >9 and iHPC < 25 and iMonth < 10
fSegment = "E"
...

End IF
End IF

End function

redphoneconsulting
The error is just coming from the length of the switch. If removing segment options, it works again. If I run each set of segments by themselves, it also works fine.

The source data is a queries that gives the donors donation behavior.

HPC = highest contribution
MRC = most recent contribution

and then it also queries their donation activity, such as whether or not they give only online, only through mail, or through both.

This query assigns them all codes based on their giving level and their giving activity, then the solicitation package is changed based on the different giving patterns.

The source data is in four tables:

DonationExtremes
ContactKey HPC MRC

OnlineOnlyDonors
ContactKey

OfflineOnlyDonors
ContactKey

MixedMethodDonors
ContactKey

The sample output is then:

ContactKey Segment
23423 NGH123A
34454 NGH123D
65464 NGH123Z

The segment is made up of the mail code supplied by the user ("NGH123" in our example) and the segment assigned by the query.

redphoneconsulting
John,

Thanks for that. I think that is the right path to be on. My VBA usage is pretty weak. How do I call that function in my SQL query?

is it just something like

SELECT Donation_Extremes.ContactKey, [MailingCode] & fSegment(HPC, MRC, ContactKey) as MailingSegment

?

Warm Regards,
redphoneconsulting
Hmm...

Well I've hit a snag, and I can't find the error message.

My query looks like this:

CODE
SELECT Donation_Extremes.ContactKey AS ContactKey, [MailingCode] &

fCustomSegment(
Donation_Extremes.[HPC],
Donation_Extremes.[MRC],
Donation_Extremes.[ContactKey],
Contacts_OnlineOnlyDonors.ContactKey,
Contacts_OfflineOnlyDonors.ContactKey,
Contacts_MixedChannelDonors.ContactKey
) AS Segment,

Donation_Extremes.HPC,
Donation_Extremes.MRC,
Contacts_OnlineOnlyDonors.ContactKey As OnlineKey,
Contacts_MixedChannelDonors.ContactKey As MixedKey,
Contacts_OfflineOnlyDonors.ContactKey AS OfflineKey

FROM (((Donation_Inception INNER JOIN Donation_Extremes ON Donation_Inception.ContactKey = Donation_Extremes.ContactKey) LEFT JOIN Contacts_OfflineOnlyDonors ON Donation_Extremes.ContactKey = Contacts_OfflineOnlyDonors.ContactKey) LEFT JOIN Contacts_MixedChannelDonors ON Donation_Extremes.ContactKey = Contacts_MixedChannelDonors.ContactKey) LEFT JOIN Contacts_OnlineOnlyDonors ON Donation_Extremes.ContactKey = Contacts_OnlineOnlyDonors.ContactKey;

And my function looks like this:

CODE
Public Function fCustomSegment(iHPC, dteMRC, iExtremeKey, iOnlineKey, iOfflineKey, iMixedKey)
Dim iMonths As Long
iMonths = -99

If IsDate(dteMRC) Then
iMonths = DateDiff("m", dteMRC, Date)
End If

If iExtremeKey <= 0 Or iMonths = -99 Then
'Return null or empty string
fSegment = "pre-else statement fail"
Else
If iHPC > 99 And iMonth < 13 And iOfflineKey > 0 Then
fSegment = "A"
ElseIf iHPC > 99 And iMonth > 12 And iOfflineKey > 0 Then
fSegment = "B"
ElseIf iHPC > 49 And iHPC < 100 And iMonth < 13 And iOfflineKey > 0 Then
fSegment = "C"
ElseIf iHPC > 24 And iHPC < 50 And iMonth > 12 And iOfflineKey > 0 Then
fSegment = "D"
ElseIf iHPC > 9 And iHPC < 25 And iMonth < 10 And iOfflineKey > 0 Then
fSegment = "E"
Else
fSegment = "-NoSegment"

End If
End If

End Function

But my output isn't returning a segment value of any type. It just looks like this:

ContactKey Segment HPC MRC OnlineKey MixedKey OfflineKey
1 MailCode 35 12/4/2010 1
4 MailCode 75 11/8/2010 4
7 MailCode 25 12/1/2010 7

redphoneconsulting
When I renamed the function, I forgot to rename the returned values. Dumb...

Thanks for the help, it's now working correctly.