I have this table:
CREATE TABLE [dbo].[tbl_CustomerRegistaration]
(
[CustID] [int] NOT NULL,
[CustName] [varchar](50) NULL,
[RegistrationDate] [datetime] NULL,
[ServiceTrpe] [varchar](15) NULL,
[IsActive] [int] NULL
) ON [PRIMARY]
INSERT INTO [dbo].[tbl_CustomerRegistaration] ([CustID],[CustName], [RegistrationDate], [ServiceTrpe], [IsActive])
VALUES (1, 'Liam', '2021-05-02 00:00:00.000', 'Weekly' ,1)
INSERT INTO [dbo].[tbl_CustomerRegistaration] ([CustID],[CustName], [RegistrationDate], [ServiceTrpe], [IsActive])
VALUES (2, 'William', '2021-06-12 00:00:00.000', 'Weekly' ,1)
INSERT INTO [dbo].[tbl_CustomerRegistaration] ([CustID],[CustName], [RegistrationDate], [ServiceTrpe], [IsActive])
VALUES (3, 'Oliver', '2021-07-23 00:00:00.000', 'Weekly' ,1)
INSERT INTO [dbo].[tbl_CustomerRegistaration] ([CustID],[CustName], [RegistrationDate], [ServiceTrpe], [IsActive])
VALUES (4, 'Emma', '2021-08-01 00:00:00.000', 'Weekly' ,1)
INSERT INTO [dbo].[tbl_CustomerRegistaration] ([CustID],[CustName], [RegistrationDate], [ServiceTrpe], [IsActive])
VALUES (5, 'Amelia', '2021-08-03 00:00:00.000', 'Monthly' ,1)
My requirement is: every 7 days customer need to renew subscription. report will be like that in current date. searching by CustID =4
CustID|CustName|RegistrationDate|LastRenewalDate|NextRenewalDate|
-----------------------------------------------------------------
4|Emma |2021-08-01 |2021-08-21 |2021-08-28 |
-----------------------------------------------------------------
**LastRenewalDate** is not stored in DB I want to execute a report where I show two more column **LastRenewalDate** and **NextRenewalDate**. for example suppose today is '2021-08-21' and **RegistrationDate** date is '2021-08-01' , when i execute this query **LastRenewalDate** and **NextRenewalDate** will show accordingly **2021-08-21** and **2021-08-28**. because CustID registered as weekly (7 days) subscription. so every week CustID got a message. If I want to show this report today(2021-08-21) by CustID =4 then I will get this information.
|CustID|CustName|RegistrationDate|LastRenewalDate|NextRenewalDate|
-----------------------------------------------------------------
| 4|Emma |2021-08-01 |2021-08-21 |2021-08-28 |
-----------------------------------------------------------------
if tomorrow (2021-08-22) I execute this query then report will be
|CustID|CustName|RegistrationDate|LastRenewalDate|NextRenewalDate|
-----------------------------------------------------------------
| 4|Emma |2021-08-01 |2021-08-21 |2021-08-28 |
-----------------------------------------------------------------
