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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

6 Responses to How many calls – A T-SQL Question

  1. Mark Johnson says:

    Hi Steve,

    How about this:

    SELECT date_sent, SUM(FiveDays) AS FiveDays, SUM(TenDays) AS TenDays
    FROM
    (SELECT date_sent
    , CASE
    WHEN DATEDIFF(DAY, date_sent, acc_call_date) < 6 THEN 1
    ELSE 0
    END AS FiveDays
    , CASE
    WHEN DATEDIFF(DAY, date_sent, acc_call_date) < 11 THEN 1
    ELSE 0
    END AS TenDays
    FROM Calls
    ) Summary
    GROUP BY date_sent

    Like

  2. Kevin says:

    Simiar to the previous post, but don’t need the CTE.

    SELECT date_sent, SUM(CASE WHEN DATEDIFF(DAY, date_sent, acc_call_date) < 6 THEN 1 ELSE 0 END) five_day_call, SUM(CASE WHEN DATEDIFF(DAY, date_sent, acc_call_date) < 11 THEN 1 ELSE 0 END) ten_day_call
    FROM Calls
    GROUP BY date_sent
    ORDER BY date_sent

    Like

  3. rvyafin says:

    … 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 10 days than are returned in 5 days….

    may be

    … so we should never have more calls returned in 5 days than are returned in 10 days …

    ??

    Like

Comments are closed.