question

imneaz avatar image
imneaz asked

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 |

-----------------------------------------------------------------

sql queryquery-resultsprogrammingsql2008
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Continuing the same example of today being 2021-08-21, what should the resulting rows for Oliver and Amelia look like?

0 Likes 0 ·

1 Answer

·
anthony.green avatar image
anthony.green answered

You would be best to create yourself a calendar table, the one I use is this https://www.sqlservercentral.com/scripts/date-calendar

Then you can join and do the necessary date math functions based on various attributes of the dates in question

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.