# Thread: Possible to UPDATE Multiple Fields Conditionally?

1. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213

## 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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Yes, use a DATE or DATETIME column instead of two INT columns and then the data arithmetic is easy!

-PatP

3. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213
Originally Posted by Pat Phelan
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Ok, so arbitrarily make them all happen on the 1st or the 10th, or whenever.

-PatP

5. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
213
Originally Posted by Pat Phelan
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