Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/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.


Filed under: Blog Tagged: sql server, syndicated, T-SQL

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...