I am attempting to retrieve records 7 days before the termination date. The code below doesn't seem to work.
CREATE VIEW vwRenewSubscription
AS
SELECT c1.id as userId, p1.id subscriptionId,
a1.id as advertisementPlanId,
c1.companyName, c1.rocNo,
a1.description, a1.fees,a1.duration
FROM subscription AS p1, subscription AS p2, corporate AS c1, advertisementplan AS a1
WHERE p1.id = p2.subscription_id_fk AND p1.id = p2.subscription_id_fk
AND p1.user_id_fk = c1.id
AND a1.id = p1.advertisementPlan_id_fk
AND date_format(now(), '%Y-%m-%d') < p1.terminationDate
AND date_format(now(), '%Y-%m-%d') > date_format(date_sub(p1.terminationDate, interval 7 day), '%Y-%m-%d');
Your help is kindly appreciated.
Thank You.