How do I get current weekly date according to my Registration date

Transact-SQL
1

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     |
-----------------------------------------------------------------
2 
SELECT R.CustID, R.CustName, R.RegistrationDate, R.ServiceType, R.IsActive
    ,CASE
        WHEN X.LastRenewalDate > R.RegistrationDate
        THEN X.LastRenewalDate
    END AS LastRenewalDate
    ,CASE
        WHEN ServiceType = 'Weekly'
        THEN DATEADD(week, 1, X.LastRenewalDate)
        WHEN ServiceType = 'Monthly'
        THEN DATEADD(month, 1, X.LastRenewalDate)
    END AS NextRenewalDate  
FROM dbo.tbl_CustomerRegistaration R
    CROSS APPLY
    (
        VALUES
        (
            CASE
                WHEN R.ServiceType = 'Weekly'
                THEN DATEADD(day, -1, DATEADD(week, DATEDIFF(week, R.RegistrationDate, CURRENT_TIMESTAMP + 1), R.RegistrationDate))
                WHEN R.ServiceType = 'Monthly'
                THEN DATEADD(day, -1, DATEADD(month, DATEDIFF(month, R.RegistrationDate, CURRENT_TIMESTAMP + 1), R.RegistrationDate))
            END
        )
    ) X (LastRenewalDate);
2 Likes
3

Dear @Ifor thank you for your kind support. yes its working. One more thing LastRenewalDate is always on or before current date. And nextrenewal date should be next in next 7 days for weekly plan.

Monthly plan also in same way to show in report

Thanks in advance.