I wrote recently about a report that a friend of mine needed from his Access database. In the first post, I used a CTE and an outer join to cull together some data, however since I wasn’t sure Access would support any of that, I came up with another way.

If you read the last post, you’ll have my setup code and sample data. In this one I decided to move to sub-queries as the first part of my work. I started with this:

SELECT a.date_sent 'date_sent' , ISNULL( a.five_day_call, 0) 'five_day_calls' , ISNULL( b.ten_day_call, 0) 'ten_day_calls' FROM ( SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
) a
inner JOIN ( SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
 ) b
   ON a.date_sent = b.date_sent
 go 

I know this doesn’t work because both queries don’t have all the dates. However there was a better way, and one I should have thought of immediately. A few people noticed this in the comments to the previous post. We are looking to count records, according to some criteria. The SUM function will do this. If I run this, I get a count of calls in my 5 day window.

SELECT a.date_sent 'date_sent' , SUM( CASE WHEN DATEDIFF(DAY, date_sent, acc_call_date) < 6 THEN 1 ELSE 0)

FROM calls GROUP BY date_sent

That worked, and it’s easy to understand. What SQL Server does is look through each row, and if there is a row that matches the DATEDIFF function, meaning the call back date is less than 6 days from the call date, then it returns a 1 for that row. Otherwise a 0 is returned. If I then sum the results for each day, I essentially get a count for the date since each call counts as one (or zero).

To get the final query, I can then do this, adding a column for each type of row I am looking for.

SELECT a.date_sent
 , SUM( CASE WHEN DATEDIFF(DAY, date_sent, acc_call_date) < 6 THEN 1 ELSE 0) 'five_day_calls' 
 , SUM( CASE WHEN DATEDIFF(DAY, date_sent, acc_call_date) < 11 THEN 1 ELSE 0) 'ten_day_calls'

FROM dbo.Calls

GROUP BY date_sent

That produced the correct results, and it was easy to convert to Access.

Again, I’m not sure this is the most efficient way to do this, but it worked and it was an interesting problem. If you have a better way to do it, let me know.

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