Any ideas

I need for a database to give my users and indication that a renewal has been complete. Basically what happens is every year once a month a report is generated from sql of how many employees need their gaming license renewed the filter is based off of a field called final suit. I need to find a way to let them know through the database that an employee has been renewed. anyone got any ideas??My first idea (and I like this one the best) was to shake up a margarita...

Then I read your question

I would have a column in a table for Renewal Date. When they get renewed, update it with that date. Since the renewal is for 1 year anything that is not renewed would be earlier than today - 1 year

I still like my first idea|||How are you doing?? Nice to hear from you.
Well they have to be renewed every 2 years, there is a column in the table with the renewal date on it already, so your saying that should be their indication that they have been renewed already??|||USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myEmployee99(LastName varchar(30), FirstName varchar(30), RenewalDate datetime)
GO

INSERT INTO myEmployee99(LastName, FirstName, RenewalDate)
SELECT 'Johnson', 'Arnie' , '10/31/2001' UNION ALL
SELECT 'Kaiser' , 'Brett' , '10/31/2002' UNION ALL
SELECT 'Lynn' , 'Connie' , '10/31/2003' UNION ALL
SELECT 'More' , 'Desiree', '10/31/2004'
GO

SELECT 'Employees that Need to Renew: ' + LastName + ', ' + FirstName
+ ' --> ' + CONVERT(varchar(5), DATEDIFF(dd,RenewalDate,GetDate())-730) + ' Days Late'
FROM myEmployee99
WHERE DATEDIFF(dd,RenewalDate,GetDate())-730 > 0
GO

SET NOCOUNT ON
DROP TABLE myEmployee99
GO|||Was that what you were looking for?|||I dont think thats what I am looking for Brett but thank you anyways, I do appreciate it :)
You see I have a query that runs off the team members Final suit date. (by the way all the dates data types are DATETIME). I need something that tells the users as soon as they go in the database looking for whether a renewal has been completed or not. YOu see they get a report every month that tells them what renewals need to be pulled (files from filing cabinet) and renewed but I need something that tells them if they have already completed the renewal or not. Does that make sense??|||Is a renewal in your case a proces or is it enough to have it all on the 'final suit' field? How does the field 'final suit' work (when is it set the first time) and how does it change? If the 'final suit' can indicate when a license is renewed, why can it not tell if a license has been renewed in the past?|||If you post some DDL, sample data and the expected results you have (like the code I posted). I'm sure we can figure something out.

It's easier to see with samples...|||Hi there,
This all sounds extremely odd.
To me, it sounds as though you want to notify the customer as soon as the renewal happens. This can be done with a trigger.

You can create an update trigger that will use the SQL sendmail to send a email to the customer perhaps?

Even better - write a VB app for Outlook that will check the SQL table and send a mail to the customer if there renewal was done in the last 24 hours|||I use to have a drop down box that said yes or no to indicate if the renewal was complete or not??