Author |
Topic |
Topaz
Posting Yak Master
199 Posts |
Posted - 2013-02-28 : 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 updateMasteropen = '6'Masterclick1 = '6'I just need pointing in the right direction.JT |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-28 : 05:59:21
|
sounds like a trigger to me to do automatic summing of valuesis this what you're after?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Topaz
Posting Yak Master
199 Posts |
Posted - 2013-02-28 : 07:11:47
|
Visakh16, this sounds like the perfect solution. Not sure how to do this though?JT |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-02-28 : 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
199 Posts |
Posted - 2013-02-28 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-28 : 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
199 Posts |
Posted - 2013-02-28 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-28 : 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 |
|
|
|