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
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.
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));
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.
ALTER TRIGGER [dbo].[RenewalUpdate]
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