SQL Query to get call per week, create column based on first day of week
Posted on 2014-02-06
I have a Phone Log table consist of CalledBy and CallOn.
I am trying to write a query that show each sales person calls per week.
The data looks like this
Bob Smith 1/7/2014
Bob Smith 1/8/2014
Bob Smith 1/17/2014
Mary Jane 1/8/2014
Mary Jane 1/9/2014
I want the result to look like this
Name TotalCallsInWeekOf1/7/2014 TotalCallsInWeekOf1/14/2014
Bob Smith 2 1
Mary Jane 1 0
I have a query in progress but it doesn't really do what I needed.
sum(A.calls) AS NumOfCallsMade
CONVERT(varchar, DATEADD(dd, -(DATEPART(dw, CalledOn)-2), CalledOn), 101) AS WeekOf,
,1 AS calls
GROUP BY CalledBy, WeekOf
ORDER BY CalledBy, WeekOf