SQL Query to get call per week, create column based on first day of week

Hi,
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.
Please help!

Select
A.CalledBy,
A.WeekOf,
sum(A.calls) AS NumOfCallsMade
from
(
    SELECT
    CONVERT(varchar, DATEADD(dd, -(DATEPART(dw, CalledOn)-2), CalledOn), 101) AS WeekOf,
    CalledBy
    ,1 AS calls
    FROM PhoneLog
   
) A
GROUP BY CalledBy, WeekOf
ORDER BY CalledBy, WeekOf
rskeptonAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
For just two weeks specifically, the code below should work ok.

If you really need the actual date in the column name, you will have to use dynamic SQL or a temp table for the results.



DROP TABLE #PhoneLog
CREATE TABLE #PhoneLog (
    CalledBy varchar(30),
    CalledOn datetime
    )
INSERT INTO #PhoneLog VALUES('Bob Smith',     '1/5/2014') --should be ignored, before start date
INSERT INTO #PhoneLog VALUES('Bob Smith',     '1/6/2014') --should be ignored, before start date
INSERT INTO #PhoneLog VALUES('Bob Smith',     '1/7/2014')
INSERT INTO #PhoneLog VALUES('Bob Smith',     '1/8/2014')
INSERT INTO #PhoneLog VALUES('Bob Smith',     '1/17/2014')
INSERT INTO #PhoneLog VALUES('Bob Smith',     '1/22/2014') --should be ignored, after start date + 2 weeks
INSERT INTO #PhoneLog VALUES('Mary Jane',     '1/8/2014')
INSERT INTO #PhoneLog VALUES('Mary Jane',     '1/9/2014')


DECLARE @start_date datetime
SET @start_date = '20140107'

----------------------------------------------------------------------------------------------------

SELECT
    pl.CalledBy AS Name,
    SUM(CASE WHEN pl.CalledOn >= @start_date AND pl.CalledOn < DATEADD(DAY, 8, @start_date) THEN 1 ELSE 0 END) AS Week1,
    SUM(CASE WHEN pl.CalledOn >= DATEADD(DAY, 7, @start_date) AND pl.CalledOn < DATEADD(DAY, 15, @start_date) THEN 1 ELSE 0 END) AS Week2
FROM #PhoneLog pl --dbo.PhoneLog pl
WHERE
    pl.CalledOn >= @start_date AND
    pl.CalledOn < DATEADD(DAY, 15, @start_date)
GROUP BY
    pl.CalledBy
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.