February 10, 2003 at 3:17 am
The system I have uses a yearly renewal date for membership. I have converted this to a week number in a calculated field in SQL 7.0.
So when the user selects week 5 he/she will get pre reminder letters for week 7 (2 weeks before that persons renewal). This same procedure happens when they are late ie week 52 for people 6 weeks late based on their renewal date.
How do I calculate in a stored procedure the correct week date.this is the code that doesn't work
SELECT (MM_Title + ' ' + MM_Surname) AS ContactName, MM_TradingAs , MM_Address1, MM_Address2 , MM_Address3, MM_Town, MM_County, MM_CountryCode, MM_PostCode,
SU_RenewalDate, SU_SubAmount, MM_MemberNo, MM_Type
FROM tblMemberDetails
INNER JOIN tblSubscriptionDetails
ON tblMemberDetails.MM_MemberNo = tblSubscriptionDetails.SU_MemberNo
INNER JOIN tblPaymentData
ON tblMemberDetails.MM_MemberNo = tblPaymentData.PD_MemberNo
WHERE SU_RenewalWeek = @WeekNo + 2
AND MM_Status IN('LM1') AND MM_StopNext = 'No' AND MM_Scheme = 'Standard'
AND SU_PayMethod = 'CH' AND PD_PaymentStatus = 'Pending' AND MM_GoneAway = 'No'
AND MM_MembershipType IN('AS','CM','FM','QE','SR')
ORDER BY MM_TradingAs
I'm passing the week number across but of course when its week 5 and they are renewals in week 53 this codes doesn't work.
Sorry its a long one and all help would be greatly appreciate
Scott
February 10, 2003 at 5:26 am
WHERE SU_RenewalWeek = (CASE WHEN @WeekNo > 50 THEN (@WeekNo-50) ELSE (@WeekNo + 2) END)
will cater for @WeekNo+2 for weeks 51+
Can u explain what you are trying to do for
'This same procedure happens when they are late ie week 52 for people 6 weeks late based on their renewal date.'
are you always checking for '6 weeks late'?
Far away is close at hand in the images of elsewhere.
Anon.
February 10, 2003 at 5:30 am
Thank you David.
Its because I'm running a letter schedule. We run letters at the same time for people who are pre-reminded of a renewal ie 2 weeks before their renewal week aswell as people who are 2 weeks late , 6 weeks late and lastly 10 weeks late
Hope that makes a little more sense
Thanks again
Scott
February 10, 2003 at 6:08 am
slightly long winded exercise but I;ve managed to work around using you code example.
SELECT @CHQPreReminder = COUNT(MM_MemberNo)
FROM tblMemberDetails
INNER JOIN tblSubscriptionDetails
ON tblMemberDetails.MM_MemberNo = tblSubscriptionDetails.SU_MemberNo
INNER JOIN tblPaymentData
ON tblMemberDetails.MM_MemberNo = tblPaymentData.PD_MemberNo
WHERE SU_RenewalWeek = CASE @WeekNo
WHEN 52 THEN 1
WHEN 53 THEN 2
ELSE @WeekNo + 2
END
AND MM_Status = 'LM1' AND MM_StopNext = 'No' AND MM_Scheme = 'Standard'
AND SU_PayMethod = 'CH' AND PD_PaymentStatus = 'Pending' AND MM_GoneAway = 'No'
AND MM_MembershipType IN('AS','CM','FM','QE','SR')
I really appreciate your time on this
Thanks
Scott
February 10, 2003 at 6:47 am
Great.
If you want to process the late renewals you can use
CASE WHEN (@WeekNo - 2) < 1 THEN (@WeekNo - 2 + 52) ELSE (@WeekNo - 2) END
and change the 2 to 6 & 10 accordingly
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply