| Author |
 Topic  |
|
|
Topaz
Posting Yak Master
United Kingdom
199 Posts |
 Posted - 02/28/2013 : 05:43:28
|
I'm using a SQL table for email marketing purposes and amongst other fields has these 5 below:
Open, click1, click2, click3 and click4.
The defaults values of these fields are set to '0'. Everytime somebody opens or clicks an email the email sending software incrementally updates the relative field by +1.
At the moment after each campaign I reset all the open and click fields to '0'. This is a loss of data and I can't do much campaign analysis.
I'd like to know if it's possible to copy the field values to another set of fields whilst adding the values together? New fields:
masteropen, masterclick1, masterclick2, masterclick3 and master click4.
Eg.
Open = '5'
Click1 = '3'
Currently
Masteropen = '1'
Masterclick1 = '3'
After the update
Masteropen = '6'
Masterclick1 = '6'
I just need pointing in the right direction.
JT |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 02/28/2013 : 05:59:21
|
sounds like a trigger to me to do automatic summing of values
is this what you're after?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
Topaz
Posting Yak Master
United Kingdom
199 Posts |
Posted - 02/28/2013 : 07:11:47
|
Visakh16, this sounds like the perfect solution. Not sure how to do this though?
JT |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 02/28/2013 : 07:14:04
|
campaign analysis?
Give each row a campaign-no so you have no loss of data at all and you can aggregate everything every time you want.
Too old to Rock'n'Roll too young to die. |
|
|
|
Topaz
Posting Yak Master
United Kingdom
199 Posts |
Posted - 02/28/2013 : 07:19:49
|
quote:
Originally posted by webfred
campaign analysis?
Give each row a campaign-no so you have no loss of data at all and you can aggregate everything every time you want.
Too old to Rock'n'Roll too young to die.
I appreciate the suggestion but I don't think this is the best solution for us. I like the sounds of a 'trigger with automatic summing'.
JT |
|
|
|
James K
Flowing Fount of Yak Knowledge
1486 Posts |
Posted - 02/28/2013 : 07:46:57
|
I like WebFred's suggestion as well. In addition to what he mentioned about the ability to aggregate data at will, if you do the automatic summation, you would have lost the details on which campaign(s) contributed to the totals in each of the summed columns.
Regardless, if you have determined that you want to use the automatic summation, one possibility would be to modify the code where you do the resetting of the click fields to '0' and do the summation immediately before that. So you would have two lines of code instead of one as in:UPDATE YourTable SET
masterOpen = ISNULL(masterOpen,0) + open,
masterClick1 = ISNULL(masterClick1,0) + click1 |
|
|
|
Topaz
Posting Yak Master
United Kingdom
199 Posts |
Posted - 02/28/2013 : 10:27:28
|
Hi James, thanks for the solution. The query sort of works.
I can get MasterOpen to add together open. It doesn't set open back to zero after the query as ran??
JT |
|
|
|
James K
Flowing Fount of Yak Knowledge
1486 Posts |
Posted - 02/28/2013 : 11:06:15
|
You are right - the UPDATE only updates the masterXXXX columns. If you want to do the reset as well, add that as well into the query:UPDATE YourTable SET
masterOpen = ISNULL(masterOpen,0) + open,
masterClick1 = ISNULL(masterClick1,0) + click1,
open = 0,
click1 = 0 |
|
|
| |
Topic |
|
Complex updating of fields?
