Results 1 to 10 of 10

Thread: Any ideas

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

    Unanswered: 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??

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

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

    Hi Brett:):)

    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??

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Was that what you were looking for?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

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

    Well No

    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??

  7. #7
    Join Date
    Feb 2004
    Posts
    492
    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?

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Oct 2004
    Location
    Durban - South Africa
    Posts
    13
    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
    Last edited by TrevorW; 11-02-04 at 10:33.
    TrevorW
    If at first you don't succeed, call it version 1.0

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

    dropdown

    I use to have a drop down box that said yes or no to indicate if the renewal was complete or not??

Posting Permissions

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