Display Expiring records in datagrid

Question

User-1901014284 posted

Hi,

I have an MSSQL DB to store contract end dates for customers, I would like to be able to display the records which would be due to expire 30 days before the Contract_End_Date record that has been entered into the DB record but i would like the record to remain in the list until the Contract_End_Date date is reached. I am using ASP.NET C# but I have tried the below code in a stored procedure within MSSQL but while it does return some records it is not accurate enough as after checking some records which should be displayed are not. Please see below the code I have used:

"SELECT c.Client_Name AS 'Client Name', cs.crownfordServices AS 'Service', sl.serviceLevels AS 'Level', cc.Contract_Start_Date AS 'Start Date', cc.Contract_End_Date AS 'End Date' FROM Customer_Contracts cc
LEFT JOIN Customer c ON (cc.Client_Name = c.ID)
LEFT JOIN Service_Levels sl ON (cc.serviceLevels = sl.ID)
LEFT JOIN CrownfordServices cs ON (cc.CrownfordServices = cs.ID)
WHERE Contract_End_Date BETWEEN DATEADD(DAY,-30,GETDATE()) and GETDATE()"

Any assistance would be greatly appreciated.

Kind regards

Jonny

Answer 1

User-183374066 posted

i would like the record to remain in the list until the Contract_End_Date date is reached

You just need to compare that Contract_End_Date should be greater than or equal to today

SELECT c.Client_Name AS 'Client Name', cs.crownfordServices AS 'Service', sl.serviceLevels AS 'Level', cc.Contract_Start_Date AS 'Start Date', cc.Contract_End_Date AS 'End Date' FROM Customer_Contracts cc
LEFT JOIN Customer c ON (cc.Client_Name = c.ID)
LEFT JOIN Service_Levels sl ON (cc.serviceLevels = sl.ID)
LEFT JOIN CrownfordServices cs ON (cc.CrownfordServices = cs.ID)
WHERE Contract_End_Date >= GETDATE()

Answer 2

User-1901014284 posted

Hi Nasser,

Thank you for your reply, while this works I only require the dates that are due to expire within the next 30 days where as your solution will display all records in the DB.

As mentioned I require all dates within 30 days to be displayed up until the Contract End Date has passed. Once the Contract End Date has passed the record will be displayed in another view which will display only expired contracts.

Again, thank you for your answer and support it is greatly appreciated.

Kind regards

Jonny

Answer 3

User-1171043462 posted

Check this example

Hide GridView rows based on condition in ASP.Net

Answer 4

User475983607 posted

Simply subtract 30 (or 31) days from today.  GETDATE() return the current time so subtracting 31 days will allows include today.  Or you can cast the date to set the time to 12am.

SELECT c.Client_Name AS 'Client Name', cs.crownfordServices AS 'Service', sl.serviceLevels AS 'Level', cc.Contract_Start_Date AS 'Start Date', cc.Contract_End_Date AS 'End Date' FROM Customer_Contracts cc
LEFT JOIN Customer c ON (cc.Client_Name = c.ID)
LEFT JOIN Service_Levels sl ON (cc.serviceLevels = sl.ID)
LEFT JOIN CrownfordServices cs ON (cc.CrownfordServices = cs.ID)
WHERE Contract_End_Date >= DATEADD(d, -31, GETDATE())

Answer 5

User-1901014284 posted

Thank you mgebhard but the records being pulled still include Contact End Dates that are not due until 2019. is there a way to only display records between the 31 days from today to the contract end date?

Answer 6

User-183374066 posted

I only require the dates that are due to expire within the next 30 days

SELECT c.Client_Name AS 'Client Name', cs.crownfordServices AS 'Service', sl.serviceLevels AS 'Level', cc.Contract_Start_Date AS 'Start Date', cc.Contract_End_Date AS 'End Date' FROM Customer_Contracts cc
LEFT JOIN Customer c ON (cc.Client_Name = c.ID)
LEFT JOIN Service_Levels sl ON (cc.serviceLevels = sl.ID)
LEFT JOIN CrownfordServices cs ON (cc.CrownfordServices = cs.ID)
WHERE Contract_End_Date >= GETDATE() AND Contract_End_Date <= DATEADD(day, 30,  GETDATE())

Answer 7

User-1901014284 posted

Worked perfectly,

Thank you all for you help.

Jonny