Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
VeselaApo
Posting Yak Master
114 Posts |
 Posted - 2011-03-28 : 18:55:55
|
| Hi,I have a table that looks like that:Name Maildate_1 Maildate_2 Maildate_3 TotalNumberofMailingsJohn 03/01/2010 Null 04/03/2010 Mary Null Null 05/02/2009I would like to populate the last fourth column with the sum of the count of the mailings date by row. In this way for John I will have a result 2 (Maildate_1 + Maildate_3) and for Mary I will have a result 1 (only maildate_3 is counted as the other two days are null). Any suggestions on easy execution of this? Thanks! I am using Microsoft Sql Server Management Studio. |
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2011-03-28 : 19:53:16
|
Try this:update YourTable set TotalNumberOfMailings = case when Maildate_1 is not null then 1 else 0 end + case when Maildate_2 is not null then 1 else 0 end + case when Maildate_3 is not null then 1 else 0 end Some days you're the dog, and some days you're the fire hydrant. |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-03-28 : 20:58:56
|
| I would add this to Skorch's posting.1. The code is simple enough that there is really no need to add another column to store the result of that computation. You will be wasting storage. You can compute it whenever and wherever required.2. Speaking of "whenever" (and perhaps more importantly), every time you update a row, you will need to recompute this column, so don't store that data.Of course, if your boss puts out an edict that "there shall be a column for TotalNumberOfMailings", you will have no choice :--) |
|
|
|
|
|
|
Adding up count of row content