What are the challenges you face when working across database platforms? Take the survey
Options

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

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     |
    -----------------------------------------------------------------
Tagged:
Sign In or Register to comment.