Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13

Thread: Task Reminder

  1. #1
    New Coder
    Join Date
    Mar 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Task Reminder

    Im trying to make reminder system
    The form has inputs for date of reminder and frequency
    frequency options are daily, weekly, monthly, quarterly, yearly
    Ive coded for daily reminder(using time check) but for other options I need help

    '==daily reminder --
    if FormatDateTime(reminderdate, 4) >FormatDateTime(Now, 4) then NewReminderDate = date else NewReminderDate = date+1

    any suggestion would be appreciated

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Never compare dates or times using strings! (And FORMATDATETIME converts a date/time into a string.)
    Try this:
    Code:
    If TimeValue(reminderdate) > Time() Then NewReminderDate = Date() Else NewReminderDate = Date() + 1
    TIMEVALUE is a function that extracts just the time-alone from a datetime value. TIME() is the current time.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Mar 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks
    Can you help me with weekly and quarterly reminder
    Im only storing the first reminder date and frequency of reminder, so confused how to get recurring date

    For monthly reminder I've done this

    comparedate = month(date)&"/"&day(reminderdate)&"/"&year(date)&" "&TimeValue(reminderdate)

    if comparedate>now then newdate = date else newdate = dateadd("m",1,date)

    NewReminderDate = month(newdate)&"/"&day(reminderdate)&"/"&year(newdate)&" "&TimeValue(reminderdate)

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Well, first of all, you are doing the monthly one wrong.

    You should do
    Code:
    compareDate = DateSerial( Year(Date()), Month(Date()), Day(reminderdate) ) + TimeValue(reminderdate)
    Never compare dates as strings. Always as DateTime values.

    But I wouldn't do it that way, at all, actually.

    I'd do it all in the database, in the SQL query.

    But I'm not sure which reminders you want to show when and how.

    Are you just looking for reminders for the current date and for times after the current time? Or what?

    Tell me what your display is supposed to contain.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Mar 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again

    My requirement is like this
    There is a page for user to store events
    The events are shown when user logs on to his account

    Eg.
    1. Check email (22 Dec 2011 10:00 AM) (daily)
    2. To call Client - Mr. Bobb (29 Dec 2011 11:30 AM) (one time)
    3. Take full backup (30 Dec 2011 4:30 PM) (monthly)

    The 1st is daily event so it should display the date '22 Dec 2011 10:00 AM' till 10:00 AM on 22nd Dec. After 10:00 AM I should display as 23 Dec 2011 10:00 AM. (I might put a margin of 15 min later)

    2nd is only one time so after 29 Dec it won't be displayed on reminder panel

    3rd is monthly so it should display 30 Dec 2011; after that date is passed it should be 30 Jan 2011

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Ahhh...got it! Much simpler than what I thought you were doing.

    So when an event is passed, you check to see if it is recurring and change its date to the next occurence, right?

    I still think this might be better done in the DB, but let me think about it a bit.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Forgot to ask: What kind of database are you using? MySQL? Access? SQL Server?

    In MySQL, it's really easy. Assuming that your table looks something like this:
    Code:
    CREATE TABLE events (
        userid INT references users(userid),
        eventid INT auto_increment PRIMARY KEY,
        eventDateTime DATETIME,
        recurrence ENUM (none,daily,weekly,monthly,quarterly,yearly)
    );
    Then you could just create a stored procedure that does this:
    Code:
    UPDATE events
    SET eventDateTime = DATE_ADD( eventDateTime, INTERVAL 1 DAY )
    WHERE eventDateTime < NOW() AND userid = @userid
    AND recurrence = 'daily';
    
    UPDATE events
    SET eventDateTime = DATE_ADD( eventDateTime, INTERVAL 1 WEEK )
    WHERE eventDateTime < NOW() AND userid = @userid
    AND recurrence = 'weekly';
    
    UPDATE events
    SET eventDateTime = DATE_ADD( eventDateTime, INTERVAL 1 MONTH )
    WHERE eventDateTime < NOW() AND userid = @userid
    AND recurrence = 'monthly';
    
    UPDATE events
    SET eventDateTime = DATE_ADD( eventDateTime, INTERVAL 1 QUARTER )
    WHERE eventDateTime < NOW() AND userid = @userid
    AND recurrence = 'quarterly';
    
    UPDATE events
    SET eventDateTime = DATE_ADD( eventDateTime, INTERVAL 1 YEAR )
    WHERE eventDateTime < NOW() AND userid = @userid
    AND recurrence = 'yearly';
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    For Access, something like this perhaps:
    Code:
    CREATE TABLE events (
        userid INT references users(userid),
        eventid INT autoincrement PRIMARY KEY,
        eventDateTime DATETIME,
        recurrence VARCHAR(20) 
    );
    (recurrence would then just be a string...or you could use an integer or or or...)

    And then execute these statements (can't be a stored procedure, but with ASP code could be all in one query):
    Code:
    UPDATE events
    SET eventDateTime = DATEADD( 'd', 1, eventDateTime)
    WHERE eventDateTime < NOW() AND userid = ###
    AND recurrence = 'daily';
    
    UPDATE events
    SET eventDateTime = DATEADD( 'ww', 1, eventDateTime)
    WHERE eventDateTime < NOW() AND userid = ###
    AND recurrence = 'weekly';
    
    UPDATE events
    SET eventDateTime = DATEADD( 'm', 1, eventDateTime)
    WHERE eventDateTime < NOW() AND userid = ###
    AND recurrence = 'monthly';
    
    UPDATE events
    SET eventDateTime = DATEADD( 'q', 1, eventDateTime)
    WHERE eventDateTime < NOW() AND userid = ###
    AND recurrence = 'quarterly';
    
    UPDATE events
    SET eventDateTime = DATEADD( 'yyyy', 1, eventDateTime)
    WHERE eventDateTime < NOW() AND userid = ###
    AND recurrence = 'yearly';
    where you could use VBScript REPLACE() to change ### to the current userid.

    If you ran this set of queries *before* you asked for the list of events for the given person, then you would automatically get the next occurrence.

    ********

    The code for SQL Server would be very similar to Access.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    What the heck. SQL Server version:
    Code:
    CREATE TABLE events (
        userid INT references users(userid),
        eventid INT IDENTITY PRIMARY KEY,
        eventDateTime DATETIME,
        recurrence VARCHAR(20) 
    )
    And then do all the updating and SELECTing with a single easy stored procedure:
    Code:
    CREATE PROCEDURE getUpdatedEvents( @userid INT )
    AS
    
    SET NOCOUNT ON
    
    UPDATE events
    SET eventDateTime = DATEADD( d, 1, eventDateTime)
    WHERE eventDateTime < getDate() AND userid = @userid
    AND recurrence = 'daily'
    
    UPDATE events
    SET eventDateTime = DATEADD( ww, 1, eventDateTime)
    WHERE eventDateTime < getDate() AND userid = @userid
    AND recurrence = 'weekly'
    
    UPDATE events
    SET eventDateTime = DATEADD( m, 1, eventDateTime)
    WHERE eventDateTime < getDate() AND userid = @userid
    AND recurrence = 'monthly'
    
    UPDATE events
    SET eventDateTime = DATEADD( q, 1, eventDateTime)
    WHERE eventDateTime < getDate() AND userid = @userid
    AND recurrence = 'quarterly'
    
    UPDATE events
    SET eventDateTime = DATEADD( yyyy, 1, eventDateTime)
    WHERE eventDateTime < getDate() AND userid = @userid
    AND recurrence = 'yearly'
    
    SET NOCOUNT OFF
    
    SELECT * FROM events
    WHERE userid = @userid AND eventDateTime >= getDate()
    ORDER BY eventDateTime;
    Then, from the ASP code, you just do:

    Code:
    Set RS = yourAlreadyOpenConnection.Execute("EXEC getUpdatedEvents " & currentUserId )
    Bang. You are done. No more messy logic in VBScript.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    In any of the DBs, it would be a good idea to add an index on the userid field in that table.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    New Coder
    Join Date
    Mar 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm using SQL server
    Actually I did the same thing earlier but I was confused here
    When should I run the SP

    Suppose I add an event on 24 Dec 2011 with event date 25 Dec 2011 10:00 AM with weekly recurrence. I store the date of adding event, event date and recurrence. If I logon to my account on 25th it will show me the event date 25 Dec 2011 10:00 AM. The next recurrence would be 1 Jan 2012. Now if I logon to my account on 10th Jan 2012 then how and when should I update the event date

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    If you use that stored procedure I showed there--and assuming it works (it *is* untested)--then you simply use it IN PLACE OF the SELECT that you are currently using.

    And it goes in an updates *ALL* events that need updating *BEFORE* it then does the SELECT to return the updated events to you.

    In the example you gave:

    -- on 12/24 you set and event for 12/25 @ 10:00 AM with weekly recurrence.
    -- If you ask to see your events any time prior to 12/25 @ 10:00 AM you will see that event
    -- If you ask to see your events at any time from 12/25 @ 10:00 AM through 1/1 @ 9:59 AM, you will see the occurrence for 1/1 @ 10:00 AM

    But I see now what you are asking! If you never logged in to look at events until 1/10, then my code would only update it from 12/25 to 1/1.

    Ugh. Okay, so my code has to simply "loop" until there are no more updates, doesn't it?

    I mean, we could go to the trouble of figuring out how many weeks have elapsed and add that number, but it's not worth it.

    Okay...back in a bit.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Okay...try this:
    Code:
    CREATE PROCEDURE updateEvents( @userid INT )
    AS
    BEGIN
    
    WHILE 1 > 0
      BEGIN
        UPDATE events
        SET eventDateTime = DATEADD( d, 1, eventDateTime)
        WHERE eventDateTime < getDate() AND userid = @userid
        AND recurrence = 'daily'
        IF @@ROWCOUNT = 0 BREAK
      END
    
    WHILE 1 > 0
      BEGIN
        UPDATE events
        SET eventDateTime = DATEADD( ww, 1, eventDateTime)
        WHERE eventDateTime < getDate() AND userid = @userid
        AND recurrence = 'weekly'
        IF @@ROWCOUNT = 0 BREAK
      END
    
    WHILE 1 > 0
      BEGIN
        UPDATE events
        SET eventDateTime = DATEADD( m, 1, eventDateTime)
        WHERE eventDateTime < getDate() AND userid = @userid
        AND recurrence = 'monthly'
        IF @@ROWCOUNT = 0 BREAK
      END
    
    WHILE 1 > 0
      BEGIN
        UPDATE events
        SET eventDateTime = DATEADD( q, 1, eventDateTime)
        WHERE eventDateTime < getDate() AND userid = @userid
        AND recurrence = 'quarterly'
        IF @@ROWCOUNT = 0 BREAK
      END
    
    WHILE 1 > 0
      BEGIN
        UPDATE events
        SET eventDateTime = DATEADD( yyyy, 1, eventDateTime)
        WHERE eventDateTime < getDate() AND userid = @userid
        AND recurrence = 'yearly'
        IF @@ROWCOUNT = 0 BREAK
      END
    
    END
    
    
    CREATE PROCEDURE getUpdatedEvents( @userid INT )
    AS
    BEGIN
    
    SET NOCOUNT ON
    EXEC updateEvents( @userid )
    SET NOCOUNT OFF
    
    SELECT * FROM events
    WHERE userid = @userid AND eventDateTime >= getDate()
    ORDER BY eventDateTime;
    
    END
    See that? If you now don't check your events until 1/10, the WHILE loop there will keep adding 1 week to that 12/25 event until the next occurrence of the event is after the current day and time, so on 1/10 you would see it as an event for 1/15 @ 10:00 AM.

    Does that work for you, now?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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