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 
