Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2010
    Posts
    20

    Unanswered: Working with dates and updates... Help, again?

    Hi!

    My goal is to define certain variables, such as "Allowance", "Benefit", "e/km" which are effective during a certain period of time...


    For example:
    The table columns are:
    -Date (date)
    -Allowance (int)
    -Benefit (int)
    -e/km (int)
    -InEffect (bool)


    The "Date" indicates the starting date when the inserted values are in effect. This part was easy... (If current date > "Date" then "InEffect" = True)
    I also want the table to keep only one row "InEffect" at a time. More specificly, the row that has the latest date.
    So, -->
    The query should update the "InEffect" to True for one row at a time that has:
    -The "Date" specified has passed
    -The latest passed "Date" of all the rows...


    To clarify:

    Date: Allowance: . . . . InEffect:
    1.1.2010 5 0
    1.6.2010 10 0
    19.12.2010 10 1
    1.1.2011 20 0

    The query should update the boolean value to true for the last date that took effect.

  2. #2
    Join Date
    Nov 2010
    Posts
    20
    Quote Originally Posted by Hamsori View Post


    To clarify:

    Date: Allowance: . . . . InEffect:
    1.1.2010 5 0
    1.6.2010 10 0
    19.12.2010 10 1
    1.1.2011 20 0

    Sorry for this mess. This was supposed to be a table that showed 3 columns (Date, Allowance, InEffect) and 4 rows...

  3. #3
    Join Date
    Nov 2010
    Posts
    20
    Quote Originally Posted by Hamsori View Post
    The "Date" indicates the starting date when the inserted values are in effect. This part was easy... (If current date > "Date" then "InEffect" = True)
    I also want the table to keep only one row "InEffect" at a time. More specificly, the row that has the latest date.

    ...Actually it it wasn't as easy as I thought. I used thw following code:

    UPDATE table_name
    SET InEffect = '1'
    WHERE (select CONVERT (varchar(10), Date, 104) from table_name) < (select CONVERT (varchar(10), getdate(), 104))

    This code runs OK if there is only one result. But as there are several dates that qualify for this, then there is an error.
    There cannot be more than one result if you use "<, >, >=, <= or =" in subquery....

  4. #4
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by Hamsori View Post
    Hi!
    The query should update the boolean value to true for the last date that took effect.
    Sounds like a good case for a GROUP BY w/ HAVING MAX(Date) < GETDATE() or something similar as a derived table that you join against to update only 1 row. Let me know if you need help with the syntax or if you have already solved this problem.

    Try to figure out what the select statement is to identify your data and then writing the update is a piece of pie.

  5. #5
    Join Date
    Nov 2010
    Posts
    20
    Hey! Thanks for the responses. I managed to do it and used somewhat a similar way Gagnon described...

    Thanks again... (more to come...)

Tags for this Thread

Posting Permissions

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