|
--Who does not want to receive reminders?
select U.UserId, U.UserName, U.FirstName, U.LastName, U.Email
from DnnForge_SubscriptionTools_UserSetting ST
Inner Join Users U on ST.UserId = U.UserId
where SettingValue = 'False
--Who has received a reminder?
SELECT DISTINCT U.UserName, U.LastName, U.LastName as Fullname, Name, DateEnd as 'Expires on', AM.LastLoginDate as 'Last login', RM.Datesent as 'Reminder sent on'
FROM DnnForge_SubscriptionTools_Receipt UR
INNER JOIN USERS U ON UR.UserID = U.UserID
INNER JOIN aspnet_Users AU ON AU.UserName = U.Username
INNER JOIN aspnet_Membership AM ON AM.UserId = AU.UserId
INNER JOIN DnnForge_SubscriptionTools_Reminder RM on UR.ReceiptId = RM.ReceiptId
WHERE UR.DateEnd > GETDATE()
ORDER BY DateEnd
|