Voice of the DBA

Author: way0utwest

How many calls – A T-SQL Question
I got a call from a friend recently that was looking for some query help. He was actually using Access, which I haven’t used in years. He knew a little T-SQL, so he could convert anything I gave him to work with his database.

Here was his issue. He had a list of calls made for a marketing campaign, and with each call, a call back date. His task was to get counts of the calls made for a particular date for which there were call backs within two time periods: 5 days and 10 days.

I want to walk through what I tried and what worked. I actually came up with two methods, though I’m not sure either is that efficient. However they worked, and since this is something he’ll run in Access monthly, it’s not a big deal.

I set up a table and get some samples from him:
```
 CREATE TABLE Calls
( date_sent DATETIME , acc_call_date DATETIME ) GO -- rules -- #1 acc call < 6 days -- #2 acc call < 11 days INSERT calls SELECT '12/1/2011', '12/2/2011' -- meets #1 INSERT calls SELECT '12/1/2011', '12/3/2011' -- meets #1 INSERT calls SELECT '12/1/2011', '12/8/2011' -- meets #2 INSERT calls SELECT '12/2/2011', '12/8/2011' -- meets #2 INSERT calls SELECT '12/2/2011', '12/9/2011' -- meets #2 INSERT calls SELECT '12/3/2011', '12/4/2011' -- meets #1 INSERT calls SELECT '12/3/2011', '12/4/2011' -- meets #1 INSERT calls SELECT '12/3/2011', '12/11/2011' -- meets #2 INSERT calls SELECT '12/4/2011', '12/11/2011' -- meets #2 INSERT calls SELECT '12/5/2011', '12/6/2011' -- meets #2 go
```
I have two rules that track the calls. As a quick note, if a call is returned in 5 days, it’s also returned in 10 days, so we should never have more calls returned in 5 days than are returned in 10 days.

Essentially to meet rule #1, we want this:
```
SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
```
If I run this, I get back three rows:

date_sent five_day_call

———————– ————-

2011-12-01 00:00:00.000 2

2011-12-03 00:00:00.000 2

2011-12-05 00:00:00.000 1

These are the counts of calls returned in five days. If I change the scalar from 6 to 11, I get back the calls back in ten days.
```
 SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls_made
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
go
```
The results, as expected, include the 5 calls above, but also have the additional calls returned in ten days.

date_sent ten_day_call

———————– ————

2011-12-01 00:00:00.000 3

2011-12-02 00:00:00.000 2

2011-12-03 00:00:00.000 3

2011-12-04 00:00:00.000 1

2011-12-05 00:00:00.000 1

Now I need to combine these sets. The first thought is often a UNION, but in this case, that doesn’t work. Here’s what happens:
```
SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
UNION SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls_made
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
```
I get duplicate rows for each date if there are rows from each separate query:

date_sent               five_day_call

———————– ————-

2011-12-01 00:00:00.000 2

2011-12-01 00:00:00.000 3

2011-12-02 00:00:00.000 2

2011-12-03 00:00:00.000 2

2011-12-03 00:00:00.000 3

2011-12-04 00:00:00.000 1

2011-12-05 00:00:00.000 1

I can’t do a DISTINCT here, nor can I sum up the rows, because the ten day calls include the five day calls.

Plus my friend really wanted this report:

date_sent               five_day_call ten_day_call

———————– ————- —————-

2011-12-01 00:00:00.000 2             3

2011-12-02 00:00:00.000 0             2

2011-12-03 00:00:00.000 2             3

2011-12-04 00:00:00.000 0             1

2011-12-05 00:00:00.000 1             1

This report is designed to measure the effectiveness of calls, and business analysts need an easy report. If I join the two queries on the call date (date_sent), the problem is that I don’t necessarily have matching call dates for all rows.

What about an outer join?
```
; WITH fiveCTE (call_date, five_day) AS ( SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
) , tenCTE (call_date, ten_Day) AS ( SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls_made
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
) SELECT a.call_Date
 , a.five_day
 , b.ten_day
 FROM fiveCTE a
   FULL OUTER JOIN tenCTE b
     ON a.call_date = b.call_date
```
I’ve moved the two queries into CTEs for readability. I then join them on the date the call was made and return the results. I get this:

call_Date               five_day    ten_day

———————– ———– ———–

2011-12-01 00:00:00.000 2           3

NULL                    NULL        2

2011-12-03 00:00:00.000 2           3

NULL                    NULL        1

2011-12-05 00:00:00.000 1           1

Hmmm, not quite what I need, but it’s closer. I need to get the date for ten day calls, and I also need the NULLs removed from the five day calls.

My first take is to remove the NULL counts.
```
; WITH fiveCTE (call_date, five_day) AS ( SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
) , tenCTE (call_date, ten_Day) AS ( SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls_made
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
) SELECT a.call_Date
 , ISNULL( a.five_day, 0) 'five_day' , b.ten_day
 FROM fiveCTE a
   FULL OUTER JOIN tenCTE b
     ON a.call_date = b.call_date
```
This was better, and cleaned up the results slightly.

call_Date               five_day    ten_day

———————– ———– ———–

2011-12-01 00:00:00.000 2           3

NULL                    0           2

2011-12-03 00:00:00.000 2           3

NULL                    0           1

2011-12-05 00:00:00.000 1           1

Next, I’ll clean up the dates.
```
; WITH fiveCTE (call_date, five_day) AS ( SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
) , tenCTE (call_date, ten_Day) AS ( SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls_made
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
) SELECT ISNULL(a.call_date, b.call_date) 'date_sent' , ISNULL( a.five_day, 0) 'five_day' , b.ten_day
 FROM fiveCTE a
   FULL OUTER JOIN tenCTE b
     ON a.call_date = b.call_date
```
This is much better:

date_sent               five_day    ten_day

———————– ———– ———–

2011-12-01 00:00:00.000 2           3

2011-12-02 00:00:00.000 0           2

2011-12-03 00:00:00.000 2           3

2011-12-04 00:00:00.000 0           1

2011-12-05 00:00:00.000 1           1

That’s what I want, or, what my friend wants. However that wasn’t what I sent. I wasn’t sure that Access would support the full outer join and CTEs, so I actually came up with another way that I’ll write about next time.

If you know of a more efficient way of doing this, I’ve love to know what it is.
December 22, 2011
Pride and Security
Do you love your job? I do.

The weakest link in most security schemes is the human. We know that there are regular breaches of trust by employees, mistakes made (fat fingers, misconfigurations and more), and supposed favors done by someone with trusted access that send data to criminals. Social engineering, in a variety of forms, preys on the trusting nature of most people to gain unauthorized access, and unfortunately, it often works.

Part of social engineering is the inherent trust for others that most of us have. Part of it is the desire that most people have to help others. However I think a part of it is also the attitude that many workers have when they aren’t treated well. When employees don’t feel they are a part of the company family and just work for a paycheck, they are less vigilant or caring about safeguarding the digital assets, and sometimes physical assets, of the company.

If you had more pride in your employer, wouldn’t you be a little more careful in caring for the company and its assets? I think most people would. I don’t have any data on this, but I bet that the companies where people take pride in their work are more secure. Employees probably know more about each other, and problem recognize a larger percentage of the company. Workers will be a little more observant and protective if they feel that the company is “theirs.”

One of the best things management can do to raise the level of security at the company, and build a better organization, is to ensure they are creating an environment that people enjoy and take pride in. That comes from showing respect, consideration, and fair treatment of all employees. It’s not even that hard to do, just be a decent human that does what’s best for everyone in the company, not just for the CEO.

Steve Jones

The Voice of the DBA Podcasts
- Watch the Windows Media Podcast – 19.6MB WMV
- Watch the iPod Video Podcast – 15.6MB MP4
- Watch the MP3 Audio Podcast – 3.3MB MP3
December 20, 2011
Publishing in the Future

A few interesting ideas and experiments in this piece on 21st century publishing. I especially like the part about most of the money in publishing going to pay for these advances that aren’t earned back. While publishers make a lot of money (go peruse a few annual reports), there are definitely some issues with money being given to authors for projects that are a complete gamble.

The idea of shorts, and potentially subscriptions, is something I’ve thought of before. If I could ever get off my duff and do more writing, maybe I’d be able to earn some $$ that way.

December 19, 2011
Creating a Service Master Key in SQL Server
The basis for all the encryption in SQL Server is the Service Master Key, which is the top of the encryption hierarchy. You can see the image below from Books Online of how this is setup.

The service master key is automatically created when you create a key that needs it, usually a database master key. So there is no CREATE SERVICE MASTER KEY command.

This key is encrypted and protected by the Windows DPAPI and the linked to the SQL Server service account. Since it secures all other keys in the encryption hierarchy, if you regenerate it, or restore it, all other keys it protects must be decrypted and re-encrypted. That can be a resource intensive operation, so don’t do that lightly.

You can regenerate a service master key, and you do that with the ALTER SERVICE MASTER KEY command like this:
```
ALTER SERVICE MASTER KEY REGENERATE
```
That’s it. If it works, no errors, no results. If an error occurs, you’d need to deal with it. However this isn’t something you should run often, and if you encounter errors while doing this, I’d suggest you immediately stop, backup all databases and master keys, and then work through the issues.

In another post, I’ll talk a little about the alterations you can make to the Service Master Key and how to back it up or restore it.
December 19, 2011