SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Complex updating of fields?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Topaz
Posting Yak Master

United Kingdom
199 Posts

Posted - 02/28/2013 :  05:43:28  Show Profile  Click to see Topaz's MSN Messenger address  Reply with Quote
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
52317 Posts

Posted - 02/28/2013 :  05:59:21  Show Profile  Reply with Quote
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/

Go to Top of Page

Topaz
Posting Yak Master

United Kingdom
199 Posts

Posted - 02/28/2013 :  07:11:47  Show Profile  Click to see Topaz's MSN Messenger address  Reply with Quote
Visakh16, this sounds like the perfect solution. Not sure how to do this though?

JT
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 02/28/2013 :  07:14:04  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

Topaz
Posting Yak Master

United Kingdom
199 Posts

Posted - 02/28/2013 :  07:19:49  Show Profile  Click to see Topaz's MSN Messenger address  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 02/28/2013 :  07:46:57  Show Profile  Reply with Quote
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
Go to Top of Page

Topaz
Posting Yak Master

United Kingdom
199 Posts

Posted - 02/28/2013 :  10:27:28  Show Profile  Click to see Topaz's MSN Messenger address  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 02/28/2013 :  11:06:15  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000