I ended up figuring it out with another student. This is what we came up with. Also, the teacher said this works so I'm content.
I understand how useful this website is so I will post the solution I found and also the solution my instructor came up with to help anyone in the future.
My solution:
First, I created the view:
GO
CREATE VIEW vwRenewal
AS
select m.memberid, m.FirstName, m.LastName, m.StartDate, m.RenewalID, r.RenewalPrice, pc.CardID
from Members m
inner join
Renewal r
on m.RenewalID = r.RenewalID
inner join
PaymentCard pc
on pc.MemberID = m.MemberID
WHERE m.CurrentFlag <> 0 AND m.RenewalID = 4
AND DATEPART(day, startdate) = DATEPART(day, getdate())
UNION ALL
select m.memberid, m.FirstName, m.LastName, m.StartDate, m.RenewalID, r.RenewalPrice, pc.CardID
from Members m
inner join
Renewal r
on m.RenewalID = r.RenewalID
inner join
PaymentCard pc
on pc.MemberID = m.MemberID
WHERE m.CurrentFlag <> 0 AND m.RenewalID = 3
AND DATEPART(day, startdate) = DATEPART(day, getdate())
AND DATEPART(month, GETDATE()) IN
((select (datepart(month, m.startdate) + 3)),
(select (datepart(month, m.startdate) + 6)),
(select (datepart(month, m.startdate) + 9)))
UNION ALL
select m.memberid, m.FirstName, m.LastName, m.StartDate, m.RenewalID, r.RenewalPrice, pc.CardID
from Members m
inner join
Renewal r
on m.RenewalID = r.RenewalID
inner join
PaymentCard pc
on pc.MemberID = m.MemberID
WHERE m.CurrentFlag <> 0 AND m.RenewalID = 2
AND DATEPART(day, startdate) = DATEPART(day, getdate())
AND DATEPART(month, startdate) = DATEPART(month, getdate())
UNION
ALL
select m.memberid, m.FirstName, m.LastName, m.StartDate, m.RenewalID, r.RenewalPrice, pc.CardID
from Members m
inner join
Renewal r
on m.RenewalID = r.RenewalID
inner join
PaymentCard pc
on pc.MemberID = m.MemberID
WHERE m.CurrentFlag <> 0 AND m.RenewalID = 1
AND GETDATE() IN
((SELECT dateadd(month, 24, m.startdate)),
(select dateadd(month, 48, m.startdate)),
(select dateadd(month, 72, m.startdate)))
GO
Then, the stored procedure:
CREATE PROCEDURE sp_Renewal
AS BEGIN
IF EXISTS (select * from vwRenewal) BEGIN
INSERT INTO Transactions (CardID, TransactionDate, Charge, Result)
VALUES ( (select CardID
from vwRenewal),
getdate(),
(select RenewalPrice
from vwRenewal),
'Pending')
END SELECT * FROM Transactions WHERE Result = 'Pending'
END
Instructor solution:
-- Determine the member's next charge date based on the last time they were charged.
DECLARE @RETURNDATE DATE -- Return value
DECLARE @SUBLEVEL INT -- Member's subscription level
DECLARE @MEMBERDAYS INT -- Days in member subscription period
DECLARE @JOINDATE DATE -- Date member joined
DECLARE @LASTCHARGE DATE -- Last charge to member account
-- Get subscription level and days in subscription period.
SELECT @SUBLEVEL = m.SubscriptionLevel, @MEMBERDAYS = s.DaysPerPeriod, @JOINDATE = m.JoinDate
FROM Members m
INNER JOIN SubscriptionLevels s
ON s.SubLevelID = m.SubscriptionLevel
WHERE MemberID = @MemberID
-- If the member's subscription level is not free...
IF @SUBLEVEL NOT IN (SELECT SubLevelID FROM SubscriptionLevels WHERE RenewalAmt > 0)
BEGIN
-- Get the most recent account charge date for the member.
-- There should always be a charge date, even for new members.
SELECT TOP 1 @LASTCHARGE = ChargeDate
FROM AccountCharges
WHERE MemberID = @MemberID
ORDER BY ChargeDate DESC
-- If there is a charge date, add the number of days in the membership period
-- to get the new date. Otherwise, add it to the join date.
IF @LASTCHARGE IS NOT NULL
SET @RETURNDATE = DATEADD(DAY, @MEMBERDAYS, @LASTCHARGE)
ELSE
SET @RETURNDATE = DATEADD(DAY, @MEMBERDAYS, @JOINDATE) -- Just in case there's no previous charge.
END
ELSE
-- If the member has a free plan, just add one year to today so the member is never charged.
SET @RETURNDATE = DATEADD(YEAR, 1, GETDATE())
RETURN @RETURNDATE
END
GO
Note: I only post this to help people in the future in some way who may encounter a similar situation as me or whatever they need it for..
Hope this helps. Enjoy.
Like any code, this is only a couple ways to accomplish this task. There are many other ways I'm sure..
