locked
Display Expiring records in datagrid RRS feed

  • 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

    Tuesday, August 28, 2018 8:47 AM

Answers

  • 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())

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 28, 2018 11:47 AM

All replies

  • 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()

    Tuesday, August 28, 2018 10:34 AM
  • 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

    Tuesday, August 28, 2018 10:42 AM
  • User-1171043462 posted

    Check this example

    Hide GridView rows based on condition in ASP.Net

    Tuesday, August 28, 2018 11:38 AM
  • 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())

    Tuesday, August 28, 2018 11:42 AM
  • 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?

    Tuesday, August 28, 2018 11:47 AM
  • 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())

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 28, 2018 11:47 AM
  • User-1901014284 posted

    Worked perfectly,

    Thank you all for you help.

    Jonny

    Wednesday, August 29, 2018 8:32 AM