Results 1 to 12 of 12

Thread: Renewal Dates

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Question Unanswered: Renewal Dates

    Hi all

    I have two tables one has Fees the other has Renewals, and the user is requesting that everytime someone renews, the renewal date from the Renewas table need to change. for instance vendors have to renew every year and at the end of the month. So when the date on the fees change then the renewals table need to pick that up and change to the next year, so the user will know when the next renewal is done when he generates a report. Does that make sense??

    right now I'm using this in the properties of my text boxes. Istn there a better way of doing this? Thank you hope everyone had a great weekend

    Code:
    =DateSerial(Year([LicenseExpireDate])+5,Month([LicenseExpireDate]),Day([LicenseExpireDate]))

  2. #2
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    if you want to do it at the backend use triggers.

  3. #3
    Join Date
    Jul 2007
    Posts
    96
    From were I stand there are a couple of scenarios here:

    - You misexplained what your business problem and requirements were; or
    - You misaddressed the problem; or
    - I totally suck and I didn't understand that much about your particular issue.

    However, from what I understood what you need is a database design that takes your requirements into account in an effective way. You set up a fee and that fee has a reccuring period of X. When you make that relation you can then make another relation to link the recurring and the fee with a person / company / client. After that, all you need is a recurring mechanism (automatic or not) that checks payments that issues invoices, therefore allowing you to check due dates, values etc.

    On top of this you should have an order history to know who bought what, when and when does that client need to be charged again.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    year plus 5?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    when you pay the fee, the account gets renewed for next 5 years

  6. #6
    Join Date
    Jul 2007
    Posts
    96
    I seriously think you have an overall design problem. For instance your are calculating the next recurring date on the user interface. If that's not bad enough, your calculation is incorrect due to the fact that dateserial is not an effective date management function. If you add 5 years to the date 2008-02-29 you will get the date 2013-02-29, which is an invalid date.

    Your should do all that in your business logic or in your back end, never in the presentation layer.

    If you want to prove the invalid date concept try the following Script in SQL Server:

    DECLARE @year as SmallInt
    , @month as TinyInt
    , @day as TinyInt;

    SELECT @year = 2008, @month = 2, @day = 29;

    SELECT CAST((@year + 5) AS Char(4)) + '-' + CAST(@month AS VarChar(2)) + '-' + CAST(@day AS VarChar(2));

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Diabolic
    If you add 5 years to the date 2008-02-29 you will get the date 2013-02-29, which is an invalid date.
    yes it is but no you won't

    try it yourself:
    Code:
    select dateadd(yy,5,cast('2008-02-29' as datetime)) as d
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    thank you for the reply, I would like to correct the design issue. I'm trying to find out the best way to go about calcuating the renewal date so the user will know when to send the letters to the vendor so they can be renewed.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the best way is to use date arithmetic, i.e. operate on dates as dates using date functions like DATEADD()

    if you operate on dates as strings, as diabolic showed, you may get incorrect results like 2013-08-29
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    ok thank you guys I appreciate it. I'll keep working on it

  11. #11
    Join Date
    Jul 2007
    Posts
    96
    Quote Originally Posted by r937
    yes it is but no you won't

    try it yourself:
    Code:
    select dateadd(yy,5,cast('2008-02-29' as datetime)) as d
    Rudy, I ment if you use DateSerial, not DateAdd. Exactly because you don't get 2013-2-29 with DateAdd was one of the reasons why I said it should be done in the business logic or backend.

    I'm pretty sure that DateSerial doesn't do any fancy date validation, but I can try it tomorrow when I'm at work

  12. #12
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    I dont this is working either

    Code:
    ALTER TRIGGER [dbo].[RenewalUpdate]
    ON [dbo].[VendorFees]
    AFTER INSERT 
    AS 
    BEGIN
    UPDATE dbo.VendorLicenseRenewals_Tbl SET
    LicenseExpireDate = i.Date
    FROM VendorLicenseRenewals_Tbl v
    INNER JOIN INSERTED i ON i.VendorLicNumber = v.VendorLicNumber
    WHERE DATEDIFF(dd,[LicenseExpireDate],GetDate())-730 > 0
    END

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •