Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Unanswered: Possible to UPDATE Multiple Fields Conditionally?

    I have a am doing some date calcs () . The situation is that I have a Move date (date a customer moved to a new home). I want to calculate their sales for the following 0-3 months after the move (month 0 being the move month). I have the month and year of the move (MthStart, YrStart), and I am adding 3 to MthStart to get the MthEnd of that 0-3 month period. I will then find sales BETWEEN YrStart&MthStart AND YrEnd&MthEnd (there is a YrMth field in the sales table)

    Of course, for MthStarts 10, 11, and 12, the ends are 13, 14, and 15. So for these, I need to subtract 12, and increment the YrEnd by 1.

    I am wondering if there is a way to update both the MthEnd and YrEnd fields at one time instead of separate SETs (or maybe I am just thinking about this the hard way to begin with). Is there a way to update both in a single CASE statement like WHEN MthEnd> 12 THEN MthEnd-12 AND YrEnd+1?

    Code:
    CREATE TABLE #myTable (
    YrStart INT,
    MthStart INT,
    YrEnd INT,
    MthEnd INT);
    
    INSERT INTO #myTable
    VALUES (2012,1,2012,4),
    (2012,5,2012,8),
    (2012,11,2012,14),
    (2012,12,2012,15);
    Currently, this is how I am doing this:

    Code:
    UPDATE #myTable
        SET MthEnd= 
          CASE 
            WHEN MthEnd> 12 THEN MthEnd-12
            ELSE MthEnd
          END
    
        ,YrEnd= 
          CASE 
            WHEN MthStart >= 10 THEN YrEnd+1
            ELSE YrEnd
          END
    
    select * from #myTable
    drop table #myTable
    Last edited by clawlan; 11-14-13 at 16:03.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, use a DATE or DATETIME column instead of two INT columns and then the data arithmetic is easy!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    Yes, use a DATE or DATETIME column instead of two INT columns and then the data arithmetic is easy!

    -PatP
    The problem with this is that i only have the month and year of the move, not the full date. Well technically, the moves are listed always as the first of the month (5/1/2013 for example), but i would want my sales calc to be from 5/1/2013 to 8/31/2013.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, so arbitrarily make them all happen on the 1st or the 10th, or whenever.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    Ok, so arbitrarily make them all happen on the 1st or the 10th, or whenever.

    -PatP
    Ok, so i have every one starting on the 1st of the month. How to I calc 3 months from then, to the end of that 3rd month?

    Example: MoveDt = 1/1/2013
    Sales data needed from 1/1/2013 thru 4/30/2013

    EDIT:
    OK, I think I got it. I was able to condense all that above work to some simple date calcs (thanks Pat). The final report actually breaks the movers' buying habits into 2 buckets: 0 to 3 months after the move, and 4 to 6 months:

    Code:
      ,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,lastmovedt)+4,0)) as [0to3End] --end of the 3rd month 
      ,DATEADD(mm,4,lastmovedt) as [4to6Start] --start of the 4th month
      ,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,lastmovedt)+7,0)) as [4to6End] --end of the 6th month
    Last edited by clawlan; 11-14-13 at 16:54.

Posting Permissions

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