Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2009
    Posts
    90

    Unanswered: Update status by date

    Say i have this column that i want to update by date. I want to update each row by its effective until date time less than current date time and that the effective until date is the latest.

    Hope you can help me.
    Thanks!
    Last edited by rvr707; 06-28-11 at 23:51.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I don't understand what you want.

    Give us some sample data and the result that you want after the UPDATE. A good example can do wonders.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2009
    Posts
    90
    Code:
    EMP NAME             EMPLOYEE STATUS          EFFECTIVE UNTIL DATE
    ------------         -------------------          ----------------------
    JOHN                        REGULAR                        6/5/2009
    JAMES                      REGULAR                        4/29/2004
    ED                           REGULAR                        3/17/2008   
    JAMES                      REGULAR                        10/6/2010
    JAMES                      REGULAR                        7/15/2006
    PAUL                        REGULAR                        4/14/2009 
    PAUL                        REGULAR                        3/10/2006
    JOHN                        REGULAR                        5/4/2007
    I only want to update the employee status of the employee's latest effective date

    Code:
    EMP NAME             EMPLOYEE STATUS          EFFECTIVE UNTIL DATE
    ------------         -------------------          ----------------------
    JOHN                       IRREGULAR                      6/5/2009
    JAMES                      REGULAR                        4/29/2004
    ED                           IRREGULAR                     3/17/2008   
    JAMES                      IRREGULAR                     10/6/2010
    JAMES                      REGULAR                        7/15/2006
    PAUL                        IRREGULAR                        4/14/2009 
    PAUL                        REGULAR                        3/10/2006
    JOHN                        REGULAR                        5/4/2007

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try:
    Code:
    UPDATE U
    SET EMPLOYEE_STATUS = 'IRREGULAR'
    FROM MyTable as U
    	INNER JOIN (SELECT EMP_NAME, 
    			MAX(EFFECTIVE_UNTIL_DATE) as MaxDate
    			FROM MyTable) AS T On
    		U.EMP_NAME = T.EMP_NAME AND
    		U.EFFECTIVE_UNTIL_DATE = T.MaxDate
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Sep 2009
    Posts
    90
    THANX WIM!
    THAT SOLVED IT!

    But I have a new problem...


    Code:
    EMP NAME             EMPLOYEE STATUS          EFFECTIVE UNTIL DATE
    ------------         -------------------          ----------------------
    JOHN                       IRREGULAR                      6/5/2009
    JAMES                      REGULAR                        4/29/2004
    ED                           IRREGULAR                     3/17/2008   
    JAMES                      IRREGULAR                     10/6/2010
    JAMES                      REGULAR                        7/15/2006
    PAUL                        IRREGULAR                        4/14/2009 
    PAUL                        REGULAR                        3/10/2006
    JOHN                        REGULAR                        5/4/2007
    CARL                         REGULAR                        4/29/2002
    CARL                          REGULAR                       6/15/2005
    CARL                          REGULAR                       NULL

    NOTICE CARL'S RECORDS. HIS EMPLOYEE STATS DID NOT CHANGE BECAUSE HE HAS A NULL VALUE IN EFFECTIVE UNTIL DATE.
    HOW CAN I DO THAT?

    THANKS IN ADVANCE!

  6. #6
    Join Date
    Sep 2009
    Posts
    90
    Is this possible by just adding a condition in the inner join?

  7. #7
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Tip: Have a look at isnull and/or coalesce in books online.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  8. #8
    Join Date
    Jun 2011
    Posts
    6

    Use This Query it will solve ur problem

    UPDATE U
    SET EMPLOYEE_STATUS = 'IRREGULAR'
    FROM MyTable as U
    INNER JOIN (SELECT EMP_NAME,
    MAX(EFFECTIVE_UNTIL_DATE) as MaxDate
    FROM MyTable) AS T On
    U.EMP_NAME = T.EMP_NAME AND
    U.EFFECTIVE_UNTIL_DATE = ISNULL(T.MaxDate,U.EFFECTIVE_UNTIL_DATE)

  9. #9
    Join Date
    Sep 2009
    Posts
    90
    Yes i know coalesce and isnull, i just dont know how to use it in this kind of data result

  10. #10
    Join Date
    Jun 2011
    Posts
    6
    ISNULL EXAMPLE

    SELECT ISNULL(NULL, 1)
    --Returns 1
    SELECT ISNULL(NULL, NULL)
    --Returns NULL

    COALESCE EXAMPLE

    SELECT COALESCE(NULL, 1)
    --Returns 1

    SELECT COALESCE(NULL, 3, NULL, 1)
    --Returns 3

    I think now you can understand how these two functions are work.
    COALESCE provides more control than the ISNULL Function.

  11. #11
    Join Date
    Sep 2009
    Posts
    90
    Quote Originally Posted by greenx View Post
    UPDATE U
    SET EMPLOYEE_STATUS = 'IRREGULAR'
    FROM MyTable as U
    INNER JOIN (SELECT EMP_NAME,
    MAX(EFFECTIVE_UNTIL_DATE) as MaxDate
    FROM MyTable) AS T On
    U.EMP_NAME = T.EMP_NAME AND
    U.EFFECTIVE_UNTIL_DATE = ISNULL(T.MaxDate,U.EFFECTIVE_UNTIL_DATE)
    Its not this... It changed CARL's record to irregular. My goal is all his status will not change when there is one null....

  12. #12
    Join Date
    Sep 2009
    Posts
    90
    CAN I add GROUP BY in the INNER JOIN to compare if the record has a null effective until date and exclude it in updating?

  13. #13
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try:
    Code:
    UPDATE U
    SET EMPLOYEE_STATUS = 'IRREGULAR'
    FROM #MyTable as U
    	INNER JOIN (SELECT EMP_NAME, 
    			MAX(EFFECTIVE_UNTIL_DATE) as MaxDate
    			FROM #MyTable
    			GROUP BY EMP_NAME
    			HAVING SUM(CASE WHEN EFFECTIVE_UNTIL_DATE IS NULL THEN 1 ELSE 0 END) = 0
    			) AS T On
    		U.EMP_NAME = T.EMP_NAME AND
    		U.EFFECTIVE_UNTIL_DATE = T.MaxDate
    I forgot to include a GROUP BY EMP_NAME in my previous post.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  14. #14
    Join Date
    Sep 2009
    Posts
    90
    Thanks wim! You save my day again!

    Quote Originally Posted by wim View Post
    try:
    Code:
    update u
    set employee_status = 'irregular'
    from #mytable as u
    	inner join (select emp_name, 
    			max(effective_until_date) as maxdate
    			from #mytable
    			group by emp_name
    			having sum(case when effective_until_date is null then 1 else 0 end) = 0
    			) as t on
    		u.emp_name = t.emp_name and
    		u.effective_until_date = t.maxdate
    i forgot to include a group by emp_name in my previous post.

  15. #15
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by rvr707 View Post
    Thanks wim! You save my day again!
    Was it worth two beers then?

    It's not that I'm turning all greedy. I just want to improve my current piteous score of 4 Earned beers. I'm getting really desperate on this.
    In a moment of weakness, I was playing with the idea of creating an army of new accounts who's sole role would be to offer me Earned beers out of gratitude to solve problems I had made up myself.
    Right before creating the first account though, I found professional help for my astroturfing disorder. The sessions do help. And when I take them, the pills help too.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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